眾所周知,TRUNCATE 會刪除所有行,而不會從資料庫中刪除資料表的結構。可以藉助 DELETE 指令完成相同的工作,從表中刪除所有行。但這兩個指令之間的 PRIMARY KEY AUTO_INCRMENT 重新初始化有顯著差異。
假設一列定義了具有PRIMARY KEY CONSTRAINT 的AUTO_INCRMENT,那麼在使用DELETE 命令刪除所有行時將不會重新初始化初始化表,即在輸入新行時,AUTO_INCREMENT 數字將從最後插入的行之後開始。相反,在使用 TRUNCATE 時,表將像新建立的表一樣重新初始化。這意味著使用 TRUNCATE 命令並插入新行後,AUTO_INCRMENT 數字將從 1 開始。
以下範例將示範上述概念 -
mysql> Create table Testing(Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, Name Varchar(20)); Query OK, 0 rows affected (0.15 sec) mysql> Insert into testing(Name) values('Gaurav'),('Rahul'),('Aarav'),('Yashraj'),('Manak'); Query OK, 5 rows affected (0.09 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> Select * from testing; +----+---------+ | Id | Name | +----+---------+ | 1 | Gaurav | | 2 | Rahul | | 3 | Aarav | | 4 | Yashraj | | 5 | Manak | +----+---------+ 5 rows in set (0.00 sec) mysql> Delete from testing where id >=4; Query OK, 2 rows affected (0.04 sec) mysql> Select * from testing; +----+--------+ | Id | Name | +----+--------+ | 1 | Gaurav | | 2 | Rahul | | 3 | Aarav | +----+--------+ 3 rows in set (0.00 sec) mysql> Insert into testing(Name) values('Harshit'),('Lovkesh'); Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> Select * from testing; +----+---------+ | Id | Name | +----+---------+ | 1 | Gaurav | | 2 | Rahul | | 3 | Aarav | | 6 | Harshit | | 7 | Lovkesh | +----+---------+ 5 rows in set (0.00 sec) mysql> Truncate table testing; Query OK, 0 rows affected (0.10 sec) mysql> Insert into testing(Name) values('Harshit'),('Lovkesh'),('Ram'),('Gaurav'); Query OK, 4 rows affected (0.11 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> Select * from testing; +----+---------+ | Id | Name | +----+---------+ | 1 | Harshit | | 2 | Lovkesh | | 3 | Ram | | 4 | Gaurav | +----+---------+ 4 rows in set (0.00 sec)
以上是MySQL TRUNCATE 和 DELETE 指令有什麼不同?的詳細內容。更多資訊請關注PHP中文網其他相關文章!