“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 (Can't write; duplicate key in table)，因為會為列 (NULL, 'b') 分配 101，並且插入列 (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 |
+----+