InnoDB AUTO_INCREMENT 锁定模式使用含义

1、将自动增量与复制一起使用

如果您使用基于语句的复制，请设置 innodb_autoinc_lock_mode为 0 或 1，并在源及其副本上使用相同的值。如果使用 innodb_autoinc_lock_mode= 2（“ interleaved ”）或源和副本不使用相同锁定模式的配置，则不能确保副本上的自动增量值与源上的值相同。

如果您使用的是基于行或混合格式的复制，所有的自动增量锁定模式都是安全的，因为基于行的复制对 SQL 语句的执行顺序不敏感。

2、“丢失”自动增量值和序列间隙

在所有锁定模式（0、1 和 2）中，如果生成自增值的事务回滚，则这些自增值将“丢失”。一旦为自增列生成了值，无论 “ INSERT-like ” 语句是否完成，以及包含的事务是否回滚，都无法回滚。这种丢失的值不会被重用。因此，存储在表的AUTO_INCREMENT列中的值可能存在间隙 。

3、为AUTO_INCREMENT列指定 NULL 或 0

在所有锁定模式（0、1 和 2）中，如果用户为INSERT中的列指定 NULL 或 0，则InnoDB将该行AUTO_INCREMENT视为未指定该值并为其生成一个新值。

4、为AUTO_INCREMENT列分配负值

在所有锁定模式（0、1 和 2）中，如果为AUTO_INCREMENT 列分配负值，则自动递增机制的行为是不确定的。

5、如果AUTO_INCREMENT值变得大于指定整数类型的最大整数

在所有锁定模式（0、1 和 2）中，如果值变得大于可以存储在指定整数类型中的最大整数，则自动递增机制的行为是不确定的。

6、“批量插入” 的自动增量值的差距

innodb_autoinc_lock_mode 设置为 0（ “ 传统”）或 1（“连续”）时，任何给定语句生成的自动增量值都是连续的，没有间隙，因为表级AUTO-INC 锁一直保持到语句结束，并且仅一次可以执行一个这样的语句。

innodb_autoinc_lock_mode 设置为 2（ “交错”）时， “ bulk inserts ”生成的自动增量值可能存在间隙，但前提是同时执行 “ INSERT-like ” 语句。

对于锁定模式 1 或 2，连续语句之间可能会出现间隙，因为对于批量插入，可能不知道每个语句所需的自动增量值的确切数量，并且可能会高估。

7、由“混合模式插入” 分配的自动增量值

考虑“混合模式插入”，其中 “简单插入”为某些（但不是全部）结果行指定自动增量值。这样的语句在锁定模式 0、1 和 2 中的行为不同。例如，假设c1是表t1的 AUTO_INCREMENT列 ，并且最近自动生成的序列号是 100。

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

现在，考虑以下“混合模式插入” 语句：

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

innodb_autoinc_lock_mode 设置为 0（ “ 传统”）时，四个新行是：

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

下一个可用的自动增量值是 103，因为自动增量值一次分配一个，而不是在语句执行开始时一次性分配。无论是否同时执行 “ INSERT-like ” 语句（任何类型），这个结果都是正确的。

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，而不是 103，因为在处理语句时分配了四个自动增量值，但只使用了两个。无论是否同时执行 “ INSERT-like ” 语句（任何类型），这个结果都是正确的。

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的值是唯一的，并且比以前生成的任何行都大。但是，x和y的具体值取决于并发执行语句生成的自增值的数量。

最后，考虑以下语句，当最近生成的序列号为 100 时：

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');

对于任何 innodb_autoinc_lock_mode 设置，此语句都会生成重复键错误 23000 ( Can't write; duplicate key in table)，因为(NULL, 'b')为行分配了101 导致插入 (101, 'c')失败。

8、在一系列 INSERT语句中间修改AUTO_INCREMENT列值

在 MySQL 5.7 及更早版本中，修改INSERT语句序列中间的AUTO_INCREMENT列值可能会导致“重复条目” 错误。例如，如果您执行的 UPDATE操作将AUTO_INCREMENT列值更改为大于当前最大自动增量值的值，INSERT的后续操作可能会遇到“重复条目”错误。在 MySQL 8.0 及更高版本中，如果您修改 AUTO_INCREMENT将列值设置为大于当前最大自增值的值，新值将被持久化，后续 INSERT操作会从新的更大值开始分配自增值。下面的示例演示了此行为。

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 |
+----+