MySQL突然產生亂序的自動數字

在 MySQL (8.0.27-cll-lve) 中有一個使用自動增量欄位的發票表invoice_id，作為 PK。事務為新發票插入數據，用於LAST_INSERT_ID()獲取新發票，invoice_id然後將其與其他內容一起插入到事務表中。

幾年來，invoice_id每次插入新發票時，autoinc 一直在穩步增加 1，昨天為 2509。

然而，倒數第二筆交易插入了invoice_id3（而不是預期的 2510），下一筆交易插入了 6 的 invoice_id

知道如何，或者更具體地說，為什麼會突然發生這種情況嗎？

附加資訊

1. 大約兩週前，通過應用程序有意刪除了幾張發票，但它們invoice_id的 s 為 1803、1779，即不是最新的 ID，而且多年來還有其他刪除，但 autoinc 序列沒有變化。
2. 在實時數據庫上插入之前拍攝的數據庫快照備份顯示invoice_id2019 年生成的最低序列為

1

2 <- 3 失去

4

5 <- 6 缺失

7

8

9，然後每 1 次，直到成千

.

.

2507

2508

2509

2510 <- 最後一個 invoice_id 生成（在有問題的兩個插入之前拍攝的快照）

因此，MySQL 似乎正在重新使用失去的 3 和 6 - 但為什麼幾年後突然出現？

程式碼以防萬一

最後兩筆交易

START TRANSACTION; 
INSERT INTO invoices (member_id,  fee_type,  fee_year,  invoice_amount,  invoice_date,  invoice_ref,  user_comment) 
VALUES ( 4525, 3, 2022, 31, '2022-01-03', '2022 Pro', 'null_comment' );
SET @the_invoice_id =  LAST_INSERT_ID(); 
INSERT INTO payment_invoice_allocation (invoice_id,allocated_amount,transaction_date,allocation_type, user_comment) 
VALUES (@the_invoice_id, 31, '2022-01-03', 'receivable','null_comment' ); 
COMMIT;


START TRANSACTION; 
INSERT INTO invoices (member_id,  fee_type,  fee_year,  invoice_amount,  invoice_date,  invoice_ref,  user_comment) 
VALUES ( 3921, 4, 2022, 56, '2022-01-03', '2022 Don', 'null_comment' );
SET @the_invoice_id =  LAST_INSERT_ID(); 
INSERT INTO payment_invoice_allocation (invoice_id,allocated_amount,transaction_date,allocation_type, user_comment) 
VALUES (@the_invoice_id, 56, '2022-01-03', 'receivable','null_comment' ); 
COMMIT;

發票表 DDL

CREATE TABLE invoices (
 invoice_id int NOT NULL AUTO_INCREMENT,
 member_id int DEFAULT NULL,
 fee_type int NOT NULL,
 fee_year int DEFAULT NULL,
 invoice_amount decimal(10,2) DEFAULT NULL,
 invoice_date date DEFAULT NULL,
 invoice_ref varchar(300) DEFAULT NULL,
 user_comment varchar(100) DEFAULT NULL,
 date_recorded datetime DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

• 雖然，我的 DBMS 中的表資訊現在顯示了活動數據庫的最後一行要說

ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 （這似乎表明它將複製invoice_id7 下一個）和備份數據庫的

ENGINE=InnoDB AUTO_INCREMENT=2511 DEFAULT CHARSET=latin1 這是我所期望的。

DBMS 中發票表的螢幕截圖，按發票創建日期/時間的降序排列。即頂部的最後兩個插入。

編輯

進一步測試以響應使用者 mustaccio 的評論。

我剛剛在數據庫的另一個副本中進行了測試，我只是用它來嘗試一下。

我使用上面的 SQL 插入了一張新發票。發票表中的 PK 自動遞增到 2076（該表中的下一個最高可用數字）並作為invoice_id.

然後我刪除了 invoice_id 為 3、4 和 5 的三行，人為地製造了一個“空白”，就像在實時數據庫中一樣。

然後我執行 SQLALTER TABLE invoices AUTO_INCREMENT = 3以嘗試重新創建 mustaccio 所說的發生的事情。然後我再次執行插入程式碼以插入另一張新發票。

正如我所料，它忽略了我試圖將 autoinc 重置為低於目前最大值 2076 並且新記錄以 id = 2077 進入。

因此，即使嘗試手動重置它，我也無法讓 autoinc ‘填充’缺失值。- 那麼它是如何在沒有人工干預的情況下發生的呢？

編輯 2

我注意到最近我用來託管遠端數據庫的 ISP 將其 cpanel 介面從“Paper_lantern”更改為“Jupiter”。我相信這只是對 cpanel GIU 的外觀改變，但這對 MySQL 的工作方式有什麼影響嗎？- 我不這麼認為，但如果我確定的話，也許我不會問那些比我更了解這些事情的人。

編輯 3 情況變得更糟，其他人也有同樣的問題

我一個接一個地做了幾個測試插入。第一個給出的 autoinc 值是 10（之前刪除的值介於 9 和 11 之間），第二個給出了 Duplicate key 錯誤，當 autoinc 嘗試為新行分配 ID 11（而該 ID 已經存在）時。

2009 年的一篇文章描述了完全相同的問題，儘管沒有給出任何理由和模棱兩可的解決方案。

我想我可能已經通過完全刪除表格發票並從幾天前的 SQL 轉儲中逐行重建它來解決我的問題。這似乎將 autoinc 索引重置回它應該在的位置（之前我可以在 PhPMyAdmin 中看到它的值錯誤）。但是，我仍然想知道是什麼原因導致這種情況再次發生。

InnoDB 不會生成小於表中最大值的 auto-inc 值。即使您使用 ALTER TABLE 將自動增量設置為較低的值，它也會立即重置為 max+1 值。

但是客戶端可以執行 INSERT 語句並手動指定一個值。這可以是您確定的未使用值之一。它不必大於表中的目前最高值。這樣，客戶就可以“填補空白”。

這是最可能的解釋：一個或多個向您的表中插入數據的客戶端正在指定 id 值，而不是依賴表來生成自動遞增的值。您可以啟用 MySQL 查詢日誌來確認這一點。

感謝 Bill Karwin 的建議，我發現這些症狀是或至少是由 InnoDB 表錯誤（bugs.mysql.com 中的 ID 號 199）引起的，該錯誤於2003 年首次報告，並且在 2017 年仍然強勁。

這是由於在刪除具有 autoinc 欄位的表中的某些行後伺服器重新啟動。

我過去刪除了行，我的 ISP 幾乎可以肯定最近重新啟動了他們的伺服器，因為我知道他們在我遇到這些症狀之前就更改了他們的 cpanel 軟體。

但是，bugs.mysql.com 說它已在 8.0.0 版中得到修復，而我的 ISP 正在執行 8.0.27 版（或者至少在發這篇文章時是這樣）。所以要麼錯誤仍然存在，要麼情節變厚。

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