“INSERT 类”” 语句

所有在表中生成新行的语句，包括 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 是表 t1 的 AUTO_INCREMENT 列

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

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

innodb_autoinc_lock_mode = 0 (“传统” 锁模式)

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

在这种锁模式下，所有“类似 INSERT”语句都会获得一个特殊的表级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无法预先知道 Tx1 中INSERT语句中的SELECT检索到多少行，它会在语句执行过程中一次分配一个自动递增值。使用表级锁，一直保持到语句结束，一次只能执行一个引用表t1的INSERT语句，并且不同语句生成的自动递增数字不会交错。Tx1 INSERT ... SELECT语句生成的自动递增值是连续的，Tx2 中INSERT语句使用的（单个）自动递增值要么小于所有用于 Tx1 的值，要么大于所有用于 Tx1 的值，具体取决于哪个语句先执行。

只要 SQL 语句在从二进制日志中回放时按相同顺序执行（在使用基于语句的复制时，或在恢复情况下），结果与 Tx1 和 Tx2 首次运行时相同。因此，一直保持到语句结束的表级锁使使用自动递增的INSERT语句可以安全地用于基于语句的复制。但是，这些表级锁会限制多个事务同时执行插入语句时的并发性和可扩展性。

在前面的示例中，如果没有表级锁，Tx2 中INSERT语句使用的自动递增列值取决于该语句的精确执行时间。如果 Tx2 的INSERT语句在 Tx1 的INSERT语句运行时执行（而不是在它开始之前或完成之后），两个INSERT语句分配的具体自动递增值是不确定的，并且可能在每次运行时都不同。

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

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

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 类”语句是否完成，以及包含的交易是否回滚，该值都无法回滚。这些丢失的值不会被重用。因此，在表的AUTO_INCREMENT列中存储的值中可能存在间隙。

为AUTO_INCREMENT列指定 NULL 或 0

在所有锁定模式（0、1 和 2）中，如果用户在INSERT 中为AUTO_INCREMENT列指定 NULL 或 0，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 (“交错”)，由“批量插入”生成的自动递增值中可能存在间隙，但前提是存在并发执行的“INSERT 类”语句。

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

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

考虑一个“混合模式插入”，其中一个“简单插入”为某些（但不是全部）结果行指定了自动递增值。这样的语句在锁定模式 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 类”语句（任何类型），此结果都是正确的。

将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 类”语句（任何类型），此结果都是正确的。

将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') 失败。

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

如果您将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 |
+----+