MySQL的自增列情況比較特殊，之前分析了兩篇。
MySQL自增列主從不一致的測試(r12筆記第37天)
MySQL自增列的重複值問題(r12筆記第25天)
而且在OOW的時候也著重提了一下自增列的歷史遺留問題。
十年前的老問題，MySQL 8.0有了答案
當然基於MySQL自增列的實現，確實是不夠優雅，在新的版本還在持續引入新的特性。比如MGR裡面，自增列的步長大了許多，預設是7了，這是在設計的時候考慮了MGR的節點數，提前做了預留，大多數情況下我們可以避免大量的預留值浪費。
當然，最近還有個網友問了我一個自增列的問題，描述的場景略微複雜些，我做了簡化和抽象。
我們建立兩個表t1,t2,在t2裡面插入資料，然後使用insert into select的方式插入資料。
mysql> create table t1(id int auto_increment primary key,name varchar(255));
Query OK, 0 rows affected (0.12 sec)
mysql> create table t2(name varchar(255))engine=innodb;
Query OK, 0 rows affected (0.10 sec)
插入兩條記錄
mysql> insert into t2 values('aa'),('bb');
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
把t2的資料插入t1
mysql> insert into t1(name) select *from t2;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
這個時候問題來了，資料id是1,2。
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
+----+------+
2 rows in set (0.00 sec)
但是自增列的值直接跳到了4。
mysql> show create table t1;
| Table | Create Table
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)
在此插入兩條記錄，使用insert into t1(name) select *from t2;
自增利的值這個時候是7，但是資料的情況如下：
mysql> select max(id) from t1;
+---------+
| max(id) |
+---------+
| 5 |
+---------+
1 row in set (0.00 sec)
mysql> select *from t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 4 | aa |
| 5 | bb |
+----+------+
這種情況就比較麻煩了，類似的測試我又做了一些，可以很明顯看到有些自增ID的缺位。
mysql> select *from t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 4 | aa |
| 5 | bb |
| 7 | aa |
| 8 | bb |
+----+------+
究其原因，和insert語句的定位也有關係，目前有幾類insert語句。
1、simple insert 如insert into t(name) values('test')
2、bulk insert 如load data | insert into ... select .... from ....
3、mixed insert 如insert into t(id,name) values(1,'a'),(null,'b'),(5,'c');
這個和引數innodb_autoinc_lock_mode有很大的關係，預設引數值為1
innodb_auto_lockmode有三個取值
1）、0 這個表示tradition 傳統
2）、1 這個表示consecutive 連續
3）、2 這個表示interleaved 交錯
這個引數不能線上修改，需要重啟例項生效。第一種是表級的auto_inc鎖，對於併發插入來說是有影響的。
預設是第二種，這個模式的好處是auto_inc鎖不要一直保持到語句的結束，只要
語句得到了相應的值後就可以提前釋放鎖
第三種是相對來說效能最好，但是資料的細節無法保證，很可能出現不一致的情況。
對於null值的處理也蠻特別的。我們補充一些，建立表t
mysql> create table t(x int auto_increment not null primary key);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t(x) values(0),(3);
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
可以看到自增列的值中間顯然是有斷層的。
mysql> select * from t;
+---+
| x |
+---+
| 1 |
| 3 |
+---+
如果我們把null值特意放進來，會自增一個相對精確的值。
mysql> insert into t(x) values(0),(null),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select *from t;
+---+
| x |
+---+
| 1 |
| 2 |
| 3 |
+---+
而一旦在程式側修改自增列，其實帶來的問題會更加嚴重，很可能出現1062的錯誤。
比如這個場景，表t有兩行記錄，值為1和3。我們修改一下自增列的值。
mysql> update t set x=4 where x=1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
可以看到資料已經默默做了修改。
mysql> select * from t;
+---+
| x |
+---+
| 3 |
| 4 |
+---+
這個時候插入資料，就會有衝突了。
mysql> insert into t(x) values(0);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
當然這類問題，細化起來，和例項是否重啟也關係重大，對此阿里特意做了定製。
對於自增列的問題，大家在程式側需要格外注意。