Mysql中truncate和delete的区别

1. 条件删除

delete是可以带where的，所以支持条件删除；而truncate只能删除整个表。

# delete - 条件删除
DELETE FROM student WHERE id = 1;
# delete - 删除整个表的数据
DELETE FROM student;
#  truncate - 删除整个表的数据
TRUNCATE TABLE student;

2. 事务回滚

由于DELETE是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollbacksegment中，可以被回滚；
而TRUNCATE是数据定义语言（DDL - Data DefinitionLanguage)，操作时不会进行存储，不能进行回滚

delete案例:

• 显示表结构

mysql> select*from studys;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1  | 2    |   3 |
+----+------+-----+
1 row in set (0.00 sec)

• 开启事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

• 删除表

mysql> delete from studys;
Query OK, 1 row affected (0.01 sec)

• 显示表结构

mysql> select *from studys;
Empty set

• 事务回滚

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

• 回滚成功

mysql> select *from studys;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1  | 2    |   3 |
+----+------+-----+
1 row in set (0.00 sec)

truncate案例:

• 显示表结构

mysql> select *from studys;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1  | 2    |   3 |
+----+------+-----+
1 row in set (0.01 sec)

• 开启事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

• 删除表

mysql> truncate table studys;
Query OK, 0 rows affected (0.12 sec)

• 显示表结构

mysql> select *from studys;
Empty set

• 事务回滚

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

• 回滚失败

mysql> select * from studys;
Empty set

总结:

delete from  student;  --不会影响自增
truncate table student; --会影响自增