MySQL 创建唯一索引忽略对已经重复数据的检查
在创建唯一索引的基础上加上关键字"IGNORE "即可。
# 重复数据 mysql> select aid,count(aid) as total ,email,tel_no,account_type,exten,passwd from fudao_admin group by user_name having total> 1; ------- ------- -------------------------- -------- -------------- ------- ---------------------------------- | aid | total | email | tel_no | account_type | exten | passwd | ------- ------- -------------------------- -------- -------------- ------- ---------------------------------- | 256 | 5 | | | 0 | | | | 4712 | 2 | bin.liu@wenba100.com | 0 | 0 | | 1afc079897c2de73022540f7c31db632 | | 1070 | 2 | feng.wang@wenba100.com | | 0 | | c81103a50f7b7748303ede9726eed91a | | 10429 | 2 | jingxuan.li@wenba100.com | 0 | 0 | 1090 | 9bd7aa138ff1486e1285a412bda934dd | | 10425 | 2 | 843633577@qq.com | 0 | 0 | | 96e79218965eb72c92a549dd5a330112 | | 10426 | 2 | 843633577@qq.com | 0 | 0 | | 96e79218965eb72c92a549dd5a330112 | | 1402 | 2 | wei.liu@wenba100.com | | 0 | | 72d1e186ab204c937ea48a3ff9f926fa | | 3385 | 2 | xiao.li@wenba100.com | 0 | 0 | | 263cad88649b39ed9fc95739c4f20edc | ------- ------- -------------------------- -------- -------------- ------- ---------------------------------- 8 rows in set (0.25 sec)
mysql> ALTER IGNORE TABLE fudao_admin ADD UNIQUE `uq_user_name` (user_name); Query OK, 13530 rows affected, 1 warning (0.44 sec) Records: 13530 Duplicates: 11 Warnings: 1
mysql> select aid,count(aid) as total ,email,tel_no,account_type,exten,passwd from fudao_admin group by user_name having total> 1; Empty set (0.08 sec)