mysql 中 TRUNCATE 、DROP 、DELETE

短命女 2022-03-26 03:24 376阅读 0赞

### 引用 ###

> [https://stackoverflow.com/questions/20559893/comparison-of-truncate-vs-delete-in-mysql-sqlserver][https_stackoverflow.com_questions_20559893_comparison-of-truncate-vs-delete-in-mysql-sqlserver]  
> [https://www.c-sharpcorner.com/blogs/difference-between-truncate-delete-and-drop-in-sql-server1][https_www.c-sharpcorner.com_blogs_difference-between-truncate-delete-and-drop-in-sql-server1]

三个操作均为危险操作 , 正式环境请务必先备份数据 。

--------------------

### TRUNCATE ###

1.  TRUNCATE 是一个 DDL 命令。
2.  TRUNCATE 使用表锁,在删除所有记录时,整张表被锁定。
3.  不能和 where 语句一起使用。
4.  TRUNCATE 会移除表中所有数据，但是不会删除表结构。
5.  TRUNCATE 不会激活触发器，因为该操作不记录单个行删除操作。
6.  不支持回滚。

--------------------

### DELETE ###

1.  DELETE 是一个 DML 命令
2.  DELETE语句使用行锁执行，表中的每一行都被锁定以进行删除。
3.  可以和 where 语句一起使用
4.  删除操作可以激活触发器，因此操作日志是单独记录。
5.  支持回滚。

--------------------

### DROP ###

1.  DROP 是一个 DDL 命令。
2.  DROP 会移除表中所有的行、索引、权限。
3.  不会触发任何DML触发器。
4.  不支持回滚。
5.  不能和 where 语句一起使用。

--------------------

### 执行效率 ###

这一点有待验证 : 执行效率 DROP > TRUNCATE > DELETE 。

--------------------

### 扩展 ###

TRUNCATE和 不带 where 子句的DELETE、以及 DROP 都会删除表内所有的数据 。如果主键自增 ， DELETE 会保留主键计数。

举例 : 如有一张 student 表 , 表中主键 id 为自增 ,现在已经有三个学生数据, id 分表为 1、2、3 , 现在想要删除 student 表

delete  from  student ;   // 删除表中所有数据 , 保留表结构 , 下次插入数据, id 从 4 开始 。
    show  tables ;   //  student  表存在 。

truncate   table  student ;   // 删除表中所有数据 , 保留表结构 , 下次插入数据, id 从 1 开始  。
    show  tables ;   //  student  表存在 。

drop table  student ;   //  删除表中所有数据 , 删除表结构 。
    show  tables ;   //  student  表不存在 。

--------------------

### 关于回滚 ###

BEGIN;
    delete from  student;  //支持回滚
    ROLLBACK;  //表中数据仍存在
    
    BEGIN;
    truncate table student ;  // 不支持回滚
    ROLLBACK;  // 表中所有数据被删除,表结构保留
    
    BEGIN;
    DROP table student ;  // 不支持回滚
    ROLLBACK;   // 表中所有行、索引、权限都被删除

--------------------

#### 如果既想用 truncate 又想使用事务 ####

可以用以下代码代替 truncate , 下列代码支持回滚 :

CREATE TABLE t_test_new LIKE t_test
    RENAME TABLE t_test TO t_test_old, t_test_new TO t_test
    DROP TABLE t_test_old

[https_stackoverflow.com_questions_20559893_comparison-of-truncate-vs-delete-in-mysql-sqlserver]: https://stackoverflow.com/questions/20559893/comparison-of-truncate-vs-delete-in-mysql-sqlserver
[https_www.c-sharpcorner.com_blogs_difference-between-truncate-delete-and-drop-in-sql-server1]: https://www.c-sharpcorner.com/blogs/difference-between-truncate-delete-and-drop-in-sql-server1