InnoDB 中 AUTO_INCREMENT 属性介绍

AUTO_INCREMENT

用于插入数据时自动填充自增数据，该属性必须用于索引中的第一列，包括主键，唯一索引，联合索引第一列。

插入数据有以下几种类型：

• 批量插入：不知道插入的数据条数，如：INSERT … SELECT
• 简单插入：知道插入的条数，如：INSERT … VALUES(…)
• 混合插入：有些值确定，有些值为NULL或0，如：INSERT … VALUES(1, “a”), (NULL, “b”), (5, “c”), (NULL, “d”)和INSERT ... ON DUPLICATE KEY UPDATE，后者既有插入也有更新。

锁模式

select @@innodb_autoinc_lock_mode;

传统 —— traditional

值为0

凡是插入语句均上表锁，这样插入语句均是串行化的。锁的持直到插入语句结束，而非事务。即若事务回滚，计数器是不会回滚的。

这样保证基于语句恢复的过程是可重复的，而且自动填充数据是连续的，除了事务回滚或用户自己设置了计数器。

连续 —— consecutive

MySQL8前默认模式，值为1

仅批量插入上表锁，这时简单插入也需要等待。在语句INSERT … SELECT中：1. 源表和插入表不同，则从源表为读取的第一行数据上共享锁后即为插入表上表锁；2. 源表和插入表相同，则从所有源数据上共享锁后才上表锁，否则没法读取数据。

简单插入和混合插入语句不用表锁，但需要上mutex锁来保证计算插入语句行数，并用计算得到的行数更新计数器的原子性，更新完即释放，执行插入操作时无表锁。

可以看见，连续模式仅在传统模式基础上改进了简单插入和混合插入的情况，可以根据插入行数更新计数器，这样就不需要上表锁了。

这样保证基于语句恢复的过程是可重复的，而且自动填充数据是连续的，除了事务回滚或用户自己设置了计数器。

间断 —— interleaved

MySQL8默认模式，值为2

插入语句均不上表锁。最快的模式。在并发插入时，能保证自动填充的数据是唯一且递增的，但不一定连续。

若所有插入为简单插入语句，则即使是并发插入，数据也能保证连续（因为可以预先知道插入行数，预留插入空间），但结果是不确定的。

这样基于语句的恢复过程是不可重复的，若有并发插入过程则结果是不确定的。

但对于基于行（最终数据与语句执行顺序无关）的恢复过程所有模式是安全的，建议用间断模式，效率更高。

使用实例

接下来实例证明均用以下定义

create table users (
id int primary key auto_increment, 
name varchar(10) not null);

事务回滚，计数器不会回滚。

start transaction;
insert into users(name) values('a'), ('b');
rollback;

insert into users(name) values ('A');
select * from users;
+----+------+
| id | name |
+----+------+
|  3 | A    |
+----+------+

插入NULL或0，会自动填充

insert into users(id, name) values (0, 'B'), (NULL, 'C');
+----+------+
| id | name |
+----+------+
|  3 | A    |
|  4 | B    |
|  5 | C    |
+----+------+

混合插入

已知最新的插入数据id为5，然后把所有数据都删除，接着执行以下操作：

INSERT INTO users (id,name) VALUES (1,'a'), (NULL,'b'), (3, 'c'), (NULL, 'd');

传统模式(0)

执行完上述操作，计数器为8，即接下来的插入操作填充数据id为8。因为传统模式下，所有插入都是串行化的，所以能保证id是连续的。

select * from users order by name;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  6 | b    |
|  3 | c    |
|  7 | d    |
+----+------+

insert into users(name) values ('e');
select * from users order by name;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  6 | b    |
|  3 | c    |
|  7 | d    |
|  8 | e    |
+----+------+

连续模式(1)

执行完上述操作，计数器为10而不是8，即接下来的插入操作填充数据id为10。连续模式下，它首先会发现上述插入语句有4行，接着会预留计数器空间(6-9)，而不管是否被使用，所以最终计数器为10.

select * from users order by name;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  6 | b    |
|  3 | c    |
|  7 | d    |
+----+------+

insert into users(name) values ('e');
select * from users order by name;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  6 | b    |
|  3 | c    |
|  7 | d    |
| 10 | e    |
+----+------+

间断模式(2)

执行完上述操作，结果是不确定的，因为并行插入语句的结果是不确定的。但x、y能保证一定不同且都大于5.

select * from users order by name;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  x | b    |
|  3 | c    |
|  y | d    |
+----+------+

补充

按照最初的条件：已知最新的插入数据id为5，然后把所有数据都删除，接着执行以下操作：

INSERT INTO users (id,name) VALUES (1,'a'), (NULL,'b'), (6,'c'), (NULL,'d');

该语句会报错：23000 (Can't write; duplicate key in table)，因为无论哪种模式，6被分配给’b’，所以插入(6, ‘c’)失败。

中途更新自增列为更大的值

若中途更新自增列某行为大于目前自增列最大值，对MySQL 5.7或更早的版本，后续插入操作会报错，因为计数器不会随着更新操作而更新；对于MySQL 8.0，后续插入操作不会报错，因为计数器会随着更新操作而更新为新的更大的值。

以下为MySQL 5.7的例子

继续使用之前的表，此时id最大值为10

select * from users;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | c    |
|  6 | b    |
|  7 | d    |
| 10 | e    |
+----+------+

update users set id = 11 where id = 1;
select * from users;
+----+------+
| id | name |
+----+------+
|  3 | c    |
|  6 | b    |
|  7 | d    |
| 10 | e    |
| 11 | a    |
+----+------+

insert into users(name) values ('f');
1062 - Duplicate entry '11' for key 'PRIMARY'

以下为MySQL 8.0的例子

mysql> CREATE TABLE t1 (
-> c1 INT NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (c1)
-> ) ENGINE = InnoDB;
mysql> INSERT INTO t1 VALUES(0), (0), (3);
mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
| 1  |
| 2  |
| 3  |
+----+
mysql> UPDATE t1 SET c1 = 4 WHERE c1 = 1;
mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
| 2  |
| 3  |
| 4  |
+----+
mysql> INSERT INTO t1 VALUES(0);
mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
| 2  |
| 3  |
| 4  |
| 5  |
+----+

计数器初始化

在MySQL 5.7和更早的版本中，计数器存储在内存中，服务器开启时，通过类似于以下的语句初始化计数器：

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

在MySQL 8.0版本中，计数器每次更新存储在redo log中，每一个检查点存储在数据字典中，这样计数器就持久化到了磁盘。服务器正常关闭再开启时，计数器通过数据字典初始化；服务器崩溃后再开启时，计数器取数据字典和redo log中最新的检查点后的计数器值两个之中最大的一个。获取计数器正确的值后，计数器就会被存储到内存中。

若人为设置计数器为小于记录的最大自增值，如：ALTER TABLE ... AUTO_INCREMENT= N，则数据库会调用类似于上述操作来确保设置的新的计数器大于等于MAX(ai_col). 在MySQL 5.7和更早版本中，该操作在服务器重启时会失效，因为其计数器存储在内存中；而在MySQL 8.0中，服务器重启后该操作不会失效，因为计数器持久化到了磁盘。

若插入的数据在自增列指定了一个大于目前最大自增值的值，例如数据中最大id为5，而插入一条id为100的行，则计数器被更新为101，后续填充自增列将从101开始。

可以通过auto_increment_offset 和 auto_increment_increment 设置计数器的起始值和步长。