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;
