MySQL删除表操作实现（delete、truncate、drop的区别）

本文主要讲mysql中三种删除表的操作，delete语句、truncate语句以及drop语句的区别：

简介

delete

1、删除整张表的数据：

delete from table_name;

2、删除部分数据，添加where子句：

delete from table_name where...;

3、说明

　　１）、属于DML语言，每次删除一行，都在事务日志中为所删除的每行记录一项。产生rollback，事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发，如果删除大数据量的表速度会很慢。
　　２）、删除表中数据而不删除表的结构(定义)，同时也不释放空间。

truncate

1、只能操作表，将表中数据全部删除，在功能上和不带where子句的delete语句相同：

truncate table table_name;

2、说明

　　１)、默认情况下，truncate通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。所以使用的系统和事务日志资源少，可以使用reuse storage; truncate会将高水线复位(回到最开始).
　　2 )、 truncate是DDL语言, 操作立即生效,自动提交，原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
　　3 )、删除内容、释放空间但不删除表的结构(定义)。

drop

1、drop语句将删除表的结构，以及被依赖的约束(constrain),触发器(trigger),索引(index);

drop table table_name;

2、说明
　　1）、删除之后，依赖于该表的存储过程/函数将保留,但是变为invalid状态.
　　2）、drop也属于DDL语言，立即执行，执行速度最快
　　3）、删除内容和定义，释放空间。

区别

1、表和索引所占空间：
　　当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小；
　　DELETE操作不会减少表或索引所占用的空间；
　　DROP语句将表所占用的空间全释放掉。

2、应用范围：
　　TRUNCATE 只能对table；
　　DELETE可以是table和view。

3、执行速度： drop > truncate > delete

4、delete from删空表后，会保留一个空的页，truncate在表中不会留有任何页。

5、DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

6、当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。truncate始终锁定表和页，而不是锁定各行。

7、如果有identity产生的自增id列，delete from后仍然从上次的数开始增加，即种子不变；

使用truncate删除之后，种子会恢复到初始值。

总结

1、delete 语句可以使用where子句实现部分删除，而truncate不可以，会将表中的整个数据全部删除，使用时，可以按需求选择；
2、如果想从表中删除所有的数据，不要使用delete，可以使用truncate语句，因为这样执行速度更快。truncate语句实际是删除原来的表然后重新建立一个新表；
3、在没有备份情况下，谨慎使用 drop 与 truncate。要删除表结构使用drop;
4、对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。

赞 (0)

相关推荐

  • 详解MySQL中DROP,TRUNCATE 和DELETE的区别实现mysql从零开始

    不同点:  1. truncate和 delete只删除数据不删除表的结构(定义)      drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的 存储过程/函数将保留,但是变为invalid状态. 2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger, 执行的时候将被触发.     truncate,drop是ddl, 操作立即生效,原数据不放到ro

  • MySQL删除表操作实现（delete、truncate、drop的区别）

    本文主要讲mysql中三种删除表的操作,delete语句.truncate语句以及drop语句的区别: 简介 delete 1.删除整张表的数据: delete from table_name; 2.删除部分数据,添加where子句: delete from table_name where...; 3.说明 1).属于DML语言,每次删除一行,都在事务日志中为所删除的每行记录一项.产生rollback,事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发,如果删除大数据量的表速

  • MySQL删除表数据与MySQL清空表命令的3种方法浅析

    目录 一.MySQL清空表数据命令:truncate 二.MySQL删除表命令:drop 三.MySQL清空数据表内容的语法:delete 补充:MySQL删除表操作delete.truncate.drop的区别 总结 一.MySQL清空表数据命令:truncate SQL语法: truncate table 表名 注意: 不能与where一起使用. truncate删除数据后是不可以rollback的. truncate删除数据后会重置Identity(标识列.自增字段),相当于自增列会被置为

  • MySQL删除表的三种方式(小结)

    drop table drop 是直接删除表信息,速度最快,但是无法找回数据 例如删除 user 表: drop table user; truncate (table) truncate 是删除表数据,不删除表的结构,速度排第二,但不能与where一起使用 例如删除 user 表: truncate table user; delete from delete 是删除表中的数据,不删除表结构,速度最慢,但可以与where连用,可以删除指定的行 例如删除user表的所有数据 delete fro

  • MySQL删除表数据的方法

    在MySQL中有两种方法可以删除数据,一种是DELETE语句,另一种是TRUNCATE TABLE语句.DELETE语句可以通过WHERE对要删除的记录进行选择.而使用TRUNCATE TABLE将删除表中的所有记录.因此,DELETE语句更灵活. 如果要清空表中的所有记录,可以使用下面的两种方法:       DELETE FROM table1       TRUNCATE TABLE table1 其中第二条记录中的TABLE是可选的. 如果要删除表中的部分记录,只能使用DELETE语句.

  • MySQL删除表的时候忽略外键约束的简单实现

    删除表不是特别常用,特别是对于存在外键关联的表,删除更得小心.但是在开发过程中,发现Schema设计的有问题而且要删除现有的数据库中所有的表来重新创建也是常有的事情:另外在测试的时候,也有需要重新创建数据库的所有表.当然很多自动化工具也可以做这样的事情. 删除表的时候有时会遇到这样的错误消息: ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails 这是因为你尝试删除的表中的

  • 为什么MySQL 删除表数据 磁盘空间还一直被占用

    为什么MySQL 删除表数据 磁盘空间还一直被占用

    目录 1.Mysql数据结构 2.表文件大小未更改和mysql设计有关 3.那怎么才能让表大小变小 4.Online DDL 5.总结 最近有个上位机获取下位机上报数据的项目,由于上报频率比较频繁且数据量大,导致数据增长过快,磁盘占用多. 为了节约成本,定期进行数据备份,并通过delete删除表记录. 明明已经执行了delete,可表文件的大小却没减小,令人费解 项目中使用Mysql作为数据库,对于表来说,一般为表结构和表数据.表结构占用空间都是比较小的,一般都是表数据占用的空间. 当我们使用

  • MySQL多表操作的外键约束教程

    目录 一.多表关系 一对一关系 一对多/多对一关系 多对多关系 二.外键约束 特点 创建外键约束 方式1-在创建表时设置外键约束 方式2-在创建表时设置外键约束 在外键约束下的操作 1.数据插入 2.删除数据 3.删除外键约束 外键约束-多对多关系 操作 一.多表关系 MySQL多表之间的关系可以概括为:一对一.一对多/多对一关系,多对多 一对一关系 一个学生只有一张身份证;一张身份证只能对应一学生.在任一表中添加唯─外键,指向另一方主键,确保一对一关系.一般一对一关系很少见,遇到一对一关系的表

  • mysql清空表数据的两种方式和区别解析

    在MySQL中删除数据有两种方式: truncate(截短)属于粗暴型的清空 delete属于精细化的删除 删除操作 如果你需要清空表里的所有数据,下面两种均可: delete from tablename; truncate table tablename; 而如果你只是删除一部分数据,就只能使用delete: delete from tablename where case1 and case2; 区别 在精细化的删除部分数据时,只能使用delete. 而清空所有表数据时,两者均可,此时这两

  • MySQL删除表时I/O错误的原因分析与解决

    MySQL删除表时I/O错误的原因分析与解决

    问题现象 最近使用sysbench测试MySQL,由于测试时间较长,写了一个脚本按prepare->run->cleanup的顺序在后台跑着.跑完后察看日志发现一个问题,MySQL服务的错误日志中出现多条类似以下信息的报错: [ERROR] InnoDB: Trying to do I/O to a tablespace which does not exist. I/O type: read, page: [page id: space=32, page number=57890], I/O

  • mysql删除表中某一字段重复的记录

    比如,表:event(id int(10) auto_increment primary key,                 sid int(10)not null,                 detail text) 我想删除表event中sid重复的记录,请问有没有这样SQL语句?或是通过其它方法? 复制代码 代码如下: delete from event as e  where id != (select min(id) from event where sid=e.sid);

随机推荐