mysql对于有大量重复数据的表添加唯一索引

r囧r小猫 2022-09-25 12:15 252阅读 0赞

例如，user表中有user\_id,user\_name两个字段，如果不希望有两条一摸一样的的user\_id和user\_name，我们可以给user表添加两个字段的联合唯一索引：  
alter table user add unique index(user\_id,user\_name);

这样当向表中添加相同记录的时候，会返回1062的添加失败信息。  
但是有一种情况是表中已经有n个重复的记录，这时候我们才想起来要添加唯一索引，再执行上面的操作时，数据库会告诉你已经有重复的记录了，建立索引失败，这时候，我们可以用下面的操作：

alter ignore table user add unique index(user\_id,user\_name);

它会删除重复的记录（别怕，会保留一条），然后建立唯一索引，高效而且人性化。

然而在执行了 alter ignore table tableA add unique index idx\_col1\_u (col1) 后，还是报了以下错误：

\#1062 - Duplicate entry '111' for key 'col1'.

不是会自动丢弃重复数据么？世界观被颠覆了。查了下资料原来是alter ignore的语法不支持innodb。

得知alter ignore的实现完全取决于存储引擎的内部实现，而不是server端强制的，具体描述如下：

>     For ALTER TABLE with the IGNORE keyword, IGNORE is now part of the
>     information provided to the storage engine. It is up to the storage
>     engine whether to use this when choosing between the in-place or copy
>     algorithm for altering the table. For InnoDB index operations, IGNORE 
>     is not used if the index is unique, so the copy algorithm is used

详见：[http://bugs.mysql.com/bug.php?id=40344][http_bugs.mysql.com_bug.php_id_40344]

当然解决这个问题的tricky的方法还是有的。具体如下：

1、

> ALTER TABLE tableA ENGINE MyISAM;  
> ALTER IGNORE TABLE tableA ADD UNIQUE INDEX idx\_col1\_u (col1)  
> ALTER TABLE table ENGINE InnoDB;
> 
> 2、
> 
> 可以不用改成MyISAM，而直接使用set old\_alter\_table = 1; 的方法。具体做法如下：
> 
> set old\_alter\_table = 1;
> 
> ALTER IGNORE TABLE tableA ADD UNIQUE INDEX idx\_col1\_u (col1) 
> 
>   
>

[http_bugs.mysql.com_bug.php_id_40344]: http://bugs.mysql.com/bug.php?id=40344