问题描述
前两天小灰清理一张表的赃数据,需要删除重复数据,仅保留一条。于是,小灰写了一条删除重复数据的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