“ INSERT-like ” 语句

在表中生成新行的所有语句，包括 INSERT、 INSERT ... SELECT、REPLACE、 REPLACE ... SELECT和LOAD DATA。包括“简单插入”、 “批量插入”和“混合模式” 插入。

“简单的插入”

可以预先确定要插入的行数的语句（当语句最初被处理时）。这包括单行和多行 INSERT以及 REPLACE没有嵌套子查询的语句，但不包括 INSERT ... ON DUPLICATE KEY UPDATE.

“批量插入”

事先不知道要插入的行数（以及所需的自动增量值的数量）的语句。这包括 INSERT ... SELECT、 REPLACE ... SELECT和LOAD DATA语句，但不包括普通 INSERT的 。在处理每一行时，一次为列 InnoDB分配一个新值。AUTO_INCREMENT

“混合模式插入”

这些是“简单插入”语句，它们为一些（但不是全部）新行指定自动增量值。下面是一个示例，其中 c1是表的一 AUTO_INCREMENT列 t1：

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

另一种类型的“混合模式插入”是 INSERT ... ON DUPLICATE KEY UPDATE，在最坏的情况下实际上是 anINSERT 后跟 a ，其中在更新阶段可能会或可能不会使用列 UPDATE的分配值 。AUTO_INCREMENT

innodb_autoinc_lock_mode = 0 （“传统”锁定模式）

innodb_autoinc_lock_mode 传统的锁定模式提供了与引入变量 之前相同的行为 。由于语义上可能存在差异，提供传统锁定模式选项是为了向后兼容、性能测试和解决“混合模式插入”的问题。

在这种锁定模式下，所有“ INSERT-like ”语句都获得一个特殊的表级AUTO-INC 锁，用于插入到具有 AUTO_INCREMENT列的表中。此锁通常保持到语句的末尾（而不是事务的末尾），以确保为给定的语句序列以可预测和可重复的顺序分配自动增量值INSERT ，并确保自动增量值由任何给定语句分配的是连续的。

对于基于语句的复制，这意味着当在副本服务器上复制 SQL 语句时，自动增量列使用与源服务器上相同的值。多条语句的执行结果 INSERT是确定的，副本重现与源相同的数据。如果多个语句生成的自动增量值INSERT交错，则两个并发 INSERT语句的结果将是不确定的，并且无法可靠地传播到使用基于语句的复制的副本服务器。

为清楚起见，请考虑使用此表的示例：

CREATE TABLE t1 (
  c1 INT(11) NOT NULL AUTO_INCREMENT,
  c2 VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB;

假设有两个事务正在运行，每个事务都将行插入到包含一 AUTO_INCREMENT列的表中。一个事务使用 INSERT ... SELECT插入 1000 行的语句，另一个事务使用插入一行的简单 INSERT语句：

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

InnoDB无法预先知道从 Tx1SELECT中的 INSERT语句中检索了多少行，并且随着语句的进行，它一次分配一个自动增量值。使用表级锁，保持到语句末尾，一次只能执行一条 INSERT引用表的语句t1，不同语句生成自增数不会交错。Tx1 语句生成的自增值 INSERT ... SELECT是连续的，并且使用的（单个）自增值 INSERTTx2 中的语句小于或大于用于 Tx1 的所有语句，具体取决于首先执行的语句。

只要 SQL 语句在从二进制日志重播时（使用基于语句的复制时，或在恢复场景中）以相同的顺序执行，结果就与 Tx1 和 Tx2 首次运行时相同。因此，表级锁一直保持到语句结束，使 INSERT使用自动增量的语句可以安全地用于基于语句的复制。但是，当多个事务同时执行插入语句时，那些表级锁会限制并发性和可伸缩性。

在前面的示例中，如果没有表级锁，则用于 INSERTin Tx2 的自增列的值恰好取决于语句执行的时间。如果 INSERTof Tx2 在INSERTof Tx1 运行时执行（而不是在它开始之前或它完成之后），则这两个语句分配的特定自动增量值 INSERT是不确定的，并且可能因运行而异。

在 连续 锁模式下，InnoDB可以避免对行数预先已知的 “简单插入”AUTO-INC语句使用表级锁 ，并且仍然为基于语句的复制保留确定性执行和安全性。

如果您不使用二进制日志来重放 SQL 语句作为恢复或复制的一部分， 则可以使用交错 锁定模式来消除对表级 AUTO-INC锁的所有使用，以实现更高的并发性和性能，但代价是允许 auto 中的间隙-递增语句分配的数字，并可能交错并发执行的语句分配的数字。

innodb_autoinc_lock_mode = 1 （“连续”锁定模式）

在这种模式下，“批量插入”使用特殊的 AUTO-INC表级锁并将其保持到语句结束。这适用于所有 INSERT ... SELECT、 REPLACE ... SELECT和LOAD DATA语句。一次只能执行一条持有 AUTO-INC锁的语句。如果批量插入操作的源表与目标表不同，则AUTO-INC在从源表中选择的第一行获得共享锁后，再获得目标表的锁。如果批量插入操作的源和目标是同一张表，则AUTO-INC在对所有选定行获取共享锁后获取锁。

“简单插入”（预先知道要插入的行数） AUTO-INC通过在互斥锁（轻量级锁）的控制下获得所需数量的自动增量值来避免表级锁在分配过程期间持有，直到语句完成。AUTO-INC除非 AUTO-INC锁被另一个事务持有，否则不使用表级 锁。如果另一个事务持有 AUTO-INC锁，则“简单插入”等待AUTO-INC 锁，就好像它是“批量插入”。

这种锁定模式确保，在存在 INSERT行数事先未知的语句（以及自动递增数字随着语句的进行分配）的情况下，任何 “ INSERT-like ” 语句分配的所有自动递增值都是连续的，并且操作对于基于语句的复制是安全的。

简而言之，这种锁定模式显着提高了可伸缩性，同时可以安全地用于基于语句的复制。此外，与“传统” 锁定模式一样，任何给定语句分配的自动递增数字都是连续的。对于 任何使用自动递增的语句， 与“传统”模式相比，语义 没有变化，但有一个重要的例外。

例外是“混合模式插入”AUTO_INCREMENT ，其中用户为多行“简单插入”中的一些（但不是全部）行的列提供显式值 。对于此类插入，InnoDB分配比要插入的行数更多的自动增量值。但是，所有自动分配的值都是连续生成的（因此高于）由最近执行的先前语句生成的自动增量值。“多余”的号码丢失。

innodb_autoinc_lock_mode = 2 （“交错”锁定模式）

在这种锁模式下，没有 “ INSERT-like ” 语句使用表级AUTO-INC 锁，可以同时执行多条语句。这是最快和最具扩展性的锁模式，但在使用基于语句的复制或从二进制日志重放 SQL 语句的恢复场景时 ，它 并不安全。

在这种锁定模式下，自动增量值保证是唯一的，并且在所有并发执行 的“ INSERT-like ” 语句中单调递增。但是，由于多个语句可以同时生成数字（即，数字的分配在语句之间交错），因此为任何给定语句插入的行生成的值可能不是连续的。

如果唯一执行的语句是“简单插入”，其中要插入的行数提前已知，则为单个语句生成的数字没有间隙， “混合模式插入”除外。但是，当执行“批量插入”时，任何给定语句分配的自动增量值可能存在间隙。

在复制中使用自动增量

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

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

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

在所有锁模式（0、1、2）下，如果一个产生自增值的事务回滚，那些自增值就会“丢失”。自增列一旦产生值，就不能回滚，不管 “ INSERT-like ” 语句是否完成，包含的事务是否回滚。这些丢失的值不会被重用。AUTO_INCREMENT因此，存储在表的列 中的值可能存在间隙 。

AUTO_INCREMENT为列 指定 NULL 或 0

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

AUTO_INCREMENT为列 分配负值

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

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

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

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

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

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

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

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

考虑一个“混合模式插入”，其中 “简单插入”指定一些（但不是全部）结果行的自动增量值。这样的语句在锁定模式 0、1 和 2 中的行为不同。例如，假设c1是 table 的 AUTO_INCREMENT列 t1，并且最近自动生成的序列号是 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)，因为为该行分配了 101，并且该行的 (NULL, 'b')插入 (101, 'c')失败。

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

在 MySQL 5.7 及更早版本中，修改 AUTO_INCREMENT一系列语句中间的列值INSERT 可能会导致“重复条目” 错误。例如，如果您执行的 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 |
+----+