對于有大量重復(fù)數(shù)據(jù)的表添加唯一索引_MySQL

1. Alter ignore table come to help

印象中MySQL有一個(gè)獨(dú)有的 alter ignore add unique index的語法。

語法如下：

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name

行為類似于insert ignore，即遇到?jīng)_突的unique數(shù)據(jù)則直接拋棄而不報(bào)錯(cuò)。對于加唯一索引的情況來說就是建一張空表，然后加上唯一索引，將老數(shù)據(jù)用insert ignore語法插入到新表中，遇到?jīng)_突則拋棄數(shù)據(jù)。

文檔中對于alter ignore的注釋：詳見：http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

2. #1062 - Duplicate entry

然而在執(zhí)行了 alter ignore table tableA add unique index idx_col1_u (col1) 后，還是報(bào)了以下錯(cuò)誤：

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

不是會(huì)自動(dòng)丟棄重復(fù)數(shù)據(jù)么？世界觀被顛覆了。查了下資料原來是alter ignore的語法不支持innodb。

得知alter ignore的實(shí)現(xiàn)完全取決于存儲(chǔ)引擎的內(nèi)部實(shí)現(xiàn)，而不是server端強(qiáng)制的，具體描述如下：

For ALTER TABLE with the IGNORE keyword, IGNORE is now part of theinformation provided to the storage engine. It is up to the storageengine whether to use this when choosing between the in-place or copyalgorithm 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

3. 解決方案

當(dāng)然解決這個(gè)問題的tricky的方法還是有的，也比較直白粗暴。具體如下：

ALTER TABLE tableA ENGINE MyISAM;
ALTER IGNORE TABLE tableA ADD UNIQUE INDEX idx_col1_u (col1)
ALTER TABLE table ENGINE InnoDB;

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com