萬盛學電腦網

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

SQL Server 自增欄位歸零:TRUNCATE TABLE vs DBCC CHECKIDENT 完整對比

身份欄位 重置為 1 的兩種正規方法與適用場景

刪除測試資料後,IDENTITY 欄位種子不會自動歸零。本文對比 TRUNCATE TABLE 與 DBCC CHECKIDENT,並說明無法清空資料時的替代方案。

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

TRUNCATE TABLE tableName;

特性

  • 清空所有資料,不可帶 WHERE。
  • 重置 IDENTITY 種子 為建表時定義的種子值(預設 1)。
  • 最小日誌記錄:僅記錄頁釋放,速度極快,不觸發觸發器。
  • 限制:表被外鍵參考時不可用(需先刪除/停用 FK);參與索引檢視/發佈/複寫時不可用。

方法二:DBCC CHECKIDENT(保留資料、指定新種子)

-- 重置為 1(下一筆插入將為 1)
DBCC CHECKIDENT ('tableName', RESEED, 0);

-- 重置為 100(下一筆插入將為 101)
DBCC CHECKIDENT ('tableName', RESEED, 100);

-- 僅檢查當前種子值(不修改)
DBCC CHECKIDENT ('tableName');

特性

  • 不刪除資料,僅調整種子計數器。
  • 可指定任意正整數種子。
  • 需 db_owner 權限。
  • 若表有資料且新種子 ≤ 現有最大值,下次插入會主鍵衝突(需手工處理)。

場景選擇決策樹

  1. 可接受清空整表 → TRUNCATE TABLE(首選)。
  2. 需保留資料、重置種子 → DBCC CHECKIDENT ('tbl', RESEED, 新種子)。
  3. 表有外鍵被參考,無法 TRUNCATE →
    1. 停用 FK:ALTER TABLE child NOCHECK CONSTRAINT fk_name;
    2. TRUNCATE TABLE parent;
    3. 重新啟用 FK:ALTER TABLE child CHECK CONSTRAINT fk_name;
  4. 無法停用 FK、需保留資料、種子歸零 →
    1. 建新表 SELECT * INTO new_tbl FROM old_tbl WHERE 1=0(複製結構含 IDENTITY)。
    2. INSERT INTO new_tbl (col...) SELECT col... FROM old_tbl;(不插入 IDENTITY 欄,自動重新編號)。
    3. 重命名交換表名(sp_rename)。

實測驗證腳本

CREATE TABLE dbo.TestIdentity (Id INT IDENTITY(1,1) PRIMARY KEY, Val VARCHAR(10));
INSERT INTO dbo.TestIdentity (Val) VALUES ('A'),('B'),('C');
-- Id: 1,2,3

DELETE FROM dbo.TestIdentity;          -- 種子仍為 4
INSERT INTO dbo.TestIdentity (Val) VALUES ('D'); -- Id=4

TRUNCATE TABLE dbo.TestIdentity;       -- 種子歸 1
INSERT INTO dbo.TestIdentity (Val) VALUES ('E'); -- Id=1 ✓

-- 或用 DBCC
DBCC CHECKIDENT ('dbo.TestIdentity', RESEED, 0);
INSERT INTO dbo.TestIdentity (Val) VALUES ('F'); -- Id=1 ✓

MySQL / PostgreSQL / Oracle 對應

資料庫重置自增/序列
MySQLALTER TABLE tbl AUTO_INCREMENT = 1; (需表空)
PostgreSQLALTER SEQUENCE seq_name RESTART WITH 1;
OracleDROP SEQUENCE seq; CREATE SEQUENCE seq START WITH 1; (12c+ Identity: ALTER TABLE ... MODIFY GENERATED AS IDENTITY (START WITH 1))
copyright © 萬盛學電腦網 all rights reserved