MySQL编写存储过程删除重复数据

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/iteye_475/article/details/82675501
[size=large]之前在开发过程中,利用sql查询一个对象是否存在时,总是报异常,异常的意思是说不能把多个对象set进一个对象中,然后通过group by,发现数据中重复数据有十几万条,于是编写了一个存储过程用来删除重复,执行了一下,效率还是超快的,一下是sql。[/size]

CREATE PROCEDURE delete_repeatUerPermission () 

BEGIN DECLARE userPermId VARCHAR (32);

DECLARE accountId VARCHAR (32);

DECLARE permissionId VARCHAR (32);

DECLARE _done INT DEFAULT 0;

DECLARE _Cur CURSOR FOR SELECT Id AS userPermId, account_id AS accountId, permission_id AS permissionId FROM idb_permission GROUP BY account_id, permission_id, `status` HAVING COUNT(*) > 1 AND `status` = "1";

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;

OPEN _Cur;

FETCH _Cur INTO userPermId, accountId, permissionId;

WHILE (_done <> 1) DO DELETE FROM idb_permission WHERE Id <> userPermId AND account_id = accountId AND permission_id = permissionId AND `status` = "1";

FETCH _Cur INTO userPermId, accountId, permissionId;

END WHILE;

CLOSE _Cur;

END;

展开阅读全文

没有更多推荐了,返回首页