MySQL TRUNCATE 和 DELETE 命令有什么区别?
众所周知,TRUNCATE 将删除所有行,但不会从数据库中删除表的结构。使用 DELETE 命令删除表中的所有行也能完成同样的工作。但是在两个命令之间,PRIMARY KEY AUTO_INCREMENT 的重新初始化有一个显着的区别。
假设一个列定义了具有 PRIMARY KEY 约束的 AUTO_INCREMENT,那么使用 DELETE 命令删除所有行不会重新初始化表,即在输入新行时,AUTO_INCREMENT 编号将从最后插入的行之后开始。相比之下,使用 TRUNCATE 时,表将重新初始化,就像新创建的表一样。这意味着在使用 TRUNCATE 命令并在插入新行之后,AUTO_INCREMENT 编号将从 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)
广告