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

最新推荐文章于 2024-06-22 21:12:31 发布

iteye_475

最新推荐文章于 2024-06-22 21:12:31 发布

阅读量483

点赞数

分类专栏： mysql 文章标签： mysql 存储过程删除重复数据

本文链接：https://blog.csdn.net/iteye_475/article/details/82675501

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

[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;