為什么80%的碼農都做不了架構師？>>> ??

之前在寫刪除數據庫重復記錄的時候沒有用存儲過程代碼如下：

表（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 from?book

where

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

and b_describe age in (select t2.b_describe from?book t2 group by t2.b_name,t2.b_describe,t2.b_author? having COUNT(*)>1)

and b_author age in (select t2.b_author from?book t2 group by t2.b_name,t2.b_describe,t2.b_author? having COUNT(*)>1)

and id not in(select MIN(t2.id) from from?book t2 group by t2.b_name,t2.b_describe,t2.b_author? having 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條記錄，只是今天剛寫的，所以記下了，希望多多指點！

轉載于:https://my.oschina.net/zjds/blog/168042

總結

以上是生活随笔為你收集整理的mysql存储过程删除重复记录的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。