“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 使用的所有自动增量值，具体取决于哪个语句先执行。

只要在从二进制日志重播 SQL 语句时（使用基于语句的复制时，或在恢复方案中）以相同的顺序执行 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 类””语句分配的所有自动增量值都是连续的，并且操作对于基于语句的复制是安全的。

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

例外情况是““混合模式插入””，其中用户为多行““简单插入””中某些行（但不是所有行）的 AUTO_INCREMENT 列提供显式值。对于此类插入，InnoDB 分配的自动增量值多于要插入的行数。但是，所有自动分配的值都是连续生成的（因此高于最近执行的上一个语句生成的自动增量值）。““超额””编号将丢失。

innodb_autoinc_lock_mode = 2 (““交错”” 锁定模式)

在此锁定模式下，任何““INSERT 类””语句都不使用表级 AUTO-INC 锁，并且多个语句可以同时执行。这是最快且可伸缩性最高的锁定模式，但在使用基于语句的复制或从二进制日志重播 SQL 语句的恢复方案时，它不安全。

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

如果唯一执行的语句是预先知道要插入的行数的““简单插入””，则为单个语句生成的编号中不会存在间隙，但““混合模式插入””除外。但是，执行““批量插入””时，任何给定语句分配的自动增量值中都可能存在间隙。

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

如果使用基于语句的复制，请将 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（无法写入；表中存在重复键），因为 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 |
+----+