众所周知，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: 0mysql> 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: 0mysql> 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: 0mysql> select * from testing;+----+---------+| id | name |+----+---------+| 1 | harshit || 2 | lovkesh || 3 | ram || 4 | gaurav |+----+---------+4 rows in set (0.00 sec)
以上就是mysql truncate 和 delete 命令有什么区别？的详细内容。