萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 自增 ID 重置歸零的兩種方法:TRUNCATE 與 ALTER TABLE 實戰對比

場景

開發/測試環境頻繁初始化資料,自增 ID 增長過大,需重置為 1 起始。

方法一:TRUNCATE TABLE(最快、最徹底)

TRUNCATE TABLE `table_name`;
  • ✅ 瞬間完成,不記錄逐行刪除日志
  • ✅ 自增計數器歸零
  • ✅ 釋放磁碟空間(InnoDB file_per_table=ON)
  • ⚠️ 不可回滾(隱式 COMMIT)
  • ⚠️ 不觸發 DELETE Trigger
  • ⚠️ 需 DROP 權限,非單純 DELETE 權限
  • ⚠️ 被外鍵引用的父表無法 TRUNCATE

方法二:DELETE + ALTER TABLE(保留表結構、可回滾)

-- 步驟 1:清空資料(可在交易中回滾)
DELETE FROM `table_name`;

-- 步驟 2:重置自增計數器
ALTER TABLE `table_name` AUTO_INCREMENT = 1;
  • ✅ 可在 Transaction 中執行,支援回滾
  • ✅ 觸發 DELETE Trigger
  • ✅ 不受外鍵限制(若子表已清空)
  • ⚠️ 大表執行慢,產生大量 Binlog/Undo Log
  • ⚠️ 不釋放磁碟空間(InnoDB 不會縮小 .ibd 檔案)
  • ⚠️ 若表有外鍵被引用,需先處理子表

進階:指定自增起始值

-- 從 10000 開始(避免與匯入舊資料 ID 衝突)
ALTER TABLE `table_name` AUTO_INCREMENT = 10000;

DBCC CHECKIDENT?

文章提及的 DBCC CHECKIDENT 是 SQL Server 指令,**MySQL 不支援**。MySQL 專用 ALTER TABLE ... AUTO_INCREMENT = N。

完整腳本範例(PHP + MySQLi)

<?php
$table = 'vote_log';
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');

if ($mysqli->connect_errno) {
    die("連線失敗: " . $mysqli->connect_error);
}

// 方法二:刪除資料 + 重置自增
$mysqli->query("DELETE FROM `$table`");
$mysqli->query("ALTER TABLE `$table` AUTO_INCREMENT = 1");

echo "已重置 $table 自增 ID 為 1";
$mysqli->close();

選擇建議

場景建議方法
測試環境、無外鍵、要釋放空間TRUNCATE
生產環境維護窗口、需回滾能力DELETE + ALTER
大表(百萬行+)TRUNCATE(秒級)優於 DELETE(分分鐘)
需保留部分資料DELETE WHERE ... + ALTER

注意事項

  1. 自增 ID 達到上限(BIGINT 922京)極罕見,通常無需擔心
  2. 業務邏輯不應依賴 ID 連續性,缺號是正常現象
  3. 分散式系統建議使用 UUID、Snowflake ID 替代自增
copyright © 萬盛學電腦網 all rights reserved