資訊科技

MySQL AUTO_INCREMENT 會 “跳號”

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

578326 這筆資料程式明明有寫入, MySQL 資料庫怎麼把資料給寫丟了呢?

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

自動編號欄位會 “跳號”, 也不是 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;
MySQL AUTO_INCREMENT 欄位跳號範例 SQL 輸出

參考資料

發佈留言

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料