MySQL auto_increment 在 MyISAM 和 InnoDB 中提供不同的主鍵

我有一個帶有自動增量主鍵的表。如果我想更改一行並添加新行，我會根據引擎 MyISAM 和 InnoDB 獲得不同的主鍵。原表是：

pk.. 動物程式碼 動物名稱 動物類 有效日期 到期日期

1… 1… 狗… m… 2013-10-21 …. 9999-12-31

2… 2… 青蛙… .. 一個… 2013-10-21 ….. 9999-12-31

如果我使用 MyISAM 執行更新/插入程式碼，我會得到：

pk.. animal_code 動物名 animal_class Effective_date expiry_date

1… 1… dogg… m… 2013-10-21 …. 2013-10-20

2… 2… frog… … 一個… 2013-10-21 … 9999-12-31

3 … 1 … 狗….. m… 2013-10-21 … 9999 -12-31

4… 3… 貓… m… 2013-10-21….. 9999-12-31

並使用 InnoDB： pk.. animal_code animal_name animal_class Effective_date expiry_date

1… 1… dogg… m… 2013-10-21…. 2013-10-20

2… 2…青蛙….. a… 2013-10-21 … 9999-12-31

4 … 1 … 狗….. m… 2013-10-21.. … 9999-12-31

5… 3… 貓… 米… 2013-10-21….. 9999-12-31

MyISAM 和 InnoDB 之間的新主鍵不同。顯然，這兩個引擎背後有不同的列舉範式。我只是想知道在這種特殊情況下會發生什麼，即InnoDB為什麼會跳轉一個主鍵。獲得此結果的程式碼如下。

感謝您的任何回答。

更新（22.10.2013）：

我將程式碼放在 sqlfiddle 上：http
://sqlfiddle.com/#!2/30cae/ 1 可以更改場景，註釋掉一個引擎並在另一個引擎中註釋。

首先在左側面板上執行Build Schema

然後在左側面板上點擊Run SQL

更改方案查看結果：在第 10 行和第 11 行中：

   ) ENGINE=MyISAM
--  ) ENGINE=INNODB  

PS：如果有id之間的差距我沒有問題，我只是想知道為什麼InnoDB會跳轉。

use test01;

DROP TABLE animals;
DROP TABLE stage;                 

/* (1) Create data base */
CREATE TABLE animals (
pk MEDIUMINT NOT NULL AUTO_INCREMENT
, animal_code    INTEGER NOT NULL
, animal_name    CHAR(30) NOT NULL
, animal_class     CHAR(30) NOT NULL
, effective_date DATE NOT NULL
, expiry_date    DATE NOT NULL
, PRIMARY KEY (pk)
--    ) ENGINE=MyISAM
 ) ENGINE=INNODB
;


/* (2) Create a stage table */
CREATE TABLE stage (
animal_code      INTEGER NOT NULL
, animal_name    CHAR(30) NOT NULL
, animal_class     CHAR(30) NOT NULL
) ENGINE=MyISAM
;

/* (3.1) First import */
INSERT INTO stage (animal_code, animal_name,animal_class) VALUES
(1,'dogg','m'),(2,'frog','a')
;

/* (3.2) Second import 
TRUNCATE stage;
INSERT INTO stage (animal_code, animal_name, animal_class) VALUES
(1,'dog','m'),(2,'frog','a'),(3,'cat','m')
;
*/

/* --- (4) - (6) are use as soon new data are loaded in table stage  --- */  
/* --- So, first insert (3.1) and run (4)-(6)                        --- */  
/* --- Then import new data (3.2) into stage and run (4)-(6)         --- */    

/* (4) expire the existing product         */
UPDATE 
 animals a
, stage   b
SET
 expiry_date = SUBDATE(CURRENT_DATE, 1)
WHERE
   a.animal_code = b.animal_code
   AND ( a.animal_name <> b.animal_name
        OR b.animal_class <> b.animal_class
       )
AND expiry_date = '9999-12-31'
;

/* (5) add a new row for the changing product    */                   
INSERT INTO animals 
SELECT
 NULL  
, b.animal_code
, b.animal_name
, b.animal_class
, CURRENT_DATE
, '9999-12-31'
FROM 
 animals a
, stage   b 
WHERE  a.animal_code = b.animal_code
      AND (a.animal_name <> b.animal_name
           OR b.animal_class <> b.animal_class
          )
      AND EXISTS 
            (SELECT * 
             FROM animals x 
             WHERE  b.animal_code = x.animal_code 
                    AND a.expiry_date = SUBDATE(CURRENT_DATE, 1) 
             )
      AND NOT EXISTS 
            (SELECT *
             FROM animals y 
             WHERE b.animal_code = y.animal_code 
                   AND y.expiry_date = '9999-12-31' 
             );

/* (6) add new product                                                   */

INSERT INTO animals
SELECT
 NULL  
, animal_code
, animal_name
, animal_class
, CURRENT_DATE
, '9999-12-31'
FROM stage  
WHERE animal_code NOT IN(
                       SELECT y.animal_code 
                       FROM animals x, stage y 
                       WHERE x.animal_code = y.animal_code 
                      );

您可以通過自動增量保證（除非您使用它來重置值）下一個生成的值將高於上一個值。它通常只是一個更高的，但你不應該依賴這個：除了簡單地總是增加更多的細節之外，還有未定義的行為。

至於為什麼不同的表類型可能會有所不同：不同的表類型在它們的鎖定/事務/並發行為上有所不同（myISAM 非常簡單/天真，而 InnoDB 則要少得多，以提供參照完整性和性能優勢）。可能是 InnoDB 很謹慎並允許並發事務，當您從動物的詳細資訊中插入行時（步驟 5）“保留”了它認為可能需要但被高估的 ID，因此沒有使用其中一個留下空白. myISAM 可能只是在更新期間應用了表鎖，因此不會發生並發，因此在並發插入操作的情況下無需保留 ID（因此，如果它高估了輸出的行數，則不會留下間隙） .

引用自：https://dba.stackexchange.com/questions/51883