MySQL删除单列重复或多列同时重复值并保留一条

在生产环境中,我们有的列是不允许出现重复值的,亦或是某两列不允许同时重复,但由于前端未做限制,或者没限制住,出现了单列重复值,或者两列本应组成唯一组合却也出现重复,这两种情况都是不允许的。现在由于前端应用限制不住,要做删除操作后,添加唯一索引,从数据库层面进行限制,以下是处理过程:

MySQL> select * from aixuan1;
+----+------+-------+
| id | text | text1 |
+----+------+-------+
|  1 | aa  | 11    |
|  2 | bb  | 22    |
|  3 | cc  | 33    |
|  4 | cc  | 44    |
|  5 | bb  | 22    |
|  6 | aa  | 11    |
|  7 | dd  | 55    |
+----+------+-------+
7 rows in set (0.00 sec)

text字段全部重复的有:
mysql> select * from aixuan1 where text in (select text from aixuan1 GROUP BY text having count(*) > 1);
+----+------+-------+
| id | text | text1 |
+----+------+-------+
|  1 | aa  | 11    |
|  2 | bb  | 22    |
|  3 | cc  | 33    |
|  4 | cc  | 44    |
|  5 | bb  | 22    |
|  6 | aa  | 11    |
+----+------+-------+
6 rows in set (0.00 sec)


筛选出text单列重复值
select * from aixuan1 where `text` in (select `text` from aixuan1 GROUP BY `text` having count(*) > 1) and id not in (select min(id) from aixuan1 group by text having count(*)>1)
+----+------+-------+
| id | text | text1 |
+----+------+-------+
|  4 | cc  | 44    |
|  5 | bb  | 22    |
|  6 | aa  | 11    |
+----+------+-------+
3 rows in set (0.00 sec)

还可以这么查
mysql> select * FROM aixuan1 WHERE id NOT IN ( SELECT temp.mid FROM ( SELECT min(id) as mid FROM aixuan1 em GROUP BY em.text) AS temp);
+----+------+-------+
| id | text | text1 |
+----+------+-------+
|  4 | cc  | 44    |
|  5 | bb  | 22    |
|  6 | aa  | 11    |
+----+------+-------+
3 rows in set (0.00 sec)

筛选出text和text1同时重复的字段:
mysql> select * FROM aixuan1 WHERE id NOT IN ( SELECT temp.mid FROM ( SELECT min(id) as mid FROM aixuan1 em GROUP BY em.text,em.text1) AS temp);
+----+------+-------+
| id | text | text1 |
+----+------+-------+
|  5 | bb  | 22    |
|  6 | aa  | 11    |
+----+------+-------+
2 rows in set (0.00 sec)

查出来了,删就好办了,把select换成delete就Ok了,具体说保留大的id还是保留小的id那条,只要子查询的id函数用min(id)或者max(id)即可

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/a57391b9ba7363c1e70013fa0e98fb91.html