mysql 删除多字段重复值_MySQL删除单列重复或多列同时重复值并保留一条
|
在生产环境中，我们有的列是不允许出现重复值的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)即可 （编辑：南京站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |