删除MySQL数据库中某字段重复数据并保留其他字段的最大值
(如果嫌太墨迹直接看最后两个SQL语句♥)
假设目前有一个表data,主键是id,有一个关键的字段code需要建一个唯一索引,但是表中该字段有大量的重复值。
因此我们需要将这些重复的值删除,一般需要保留id值最大的那一行就行,其他的数据删除掉,才可以建一个唯一索引。
首先我查询一下哪些值重复,重复多少,对应的id是哪个:
SELECT
id,
code,
COUNT(*)
FROM
data
GROUP BY
code
HAVING
COUNT(code) > 1;
看着返回的结果数据,首先我想出将所有重复的值的最小id的值找出来,然后将这些id删除掉,这样重复两行的数据就可以直接删除,SQL语句如下:
DELETE
FROM
data
WHERE
id IN (
SELECT
id
FROM
(
SELECT
id
FROM
data
GROUP BY
code
HAVING
COUNT(code) > 1
) AS temp_1
);
其中,因为MySQL不允许对要update的表先进行子查询,所有需要把查到的数据先放在临时表中,然后再进行删除。
这样的操作其实是绕远路了:
因为SELECT id FROM data GROUP BY code HAVING COUNT(code) > 1;
只会返回所有重复行的最小id,而我们的目标应该是只保留最大的id,所以在这个SQL语句的基础上我们还要再进行修改(一开始我就是用下面的方案,但是SQL语句写错了,导致加班还没有把数据删完):
DELETE
FROM
data
WHERE
code IN (
SELECT
code
FROM
(
SELECT
code
FROM
data
GROUP BY
code
HAVING
COUNT(code) > 1
) AS temp_1
)
AND id NOT IN (
SELECT
id
FROM
(
SELECT
MAX(id) AS id
FROM
data
GROUP BY
code
HAVING
COUNT(code) > 1
) AS temp_2
);
一开始我没有用这个方式的原因是因为我在写SQL语句的时候忘记在MAX(id)后面加一个AS id,导致后面没有排除任何值,在测试中把所有的数据都删除了,让我百思不得其解,也是马虎了,研究半天的语句愣是没有看到这个问题,以后要注意啊。
主要的思路就是把code中那个重复的值除了那行id值最大的,其他全部删除掉。
有的时候,我们一开始的思路是正确,如果在实现过程中遇到问题,除非有别的方式解决,否则还是不要随便启动B方案啊,用开头那个一遍遍的删除最小id值的方法,用一天都删不完。
假如我们需要删除的表是有多个,而且他们有相同的前缀和字段信息,那么我们也可以通过以下的SQL语句获得用于删除这些表的SQL语句:
SELECT
CONCAT(
'DELETE FROM ',
table_name,
' WHERE
后续作为唯一索引的字段 IN (
SELECT
后续作为唯一索引的字段
FROM
(
SELECT
后续作为唯一索引的字段
FROM ',
table_name,
' GROUP BY
后续作为唯一索引的字段
HAVING
COUNT(后续作为唯一索引的字段) > 1
) AS temp_1
)
AND 主键 NOT IN (
SELECT
主键
FROM
(
SELECT
MAX(主键) AS 主键
FROM ',
table_name,
' GROUP BY
后续作为唯一索引的字段
HAVING
COUNT(后续作为唯一索引的字段) > 1
) AS temp_22
);'
)
FROM
information_schema.COLUMNS
WHERE
table_name LIKE '表名前缀%'
GROUP BY
table_name;