InnoDB自增原理都搞不清楚，还怎么CRUD？( 二 ) _InnoDB自增原理

在MySql 8.0以及之后默认的主从复制策略变成了基于数据行实现，在这样的背景下INSERT ... SELECT这样的复杂插入语句也不需要全表加锁来生成自增列数据了，所有的插入语句只有在生成自增列数据的时候要求持有一个轻量级的互斥锁，等到自增数据生成好之后释放锁。在这种实现下，所有插入语句的自增列都不能保证连续自增，但是并发性能确实最好的。
总结需要说明的是，如果插入语句所处的事务回滚了，生成的自增列数据是不会回滚的，这种情况下会造成自增列数据非连续增长。
以上所述都是各个MySql版本的默认实现， MySql 5.1引入了一个新的参数 innodb_autoinc_lock_mode 通过修改这个字段的值，可以改变InnoDB生成自增列的策略，其值总结如下：

文章插图

不推荐显式指定自增列数据，因为在5.7以及之前的版本，如果通过update语句显式指定一个比SELECT MAX(*ai_col*)还大的自增列值，后续insert语句可能会抛"Duplicate entry"错误，这一点在8.0版本之后也有了改变，如果通过显式的update语句显式指定一个比SELECT MAX(*ai_col*)还大的自增列值，那该值就会被持久化，后续的自增列值都从该值开始生成。
假如有下面这张表

mysql> CREATE TABLE t1 (    -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,     -> c2 CHAR(1)    -> ) ENGINE = INNODB AUTO_INCREMENT=100;

试想，在我们执行完下面这条语句之后表的内容变成了什么？
mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');MySql 5.1之前，或者innodb_autoinc_lock_mode设置为0

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;+-----+------+| c1  | c2   |+-----+------+|   1 | a    || 101 | b    ||   5 | c    || 102 | d    |+-----+------+

在这种模式下，每插入一行数据就会生成一个自增值赋到c1这一行，因此c1的下一个自增值是103
MySql 8.0之前，或者innodb_autoinc_lock_mode设置为1

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;+-----+------+| c1  | c2   |+-----+------+|   1 | a    || 101 | b    ||   5 | c    || 102 | d    |+-----+------+

当前表的数据与前一个场景一致，但是下一个自增值却是105 ，因为在这个场景下，自增数据是在插入语句执行的最开始一次性生成的
MySql 8.0之后，或者innodb_autoinc_lock_mode设置为2

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;+-----+------+| c1  | c2   |+-----+------+|   1 | a    ||   x | b    ||   5 | c    ||   y | d    |+-----+------+

在这种场景下，因为同时可能有其他的插入语句执行，因此x和y的值是不确定的，下一个自增值也是未知的。