1. 场景

用户表中有weibo_id,qq_id,wx_id字段,这些字段逻辑上是唯一的,但是并没有唯一索引,导致可能被插入了重复记录。现在需要把这些字段加入UNIQUE INDEX。所以需要把重复记录删除,仅保留ID最大的重复记录。

2. 查询重复记录

# 查询微博ID重复的记录
SELECT
*
FROM
cas_t_account
WHERE
weibo_uid IS NOT NULL
AND cas_t_account.weibo_uid IN (SELECT
weibo_uid
FROM
cas_t_account
WHERE
weibo_uid IS NOT NULL
GROUP BY weibo_uid
HAVING COUNT(*) > 1)

3. 查询需要保留的行

# 查询重复记录中ID最大的一条
SELECT
*
FROM
cas_t_account
WHERE
weibo_uid IS NOT NULL
AND id IN (SELECT
MAX(id)
FROM
cas_t_account
WHERE
weibo_uid IS NOT NULL
GROUP BY weibo_uid
HAVING COUNT(*) > 1)

4. 查询需要删除的重复记录

#有重复行的记录中去掉要保留的行
SELECT
*
FROM
cas_t_account
WHERE
weibo_uid IS NOT NULL
AND weibo_uid IN (SELECT
weibo_uid
FROM
cas_t_account
WHERE
weibo_uid IS NOT NULL
GROUP BY weibo_uid
HAVING COUNT(*) > 1)
AND id NOT IN (SELECT
MAX(id)
FROM
cas_t_account
WHERE
weibo_uid IS NOT NULL
GROUP BY weibo_uid
HAVING COUNT(*) > 1)

把最后这条预计改为delete即可。