前言
自增主鍵沒有持久化是個比較早的bug，這點從其在官方bug網站的id號也可看出（https://bugs.mysql.com/bug.php?id=199）。由Peter Zaitsev（現Percona CEO）于2003年提出。歷史悠久且臭名昭著。
首先，直觀的重現下。
mysql> create table t1(id int auto_increment primary key);Query OK, 0 rows affected (0.01 sec)mysql> insert into t1 values(null),(null),(null);Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from t1;+----+| id |+----+| 1 || 2 || 3 |+----+rows in set (0.00 sec)mysql> delete from t1 where id=3;Query OK, 1 row affected (0.36 sec)mysql> insert into t1 values(null);Query OK, 1 row affected (0.35 sec)mysql> select * from t1;+----+| id |+----+| 1 || 2 || 4 |+----+rows in set (0.01 sec)
雖然id為3的記錄刪除了，但再次插入null值時，并沒有重用被刪除的3，而是分配了4。
刪除id為4的記錄，重啟數據庫，重新插入一個null值。
mysql> delete from t1 where id=4;# service mysqld restartmysql> insert into t1 values(null);Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+----+| id |+----+| 1 || 2 || 3 |+----+rows in set (0.00 sec)
可以看到，新插入的null值分配的是3，按照重啟前的操作邏輯，此處應該分配5啊。
這就是自增主鍵沒有持久化的bug。究其原因，在于自增主鍵的分配，是由InnoDB數據字典內部一個計數器來決定的，而該計數器只在內存中維護，并不會持久化到磁盤中。當數據庫重啟時，該計數器會通過下面這種方式初始化。
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
MySQL 8.0的解決思路
將自增主鍵的計數器持久化到redo log中。每次計數器發生改變，都會將其寫入到redo log中。如果數據庫發生重啟，InnoDB會根據redo log中的計數器信息來初始化其內存值。為了盡量減小對系統性能的影響，計數器寫入到redo log中，并不會馬上刷新。具體可參考：https://dev.mysql.com/worklog/task/?id=6204
因自增主鍵沒有持久化而出現問題的常見場景：
1. 業務將自增主鍵作為業務主鍵，同時，業務上又要求主鍵不能重復。
2. 數據會被歸檔。在歸檔的過程中有可能會產生主鍵沖突。
所以，強烈建議不要使用自增主鍵作為業務主鍵。刨除這兩個場景，其實，自增主鍵沒有持久化的問題并不是很大，遠沒有想象中的”臭名昭著“。
最后，給出一個歸檔場景下的解決方案，
創建一個存儲過程，根據table2（歸檔表）自增主鍵的最大值來初始化table1（在線表）。這個存儲過程可放到init_file參數指定的文件中，該文件中的SQL會在數據庫啟動時執行。
DELIMITER ;;CREATE PROCEDURE `auto_increment_fromtable2`(IN table1 VARCHAR(255), IN table2 VARCHAR(255))BEGINset @qry = concat('SELECT @max1 := (`id` + 1) FROM `',table1,'` ORDER BY `id` DESC LIMIT 1;'); prepare stmt from @qry;execute stmt;deallocate prepare stmt; set @qry = concat('SELECT @max2 := (`id` + 1) FROM `',table2,'` ORDER BY `id` DESC LIMIT 1;'); prepare stmt from @qry;execute stmt;deallocate prepare stmt;IF @max1 < @max2 THEN set @qry = concat('alter table `',table1,'` auto_increment=',@max2);prepare stmt from @qry;execute stmt;deallocate prepare stmt;SELECT 'updated' as `status`;elseSELECT 'no update needed' as `status`;END IF;END ;;DELIMITER ;總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對VeVb武林網的支持。
新聞熱點
疑難解答
