mysql中的delete,drop和truncate有什么区别

目录
  • 前言：
  • 1.delete
    • 1.1 delete 实现原理
    • 1.2 关于自增列
  • 2.truncate
    • 2.1 truncate 实现原理
    • 2.2 重置自增列
  • 3.drop
  • 4.三者的区别
  • 总结

前言：

在 MySQL 中，删除的方法总共有 3 种：delete、truncate、drop，而三者的用法和使用场景又完全不同，接下来我们具体来看。

1.delete

detele 可用于删除表的部分或所有数据，它的使用语法如下：

delete from table_name [where...] [order by...] [limit...]

PS：[] 中的命令为可选命令，可以被省略。

如果我们要删除学生表中数学成绩排名最高的前 3 位学生，可以使用以下 SQL：

delete from student order by math desc limit 3;

1.1 delete 实现原理

在 InnoDB 引擎中，delete 操作并不是真的把数据删除掉了，而是给数据打上删除标记，标记为删除状态，这一点我们可以通过将 MySQL 设置为非自动提交模式，来测试验证一下。

非自动提交模式的设置 SQL 如下：

set autocommit=0;

之后先将一个数据 delete 删除掉，然后再使用 rollback 回滚操作，最后验证一下我们之前删除的数据是否还存在，如果数据还存在就说明 delete 并不是真的将数据删除掉了，只是标识数据为删除状态而已，验证 SQL 和执行结果如下图所示：

1.2 关于自增列

在 InnoDB 引擎中，使用了 delete 删除所有的数据之后，并不会重置自增列为初始值，我们可以通过以下命令来验证一下：

2.truncate

truncate 执行效果和 delete 类似，也是用来删除表中的所有行数据的，它的使用语法如下：

truncate [table] table_name

truncate 在使用上和 delete 最大的区别是，delete 可以使用条件表达式删除部分数据，而 truncate 不能加条件表达式，所以它只能删除所有的行数据，比如以下 truncate 添加了 where 命令之后就会报错：

2.1 truncate 实现原理

truncate 看似只删除了行数据，但它却是 DDL 语句，也就是 Data Definition Language 数据定义语言，它是用来维护存储数据的结构指令，所以这点也是和 delete 命令是不同的，delete 语句属于 DML，Data Manipulation Language 数据操纵语言，用来对数据进行操作的。 为什么 truncate 只是删除了行数据，没有删除列数据（字段和索引等数据）却是 DDL 语言呢？ 这是因为 truncate 本质上是新建了一个表结构，再把原先的表删除掉，所以它属于 DDL 语言，而非 DML 语言。

2.2 重置自增列

truncate 在 InnoDB 引擎中会重置自增列，如下命令所示： 

3.drop

drop 和前两个命令只删除表的行数据不同，drop 会把整张表的行数据和表结构一起删除掉，它的语法如下：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [,tbl_name]

其中 TEMPORARY 是临时表的意思，一般情况下此命令都会被忽略。

drop 使用示例如下： 

4.三者的区别

  • 数据恢复方面：delete 可以恢复删除的数据，而 truncate 和 drop 不能恢复删除的数据。
  • 执行速度方面：drop > truncate > delete。
  • 删除数据方面：drop 是删除整张表，包含行数据和字段、索引等数据，而 truncate 和 drop 只删除了行数据。
  • 添加条件方面：delete 可以使用 where 表达式添加查询条件，而 truncate 和 drop 不能添加 where 查询条件。
  • 重置自增列方面：在 InnoDB 引擎中，truncate 可以重置自增列，而 delete 不能重置自增列。

总结

delete、truncate 可用于删除表中的行数据，而 drop 是把整张表全部删除了，删除的数据包含所有行数据和字段、索引等数据，其中 delete 删除的数据可以被恢复，而 truncate 和 drop 是不可恢复的，但在执行效率上，后两种删除方式又有很大的优势，所以要根据实际场景来选择相应的删除命令，当然 truncate 和 drop 这些不可恢复数据的删除方式使用的时候也要小心。

到此这篇关于mysql中的delete,drop和truncate有什么区别的文章就介绍到这了,更多相关mysql delete drop truncate内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • 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中DROP,TRUNCATE 和DELETE的区别实现mysql从零开始

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

  • mysql中drop、truncate与delete的区别详析

    mysql中drop、truncate与delete的区别详析

    目录 1.drop:删除数据库 2.对比TRUNCATETABLE和DELETEFROM 3.DDL和DML的说明 4.效率对比 总结 1. drop:删除数据库 drop语句将表所占用的空间全释放掉. drop > truncate > delete 方式1:如果要删除的数据库存在,则删除成功.如果不存在,则报错 DROP DATABASE mytest1; 方式2:推荐. 如果要删除的数据库存在,则删除成功.如果不存在,则默默结束,不会报错. DROP DATABASE IF EXISTS

  • mysql中的delete,drop和truncate有什么区别

    mysql中的delete,drop和truncate有什么区别

    目录 前言: 1.delete 1.1 delete 实现原理 1.2 关于自增列 2.truncate 2.1 truncate 实现原理 2.2 重置自增列 3.drop 4.三者的区别 总结 前言: 在 MySQL 中,删除的方法总共有 3 种:delete.truncate.drop,而三者的用法和使用场景又完全不同,接下来我们具体来看. 1.delete detele 可用于删除表的部分或所有数据,它的使用语法如下: delete from table_name [where...]

  • Mysql中的排序规则utf8_unicode_ci、utf8_general_ci的区别总结

    用了这么长时间,发现自己竟然不知道utf_bin和utf_general_ci这两者到底有什么区别.. ci是 case insensitive, 即 "大小写不敏感", a 和 A 会在字符判断中会被当做一样的; bin 是二进制, a 和 A 会别区别对待. 例如你运行: SELECT * FROM table WHERE txt = 'a' 那么在utf8_bin中你就找不到 txt = 'A' 的那一行, 而 utf8_general_ci 则可以. utf8_general_

  • mysql中insert ignore、insert和replace的区别及说明

    目录 insert ignore.insert和replace的区别 测试代码 insert replace insert ignore insert ignore.insert和replace的区别 指令 已存在 不存在 举例 insert 报错 插入 insert into names(name, age) values(“小明”, 23); insert ignore 忽略 插入 insert ignore into names(name, age) values(“小明”, 24); r

  • 基于mysql中delete的语法别名问题

    目录 mysql delete的语法别名问题 mysql delete 语句中使用别名 alias mysql delete的语法别名问题 首先确认,mysql中的delete语句是支持别名的: 在自己书写delete语法时候,语句如下: delete from tableA a where a.c_pk_id = '123' 但是会报一个别名使用错误,如下: [Err] 1064 - You have an error in your SQL syntax; check the manual

  • mysql中key 、primary key 、unique key 与index区别

    mysql中索引是非常重要的知识点,相比其他的知识点,索引更难掌握,并且mysql中的索引种类也有很多,比如primary key .unique key 与index等等,本文章向大家介绍mysql中key .primary key .unique key 与index区别.  一.key与primary key区别 CREATE TABLE wh_logrecord ( logrecord_id int(11) NOT NULL auto_increment, user_name varch

  • mysql中 $｛param｝与#｛param｝使用区别

    ${param}传递的参数会被当成sql语句中的一部分,比如传递表名,字段名 例子:(传入值为id) order by ${param} 则解析成的sql为: order by id #{parm}传入的数据都当成一个字符串,会对自动传入的数据加一个双引号 例子:(传入值为id) select * from table where name = #{param} 则解析成的sql为: select * from table where name = "id" 为了安全,能用#的地方就用

  • MySQL中的join以及on条件的用法解析

    目录 join的用法:连接 on的用法:添加约束 在数据库中验证 MySQL中连接表时join和on的区别 MySQL中 join的用法:join具有 连接的作用,即当两个或者两个以上的表有关系时,需要用join来连接这些相关的表,来处理或分析数据: join的用法:连接 举例先看看,就懂了: 例如: 有一个表叫做stu,一个表叫class,stu join class会生成一个新的表, 我们执行: stu join class 后,新的表成为: 通过上面的例子总结一下: join后的列名是两个

  • 你也许连删库跑路都不会（delete、drop和truncate删除数据）

    前言 这两年 IT 界隔三岔五的出现一次程序员删库的新闻,这种删库跑路的行为往往会给受害公司造成很大的损失,甚至会导致一个公司的破产.我们程序员看到这类新闻的时候很大一部分会把它当作一个闲聊的摊子,但是各位读者你是否想过这么一个问题:我知道怎么正确删库吗?看到这里估计有读者会感觉删库谁不会啊 Delete 以下呗.如果你这么想的话那就接着看这篇文章吧,在后面的内容中我将讲解数据库删除数据的方式以及原理. 在 MySQL 中有三种删除数据的方式,分别是 delete.drop 和 truncate

随机推荐