MySQL AUTO_INCREMENT 會 “跳號” 首部曲

偶爾開發人員會被自動編號欄位跳號的現象誤導, 將其解釋為程式有寫入資料庫, 但是被資料庫把資料給搞丟了. (因為開發人員沒看到程式執行的錯誤, 也不認為寫入資料庫的資料有問題, 更不認為程式寫入資料庫的邏輯有問題.)

其實, 關聯式資料庫引擎發展這麼久了, 要它把寫入的資料 “搞丟”, 應該算是還蠻困難的一件事. 開發人員進行程式除錯的時候, 抱持著千錯萬錯, 絕對不會是資料庫引擎的錯, 可能是一個還不錯的原則!

自動編號欄位會 “跳號”, 也不是 MySQL 特有的行為, Oracle / SQL Server 也都是如此.

以下是用來展示 MySQL AUTO_INCREMENT 欄位跳號現象的範例 SQL:

USE MYDB;

DROP TEMPORARY TABLE IF EXISTS t1;
DROP TEMPORARY TABLE IF EXISTS l1;

CREATE TEMPORARY TABLE t1
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    f1 VARCHAR(50)  NOT NULL,
    PRIMARY KEY (id)
);

CREATE TEMPORARY TABLE l1
(
     message VARCHAR(255) NOT NULL
);

INSERT INTO t1 (f1) VALUES ('r1');
INSERT INTO l1 (message)
  SELECT CONCAT_WS(' ', 'In the beginning,', 't1 has only', (SELECT count(*) FROM t1), 'rows.')
    FROM DUAL;
 INSERT INTO l1 (message)
  SELECT CONCAT('This row is:', '(', (SELECT CONCAT_WS(', ', id, f1) FROM t1 ORDER BY id DESC LIMIT 1), ')')
    FROM DUAL;
 
START TRANSACTION;
  INSERT INTO t1 (f1) VALUES ('r2');
  INSERT INTO t1 (f1) VALUES ('r3');
ROLLBACK;

INSERT INTO l1 (message)
  SELECT CONCAT_WS(' ', 'After two rollbacked inserts,', 't1 still has', (SELECT count(*) FROM t1), 'row only.')
    FROM DUAL;
 INSERT INTO l1 (message)
   SELECT CONCAT_WS(' ', 'Next auto increment id will be: ', LAST_INSERT_ID() + 1)
     FROM DUAL;
 INSERT INTO t1 (f1) VALUES ('r4');
 INSERT INTO l1 (message)
   SELECT CONCAT_WS(' ', 'After 1 committed insert,', 't1 now has', (SELECT count(*) FROM t1), 'rows.')
     FROM DUAL;
 INSERT INTO l1 (message)
   SELECT CONCAT('The new row is: ', '(', (SELECT CONCAT_WS(', ', id, f1) FROM t1 ORDER BY id DESC LIMIT 1), ')')
     FROM DUAL;

SELECT * FROM l1;

DROP TEMPORARY TABLE IF EXISTS t1;
DROP TEMPORARY TABLE IF EXISTS l1;

參考資料

• Using AUTO_INCREMENT
• MySQL AUTO_INCREMENT 會 “跳號” 二部曲