之前在开发过程中,利用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
分类:数据库
评论