mysql重复记录处理

这里记录一下用到的语句和语句模板：

-- 查询出重复的数据
SELECT
COUNT(*) as repeats,
address,
signer_name,
signer_mobile
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
repeats > 1;

-- 查询出重复的数据中最小的id
SELECT
MIN(id) as min_id,
COUNT(*) as repeats,
address,
signer_name,
signer_mobile
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
repeats > 1;

-- 查出重复的数据中非最小的id(需要删除的)
SELECT
id
FROM user_operation_useraddress
WHERE
(address, signer_name, signer_mobile) IN (
SELECT
address,
signer_name,
signer_mobile
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
COUNT(*) > 1
)
AND id NOT IN (
SELECT
MIN(id)
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
COUNT(*) > 1
);

-- 建立一张临时表t
SELECT
id
FROM (
SELECT
id
FROM user_operation_useraddress
WHERE
(address, signer_name, signer_mobile) IN (
SELECT
address,
signer_name,
signer_mobile
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
COUNT(*) > 1
)
AND id IN (
SELECT
MIN(id)
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
COUNT(*) > 1
)
) as t;

-- 删除重复的数据(只保留一条，保留最小id的)
DELETE FROM user_operation_useraddress
WHERE
id IN (
SELECT
id
FROM (
SELECT
id
FROM user_operation_useraddress
WHERE
(address, signer_name, signer_mobile) IN (
SELECT
address,
signer_name,
signer_mobile
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
COUNT(*) > 1
)
AND id IN (
SELECT
MIN(id)
FROM user_operation_useraddress
GROUP BY
address,
signer_name,
signer_mobile
HAVING
COUNT(*) > 1
)
) as t
);