MySQL - 如何將具有重複項的現有 int 列轉換為自動增量？

在表中添加數千條記錄後，我注意到主列的“主要”和“自動增量”屬性已被刪除。現在不是自動遞增的數字，它們中的大多數（但不是全部）只是“0”。

知道如何解決這個問題嗎？所有“0”值都應更新為：Max(int) of column + 1。

假設我有下表（id 是我們想要成為 autoinc 的行，名稱是所有其他列）：

MariaDB [test]> CREATE TABLE without_auto_inc (id int, name text);
Query OK, 0 rows affected (0.013 sec)

讓我們添加一些範例數據，其中包含一些 0：

MariaDB [test]> insert into without_auto_inc VALUES (1, 'a'), (0, 'b'), (3, 'c'), (0, 'd'), (0, 'e'), (5, 'f'), (0, 'g'), (100, 'h');                                             Query OK, 8 rows affected (0.001 sec)
Records: 8  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM without_auto_inc;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    0 | b    |
|    3 | c    |
|    0 | d    |
|    0 | e    |
|    5 | f    |
|    0 | g    |
|  100 | h    |
+------+------+
8 rows in set (0.001 sec)

我們可以簡單地獲取最大 id，重新插入（複製）id = 0 的行，但使用一個變數為每個選定的行增加其值，然後刪除 0 行：

MariaDB [test]> BEGIN;
SELECT max(id) INTO @id FROM without_auto_inc; 
INSERT INTO without_auto_inc SELECT @id := @id + 1 as id, name FROM without_auto_inc WHERE id = 0;
DELETE FROM without_auto_inc WHERE id = 0;
COMMIT;
ALTER TABLE without_auto_inc MODIFY id int primary key auto_increment;
Query OK, 0 rows affected (0.000 sec)

Query OK, 1 row affected (0.001 sec)

Query OK, 4 rows affected (0.002 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected (0.001 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 8 rows affected (0.034 sec)              
Records: 8  Duplicates: 0  Warnings: 0

您可以添加一個ORDER BYTO 選擇以按照您喜歡的順序創建 auto-inc。如果您希望從不重複行，您也可以從文件中插入。確切的方法將取決於您的約束 - 確保您在執行時沒有插入新值，否則您可能會遇到不希望的結果/數據失去（例如，考慮阻止表進行新寫入）。可悲的是，大多數版本的 MySQL 不允許事務性 ALTER（作為更大事務的一部分），因此它會在執行前強制自動送出。

這是執行後表的狀態：

MariaDB [test]> SELECT * FROM without_auto_inc;
+-----+------+
| id  | name |
+-----+------+
|   1 | a    |
|   3 | c    |
|   5 | f    |
| 100 | h    |
| 101 | b    |
| 102 | d    |
| 103 | e    |
| 104 | g    |
+-----+------+
8 rows in set (0.001 sec)

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