「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 語句在從二進位日誌重播時以相同的順序執行 (當使用基於語句的複製或在復原情況下)，結果就會與 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 |
+----+