mysql删除重复数据仅保留一条包含null的问题

问题描述

前两天小灰清理一张表的赃数据，需要删除重复数据，仅保留一条。于是，小灰写了一条删除重复数据的sql语句，但是执行之后，发现仍然有重复数据。经排查是重复数据中有null值引起的。为了清晰直观的说明问题，这里新建一张testtable表:

CREATE TABLE `testtable` (
  `id` int(11) NOT NULL,
  `type` varchar(45) NOT NULL,
  `state` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

其中type可能的取值为’a’、‘b’、‘c’，state可能的取值为’0’、‘1’、NULL，现表中存在重复数据(两行数据中type值和state值分别相等)，select * from testtable order by type,state;查询数据如下：

id	type	state
10	a	NULL
1	a	0
9	a	1
11	b	NULL
4	b	0
3	b	0
2	b	1
5	c	NULL
7	c	NULL
6	c	0
8	c	1

小灰写的删除语句是：

delete from testtable where (type,state) in (
	select t.type, t.state from (
		select type,state from testtable 
        group by type,state
        having count(1) > 1
    ) t
) and id not in(
	select dt.minid from(
		select min(id) as minid from testtable
        group by type,state
        
    ) dt
)

执行完之后，再次查询select * from testtable order by type,state;结果如下：

id	type	state
10	a	NULL
1	a	0
9	a	1
11	b	NULL
3	b	0
2	b	1
5	c	NULL
7	c	NULL
6	c	0
8	c	1

大家注意到此时还是有两条重复数据id分别为5和7。

问题排查

大家看到表中数据很容易怀疑是不是两条重复数据中存在NULL的问题。但其实真实环境中，字段很多，数据量也大，小灰并没有第一眼发现问题所在。于是查询了一下delete语句中where条件后面的in子句：

select t.type, t.state from (
		select type,state from testtable 
        group by type,state
        having count(1) > 1
    ) t

可以查到重复数据：

type	state
c	NULL

但是执行delete语句确没有删除掉：

delete from testtable where (type,state) in (
	select t.type, t.state from (
		select type,state from testtable 
        group by type,state
        having count(1) > 1
    ) t
)

执行结果如下：
11:02:27 delete from testtable where (type,state) in ( select t.type, t.state from ( select type,state from testtable group by type,state having count(1) > 1 ) t ) 0 row(s) affected 0.053 sec
所以问题出在where (type, state) in(…) 这里。
我们知道in运算符用来判断操作数是否为in列表中的一个值，将操作数依次与列表中的元素比较，如果相等则返回1，如果都不相等则返回0。但是NULL比较特殊，如果操作数中有NULL值，不管in列表中是否有NULL值，都返回NULL。如果操作数中没有NULL，右边in列表中没有与其相等的值并且有NULL值，此时不返回0而是返回NULL。如：

select 2 in ('2'), 2 in ('3'), NULL in ('2'), NULL in ('2', NULL), 2 in ('3', NULL), 2 in('2', NULL)

2 in (‘2’)	2 in (‘3’)	NULL in (‘2’)	NULL in (‘2’, NULL)	2 in (‘3’, NULL)	2 in(‘2’, NULL)
1	0	NULL	NULL	NULL	1

所以我们平时判断一个值是否为NULL，应该用is NULL、is not NULL，而不能用=NULL、!= NULL。
前面例子中，存在重复数据的记录中“state”字段的值为NULL，所以执行delete语句的时候，循环到id为7的记录时，该记录的state字段值为NULL，判断是否在in列表中包含时，虽然in列表的记录中，存在state字段值为NULL的记录，但是in运算结果返回NULL，认为in列表中不存在与id为7的记录相等的值，导致最终没有删除掉该重复记录。

问题解决

问题已经清楚了，那如何解决呢？其实我们可以换个思路，要删除掉重复记录，就是删除唯一记录之外的所有记录。由于group by结果中非group by字段只返回一条，也符合上述原则。所以，删除语句改写如下：

delete from testtable where id not in (
	select minid from (
		select min(id) as minid from testtable group by type,state 
    ) t
)

参考
[1]: https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html
[2]: https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html
[3]: https://blog.csdn.net/n950814abc/article/details/82284838