mysql 存储过程游标删除_mysql存储过程（游标)删除重复记录

之前在写删除数据库重复记录的时候没有用存储过程代码如下：

表(book)字段

(id   ,    b_name,   b_describe,     b_author)

1   a,    b,     c

2   a,    b,     c

3   e,    r,     h

4   e,    r,     h

5   a,    r,     c

delete frombook

where

b_name in (select t2.b_name frombookt2 group by t2.b_name,t2.b_describe,t2.b_author having COUNT(*)>1)

and b_describeage in (select t2.b_describe from bookt2 group by t2.b_name,t2.b_describe,t2.b_authorhaving COUNT(*)>1)

and b_authorage in (select t2.b_author from bookt2 group by t2.b_name,t2.b_describe,t2.b_authorhaving COUNT(*)>1)

andid not in(select MIN(t2.id) from from bookt2 group by t2.b_name,t2.b_describe,t2.b_authorhaving COUNT(*)>1)

使用上面的代码可以把完全重复记录删除，但同时也会把第5条记录删除；

后来就改用存储过程的方法如下：

CREATE PROCEDURE del_pointer()

BEGIN

/* 定义变量一 */

DECLARE paramId int;

DECLARE paramName VARCHAR(16);

DECLARE paramDes VARCHAR(16);

DECLARE paramAut VARCHAR(16);

DECLARE  _done int default 0;

/* 定义光标 */

DECLARE _Cur CURSOR FOR

SELECT id,b_name,b_describe,b_author FROM my_book GROUP BY b_name,b_describe,b_author HAVING COUNT(*)>1;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;#错误定义，标记循环结束

/* 打开光标 */

OPEN _Cur;

FETCH _Cur INTO paramId, paramName, paramDes, paramAut;

/* 循环执行 */

WHILE ( _done <> 1) DO

DELETE from my_book WHERE id<>paramId and b_name=paramName and b_describe=paramDes and b_author=paramAut;

/*游标向下走一步*/

FETCH _Cur INTO paramId, paramName, paramDes, paramAut;

END WHILE;

/*关闭光标*/

CLOSE _Cur;

END;

这样就不会删除上面第5条记录，只是今天刚写的，所以记下了，希望多多指点！