MySQL 自增 ID 刪除後重用間隙：重排序存儲過程與 TRUNCATE 行為差異

現象

自增列初始 1，插入→ID=2，刪除該行，再插入 ID 為 3（不重用 2）。若 `TRUNCATE TABLE` 則重置為 1。

原理

InnoDB 自增計數器僅**單調遞增**，不回收間隙（為性能與複製一致性）。`TRUNCATE` 會重建表，重置計數器。

強制重用間隙：重排序存儲過程 (慎用！)

CREATE PROCEDURE ReorderAutoInc(IN tbl CHAR(64), IN pk CHAR(64))
BEGIN
  SET @s = CONCAT('ALTER TABLE `', tbl, '` DROP `', pk, '`;');
  PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  SET @s = CONCAT('ALTER TABLE `', tbl, '` ADD `', pk, '` INT UNSIGNED NOT NULL FIRST;');
  PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  SET @s = CONCAT('ALTER TABLE `', tbl, '` MODIFY `', pk, '` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;');
  PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
END

風險警示

• 鎖表時間長，高併發環境**嚴禁**使用。
• 外鍵引用該主鍵的表會斷裂，需同步更新。
• 業務邏輯不應依賴 ID 連續性；如需連續編號，另建 `display_no` 列由應用層維護。