MySQL AUTO_INCREMENT 會 “跳號” 二部曲

2020 年最後一天, 公司施行德政下午不上班, 正在享受難得不用急急忙忙接送小孩的時光, 冷不防收到同學在 Line 的另一邊劈頭就問, 他的公司軟體團隊發現, MySQL 資料庫中有很多使用 AUTO_INCREMENT 欄位的資料表, 出現中斷數萬號的情況. 因為他下轄的團隊成員有人離職日期是今天, 他搞不清楚到底是正常的“跳號”, 還是上演了被刪庫跑路的戲碼, 因此要我幫忙瞧瞧怎麼回事.

我請他把團隊中典型的資料維護程式傳一支給我瞧瞧先, 在其中發現程式使用了大量的 INSERT … ON DUPLICATE KEY UPDATE 敘述, 由於先前很少看到軟體開發團隊會用這敘述來取代傳統的 UPDATE 敘述, 所以先去 MySQL 官網中翻閱 INSERT … ON DUPLICATE KEY UPDATE 的文件, 才看到第二段文字, 馬上就覺得好像怪怪的喔…

當下請同學再傳程式使用的 DDL 給我瞧瞧, 立刻就發現問題所在囉, 因此請同學可以稍微放心, 這資料庫中的資料發生跳號的情況, 有可能不是離職員工刪庫跑路所造成的. 同時寫了個 Appliction Note 給他, 請他發給他的團隊參考, 更新資料表中的資料, 多數情況用 UPDATE 敘述就夠了, INSERT … ON DUPLICATE KEY UPDATE 敘述應該只在非不得已的情況之下才去使用 (什麼情況算是非不得已的情況呢？ *_*).

Application Note

 USE playground;

 DROP TABLE IF EXISTS t1;

 CREATE TABLE t1
 (
     f_id    int NOT NULL AUTO_INCREMENT,
     f_ak1   int NOT NULL,
     f_ak2   int NOT NULL,
     f_value int NOT NULL,
     PRIMARY KEY (f_id),
     UNIQUE INDEX idx_f_unique (f_ak1, f_ak2)
 ) ENGINE = InnoDB;

 INSERT INTO t1 (f_ak1, f_ak2, f_value)
 VALUES (1, 1, 1),
        (2, 2, 2);

 SELECT f_id, f_ak1, f_ak2, f_value FROM t1;
 /*
 ** Result Grid:
 **
 **      |f_id       |f_ak1      |f_ak2      |f_value    |
 **      |-----------|-----------|-----------|-----------|
 **      |1          |1          |1          |1          |
 **      |2          |2          |2          |2          |
 */

 -- Create a gap, 3
 INSERT INTO t1 (f_ak1, f_ak2, f_value)
 VALUES (1, 1, 1)
     ON DUPLICATE KEY UPDATE f_ak1   = VALUES(f_ak1),
                             f_ak2   = VALUES(f_ak2),
                             f_value = VALUES(f_value) * 10 + VALUES(f_value);

 INSERT INTO t1 (f_ak1, f_ak2, f_value) VALUES (3, 3, 3);

 -- Create another gap, 5
 INSERT INTO t1 (f_ak1, f_ak2, f_value)
 VALUES (3, 3, 3)
     ON DUPLICATE KEY UPDATE f_ak1   = VALUES(f_ak1),
                             f_ak2   = VALUES(f_ak2),
                             f_value = VALUES(f_value) * 10 + VALUES(f_value);

 INSERT INTO t1 (f_ak1, f_ak2, f_value) VALUES (4, 4, 4);

 SELECT f_id, f_ak1, f_ak2, f_value FROM t1;
 /*
 ** Result Grid:
 **
 **      |f_id       |f_ak1      |f_ak2      |f_value    |
 **      |-----------|-----------|-----------|-----------|
 **      |1          |1          |1          |11         |
 **      |2          |2          |2          |2          |
 **      |4          |3          |3          |33         |
 **      |6          |4          |4          |4          |
 */

 DROP TABLE IF EXISTS t1;

建議事項

• 勿對 MySQL 的自動編號欄位中的數值, 抱有其中之值是連續的誤解.
• 盡量少用 INSERT … ON DUPLICATE KEY UPDATE 敘述, 尤其是在 SA/SD 使用自動編號欄位之資料表的維護程式中.
• 自動編號欄位中的數值產生跳號的情況是非常正常的情況, 不需要特別擔心, 更不要把這種欄位應用在需要產生連續資料的商業應用中.

參考資料

• INSERT … ON DUPLICATE KEY UPDATE Statement
• MySQL AUTO_INCREMENT 會 “跳號” 首部曲