我正在auto-increment向一個大表添加一個欄位。

該欄位已填充（大約 1-500M），並且新插入正確地手動遞增。為了驗證，我執行了一個查詢來顯示任何重複項：

mysql> SELECT new_id, COUNT(*) AS count FROM my_table GROUP BY new_id HAVING count > 1;
#      No Results

當我手動查看最新記錄時，我會看到看起來像自動遞增BIGINT(20)欄位的內容。並且NULL該列中不存在 ‘。

然而，當我嘗試將索引更改為UNIQUE INDEX（從非唯一索引，但具有唯一值）時，它會觸發DUPLICATE KEY錯誤。並且重複鍵總是MAX(new_id)+1，因此有問題的重複項的 aSELECT始終不返回任何結果。

這是我正在執行的內容：

mysql> SELECT MAX(new_id) FROM my_table;
+-------------+
| MAX(new_id) |
|   512345678 |
+-------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE my_table
       DROP INDEX `idx_new_id`,
       ADD UNIQUE INDEX `idx_new_id ` USING BTREE (`new_id `);

ERROR 1062 (23000): Duplicate entry '512345679' for key 'idx_new_id'

但為什麼？這個記錄根本不存在！MAX比這個小 1並且自查詢之前以來沒有改變。所以我證明…

mysql> SELECT MAX(new_id) FROM my_table;

+-------------+
| MAX(new_id) |
|   512345678 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM my_table WHERE new_id = 512345679;
# No results

如果記錄甚至不存在，為什麼 MySQL 會標記512345679為重複？512345679

到目前為止，我無法從頭開始複製問題。（分貝小提琴）

你能指點我接下來看哪裡嗎？

最終，我正朝著將主鍵從另一個欄位更改為這個欄位的方向前進。但是我試圖通過預先填充唯一 ID（已經完成）來防止停機。如果我成功完成這一步，我會將此new_id欄位切換為PRIMARY KEYand AUTOINCREMENTit。

---

這是SHOW CREATEfor my_table：

CREATE TABLE `my_table` (
 `new_id` bigint(20) NOT NULL,
 `id` bigint(20) NOT NULL,
 `username` varchar(80) NOT NULL,
 `name` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_name` (`name`),
 KEY `idx_username` (`username `),
 KEY `idx_new_id` (`new_id`) USING BTREE,
 KEY `idx_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我嘗試在 MySQL 5.7.12 for MacOS 中執行您的程式碼。我似乎無法複製這個，但我發現了一些有趣的東西。

SQL-Fiddle 有 MySQL 5.7.17。您在 MySQL 5.7.13 中遇到了錯誤。在這些版本之間刪除和添加二級索引有什麼問題嗎？我相信是的。請注意5.7.14 的發行說明，小標題下的第 21 點Bugs Fixed：

InnoDB：在 ALTER TABLE 操作的回滾期間引發了一個斷言，該操作刪除並添加了二級索引。（錯誤號 22005726）

問題：什麼是二級索引？根據標題“二級索引如何與聚集索引相關”下的MySQL 文件Clustered and Secondary Indexes：

除聚集索引之外的所有索引都稱為二級索引。在 InnoDB 中，二級索引中的每條記錄都包含行的主鍵列，以及為二級索引指定的列。InnoDB 使用這個主鍵值來搜尋聚集索引中的行。

這表明 aUNIQUE INDEX只是一個二級索引。二級索引將有一個 PRIMARY KEY 的副本附加到每個索引條目。

我的斷言是，在 5.7.13 中引入了一個在 5.7.12 中不存在的錯誤，當涉及同時在同一列上刪除和創建索引時。

回頭看看你的ALTER TABLE命令

mysql> ALTER TABLE my_table
       DROP INDEX `idx_new_id`,
       ADD UNIQUE INDEX `idx_new_id` USING BTREE (`new_id `);

您刪除了具有特定索引名稱 ( idx_new_id) 和特定列列表（只有一列new_id）的索引，然後說添加一個具有相同名稱和相同列列表的新索引，但使其成為 index UNIQUE。最終結果是該索引從未真正刪除，並且該UNIQUE屬性只是附加到已經存在的非唯一索引。然後，在後台，mysqld 試圖將數據載入到已經填充的表和仍然填充的索引中，從而產生重複鍵錯誤。請注意，我只是在斷言這一點。我將不得不查看 5.7.13 的原始碼，看看這是否確實發生了。

儘管如此，5.7.12 和 5.7.14 無法重現此問題。這表明在製作 5.7.14 時解決了這種奇怪的情況。

鑑於我的所有猜想，以下是我的建議：

建議

建議 #1：升級到 5.7.14 或更高版本

建議 #2：更改ALTER TABLE以使用不同的索引名稱

mysql> ALTER TABLE my_table
      DROP INDEX `idx_new_id`,
      ADD UNIQUE INDEX `uniq_idx_new_id` USING BTREE (`new_id`);

建議 #3：執行 2 個ALTER TABLE命令

mysql> ALTER TABLE my_table DROP INDEX `idx_new_id`;
mysql> ALTER TABLE my_table ADD UNIQUE INDEX `uniq_idx_new_id` USING BTREE (`new_id`);

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/183127