行數據批量delete時，InnoDB如何處理自增ID，是一個潛在的大坑。

整個實驗步驟如上圖：

• 第一步：建表，設定自增列;
• 第二步：指定id=1插入，錨定第一行是id是1;
• 第三步：不指定id，依賴自增機制，插入3行;畫外音：此時id應該變為2，3，4了?
• 第四步：delete刪除所有記錄;畫外音：坑就容易出在這里。
• 第五步：指定id=0插入;
• 第六步：指定id=1插入;
• 第七步：不指定id，依賴自增機制，插入1行;

請問，此時表中的三行記錄，id分別是多少?

是否符合大家的預期?

今天花1分鐘，說說使用truncate與delete批量刪除數據的異同。

批量刪除數據有三種常見的方法：

• drop table：當不需要該表時，可以使用該方法。
• truncate table：刪除所有數據，同時保留表，速度很快。畫外音：可以理解為，drop table然后再create table。
• delete from table：可以刪除所有數據，也能保留表，但性能較差。也可以帶where條件刪除部分數據，靈活性強。

雖然truncate和delete都能夠刪除所有數據，且保留表，但他們之間是有明顯差異的。

(1) truncate是DDL語句，它不存在所謂的“事務回滾”;delete是DML語句，它執行完是可以rollback的。

(2) truncate table返回值是0;delete from table返回值是被刪除的行數。

(3) InnoDB支持一個表一個文件，此時：

• truncate會一次性把表干掉，且不會激活觸發器，速度非常快;
• delete from table則會一行一行刪除，會激活觸發器，速度比較慢。

畫外音：delete數據，是要記錄日志的，truncate表不需要記錄日志。

(4) 當表中有列被其它表作為外鍵(foreign key)時：

• truncate會是失敗;
• delete則會成功。

畫外音：這類數據刪除失敗很容易定位問題，因為報錯提示簡單易懂。

(5) 當表中有自增列是：

• truncate會使得自增列計數復原;
• delete所有數據后，自增列計數并不會從頭開始。

畫外音：因此，delete所有數據后，自增列計數的這個行為，往往不是用戶想要的，所以是一個潛在坑。

這一分鐘，有收獲嗎?

請根據自己的業務場景，選擇刪除數據的方式喲。

【本文為51CTO專欄作者“58沈劍”原創稿件，轉載請聯系原作者】

戳這里，看該作者更多好文