“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为每个行分配新的自动递增值。

“混合模式插入”

这些是“简单插入”语句，其中指定了部分新行的自动递增值，但不是所有。以下是一个示例，where 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-like”语句对具有AUTO_INCREMENT列的表获取特殊的表级AUTO-INC锁，以便在给定的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无法事先知道SELECT语句在INSERT语句中将检索多少行，并且它会按顺序为语句分配auto-increment值。使用表级锁定直到语句结束，只能在同一时间执行一个INSERT语句，该语句引用表t1，并且不同语句生成的auto-increment值不会交错。Tx1中的INSERT ... SELECT语句生成的auto-increment值是连续的，而Tx2中的INSERT语句使用的auto-increment值则是所有Tx1中语句生成的auto-increment值中的最小或最大值，取决于哪个语句执行的先后。

在从二进制日志中重新播放SQL语句时（使用statement-based复制或恢复场景），只要语句的执行顺序相同，结果将与Tx1和Tx2第一次运行时相同。因此，在语句结束时持有表级锁定的INSERT语句使用自增安全，可以与statement-based复制一起使用。但是，这些表级锁定限制了并发性和可扩展性，当多个事务同时执行插入语句时。

在前面的示例中，如果没有表级锁定，Tx2中的自增列值用于INSERT语句的值取决于语句执行的确切时间。如果Tx2中的INSERT语句在Tx1中的INSERT语句运行时执行（而不是在它开始或完成之前），两个INSERT语句分配的自增值将是非确定性的，可能会因运行而异。

在连续锁定模式下，InnoDB可以避免使用表级“简单插入”语句的AUTO-INC锁定，并仍然保留确定性执行和statement-based复制的安全性。

如果您不使用二进制日志来重放SQL语句作为恢复或复制的一部分，那么可以使用interleaved锁模式以消除所有表级别的AUTO-INC锁，以获得更高的并发性和性能，但这将导致自动递增数字中出现空缺，并且可能会在同时执行的语句之间交错分配这些数字。

innodb_autoinc_lock_mode = 1 (“consecutive” 锁模式)

在这个模式下，“批量插入”使用特殊的表级别锁AUTO-INC，直到语句结束。这适用于所有INSERT ... SELECT、REPLACE ... SELECT和LOAD DATA语句。只有一个语句持有AUTO-INC锁可以同时执行。如果批量插入操作的源表不同于目标表，那么在获取目标表的AUTO-INC锁之前，首先对源表中的第一行加共享锁。如果批量插入操作的源和目标都是同一张表，那么在对所有选择行加共享锁后获取AUTO-INC锁。

“简单插入”(知道要插入的行数)避免了表级别的AUTO-INC锁，通过使用一个轻量级锁（mutex），在分配过程中控制所需的自动递增值，而不是直到语句完成。除非另一个事务持有AUTO-INC锁，否则不使用表级别的AUTO-INC锁。如果另一个事务持有AUTO-INC锁，则一个“简单插入”等待AUTO-INC锁，就像它是一个“批量插入”一样。

这个锁模式确保了，在存在不知道要插入的行数（并且在语句执行过程中分配自动递增号）的INSERT语句时，所有由任何“INSERT-like”语句分配的自动递增号都是连续的，并且操作是安全的，以便于语句级别的复制。

简而言之，这种锁定模式对可扩展性产生了显著的改进，同时也安全地与语句复制一起使用。另外，与传统锁定模式一样，对于任何给定的语句，auto-increment分配的数字都是连续的。相比传统模式，没有语句 semantics 的变化，只有一项重要的例外。

这项例外是对于mixed-mode inserts，用户为多行简单插入提供了明确的值，但不是所有行的AUTO_INCREMENT列。对于这种插入，InnoDB分配了更多的auto-increment值，而不是要插入的行数。但是，这些自动分配的值都是连续生成的（且高于）最近执行的前一个语句生成的auto-increment值。超出数字将被丢弃。

innodb_autoinc_lock_mode = 2（“interleaved”锁定模式）

在这个锁定模式下，不会使用表级别的AUTO-INC锁，并且可以同时执行多个语句。这是最快和最可扩展的锁定模式，但是当使用基于语句的复制或恢复方案时，SQL 语句从二进制日志中重放时，这不是安全的。

在这个锁定模式下，自增值保证是唯一的和单调递增的，对于所有同时执行的INSERT-like 语句。但是，因为多个语句可以同时生成数字（即数字分配是interleaved），给任何语句插入的行可能不是连续的。

如果唯一执行的语句是简单插入语句，其中插入的行数已知，可以在单个语句中生成的数字中没有空隙，除非是混合模式插入。然而，当批量插入语句执行时，可能会出现自增值中的空隙。

使用自动递增与复制

如果您使用语句复制，设置innodb_autoinc_lock_mode为0或1，并在源服务器和其副本上使用相同的值。自动递增值不会在副本上与源服务器上保持一致，如果您使用innodb_autoinc_lock_mode=2（“interleaved”）或配置源服务器和副本不使用相同的锁定模式。

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

“丢失”自动递增值和序列gap

在所有锁定模式（0、1和2）中，如果一个生成自动递增值的事务回滚，那些自动递增值将被“丢失”。一旦为自动递增列生成了值，它不能被回滚，是否完成INSERT-like语句，并且是否回滚包含的事务。这些丢失的值不会被重用。因此，在表中的AUTO_INCREMENT列中可能会出现gap。

为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（“interleaved”）时，可能会出现自增值之间的空隙，这只会在并发执行INSERT-like语句的情况下出现。

对于锁定模式1或2，可能会出现连续语句之间的空隙，因为bulk inserts中每个语句所需的自增值数量可能不确定，并且可能会进行过度估算。

自增值由INSERT-like语句分配

考虑一个INSERT-like语句，其中简单的插入语句指定了自增值，但不是所有结果行。这种语句在锁定模式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;

现在，考虑以下INSERT-like语句：

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

在将innodb_autoinc_lock_mode设置为0（“traditional”）时，四个新行是：

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（“consecutive”），四个新行也包括：

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（“interleaved”），四个新行是：

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