mysql+存储过程+删除重复数据_MySQL编写存储过程删除重复数据

之前在开发过程中，利用sql查询一个对象是否存在时，总是报异常，异常的意思是说不能把多个对象set进一个对象中，然后通过group by,发现数据中重复数据有十几万条，于是编写了一个存储过程用来删除重复，执行了一下，效率还是超快的，一下是sql。

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;

分享到：

2016-11-07 17:00

浏览 1261

分类:数据库

评论