MySQL的自增列（AUTO_INCREMENT）和其它資料庫的自增列對比，有很多特性和不同點（甚至不同存儲引擎、不同版本也有一些不同的特性），讓人感覺有點稍微複雜。下麵我們從一些測試開始，來認識、瞭解一下這方面的特殊知識點： 自增列持久化問題 如果一個表擁有自增列，當前最大自增列值為9， 刪除了自... ...
MySQL的自增列（AUTO_INCREMENT）和其它資料庫的自增列對比，有很多特性和不同點（甚至不同存儲引擎、不同版本也有一些不同的特性），讓人感覺有點稍微複雜。下麵我們從一些測試開始，來認識、瞭解一下這方面的特殊知識點：
自增列持久化問題
如果一個表擁有自增列，當前最大自增列值為9， 刪除了自增列6、7、8、9的記錄，重啟MySQL服務後，再往表裡面插入數據，自增列的值為6還是10呢？ 如果表的存儲引擎為MyISAM呢，又會是什麼情況？ 下麵實驗環境為MySQL 5.7.21
mysql> drop table if exists test;
Query OK, 0 rows affected (0.08 sec)mysql> create table test(id int auto_increment primary key, name varchar(32)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)mysql> insert into test(name)-> select 'kkk1' from dual union all
-> select 'kkk2' from dual union all
-> select 'kkk3' from dual union all
-> select 'kkk4' from dual union all
-> select 'kkk5' from dual union all
-> select 'kkk6' from dual union all
-> select 'kkk7' from dual union all
-> select 'kkk8' from dual union all
-> select 'kkk9' from dual;
Query OK, 9 rows affected (0.01 sec)Records: 9 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | kkk1 |
| 2 | kkk2 |
| 3 | kkk3 |
| 4 | kkk4 |
| 5 | kkk5 |
| 6 | kkk6 |
| 7 | kkk7 |
| 8 | kkk8 |
| 9 | kkk9 |
+----+------+
9 rows in set (0.00 sec)
mysql> delete from test where id>=6;
Query OK, 4 rows affected (0.00 sec)
重啟MySQL服務後，然後我們插入一條記錄，欄位ID會從什麼值開始呢？ 如下所示，如果表的存儲引擎為InnoDB，那麼插入的數據的自增欄位值為6.
接下來，我們創建一個MyISAM類型的測試表。如下所示：
mysql> drop table if exists test;
Query OK, 0 rows affected (0.01 sec)mysql> create table test(id int auto_increment primary key, name varchar(32)) engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)mysql>
insert into test(name)select 'kkk1' from dual union all
select 'kkk2' from dual union all
select 'kkk3' from dual union all
select 'kkk4' from dual union all
select 'kkk5' from dual union all
select 'kkk6' from dual union all
select 'kkk7' from dual union all
select 'kkk8' from dual union all
select 'kkk9' from dual;
mysql> delete from test where id>=6;
Query OK, 4 rows affected (0.00 sec)
刪除了id>=6的記錄後，重啟MySQL服務，如下所示，測試結果為id =10, 那麼為什麼出現不同的兩個結果呢？這個是因為InnoDB存儲引擎中，自增主鍵沒有持久化，而是放在記憶體中，關於自增主鍵的分配，是由InnoDB數據字典內部一個計數器來決定的，而該計數器只在記憶體中維護，並不會持久化到磁碟中。當資料庫重啟時，該計數器會通過SELECT MAX(ID) FROM TEST FOR UPDATE這樣的SQL語句來初始化（不同表對應不同的SQL語句）, 其實這是一個bug來著, 對應的鏈接地址為：https://bugs.mysql.com/bug.php?id=199，直到MySQL 8.0 ，才將自增主鍵的計數器持久化到redo log中。每次計數器發生改變，都會將其寫入到redo log中。如果資料庫發生重啟，InnoDB會根據redo log中的計數器信息來初始化其記憶體值。 而對應與MySIAM存儲引擎，自增主鍵的最大值存放在數據文件當中，每次重啟MySQL服務都不會影響其值變化。
自增列細節特性
1：SQL模式的NO_AUTO_VALUE_ON_ZERO值影響AUTO_INCREMENT列的行為。
mysql> drop table if exists test;
Query OK, 0 rows affected (0.01 sec)mysql> create table test(id int auto_increment primary key, name varchar(32));
Query OK, 0 rows affected (0.02 sec)mysql> select @@sql_mode;+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into test(id, name) value(0, 'kerry');
Query OK, 1 row affected (0.00 sec)mysql> select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | kerry |
+----+-------+
1 row in set (0.00 sec)
mysql>
