alter ignore table add unique key

朱康

背景


经常遇到开发同事有加唯一索引的需求,但是加上唯一索引之前,比较麻烦的事情就是去除重复数据。MySQL提供了一个 ALTER IGNORE TABLE xxx ADD UNIQUE KEY的方式,来忽略掉加唯一索引时的duplicate key errors。

用法


ALTER IGNORE TABLE xxx ADD UNIQUE KEY语句在MySQL5.6.6之前的可用度都是不高的,有bug,会导致两种后果:

  1. ignore并不生效,加不上唯一索引
  2. 主从数据不一致

参考MySQL5.6官方文档,5.6.6修复了bug之后可以正常使用:

avatar

但从官方文档里面,根本看出来,重复的记录到底哪一行被保留,哪些行被删掉了,保留的规则是什么?

从我测试的结果来看,加唯一索引似乎是保留id最小的那一行

mysql> alter table test1 add unique key uniq_notifyid_targetid_type(notify_id,target_id,target_type);  
ERROR 1062 (23000): Duplicate entry '963578-23-1' for key 'uniq_notifyid_targetid_type'  
mysql>  
mysql>  
mysql>  
mysql> select id from test1 where notify_id=963578 and target_id=23 and target_type=1;  
+----------+
| id       |
+----------+
| 60002528 |
| 60002617 |
+----------+
2 rows in set (0.00 sec)

mysql> alter ignore table test1 add unique key uniq_notifyid_targetid_type(notify_id,target_id,target_type);  
Query OK, 681046 rows affected (7.11 sec)  
Records: 681046  Duplicates: 1317  Warnings: 0

mysql> select id from test1 where notify_id=963578 and target_id=23 and target_type=1;  
+----------+
| id       |
+----------+
| 60002528 |
+----------+
1 row in set (0.00 sec)  

总结


  1. 开发对保留具体哪一行记录无要求,或者保留id最小的那一行,采用ALTER IGNORE TABLE xxx ADD UNIQUE KEY的方式是简单有效的。
  2. 有其他特殊的保留规则,直接采用ALTER IGNORE TABLE xxx ADD UNIQUE KEY的方式就不合适了。只能先将重复的记录全部找出,然后根据业务逻辑的保留规则,逐行匹配删除。并且在加唯一索引的期间,确保应用端不会再写入新的重复记录,否则,
    1)如果是采用ALTER TABLE xxx ADD UNIQUE KEY加唯一索引,则仍然失败。
    2)如果是采用pt-online-schema-change工具加唯一索引的话,必须指定--nocheck-unique-key-change选项才能确保添加成功,但这样会有丢失数据的风险。