MySQL中drop、truncate和delete的区别

这篇具有很好参考价值的文章主要介绍了MySQL中drop、truncate和delete的区别。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

✅作者简介:大家好,我是Leo,热爱Java后端开发者,一个想要与大家共同进步的男人😉😉
🍎个人主页:Leo的博客
💞当前专栏:每天一个知识点
✨特色专栏: MySQL学习
🥭本文内容:MySQL中drop、truncate和delete的区别
📚个人知识库: Leo知识库,欢迎大家访问

1.前言

对于drop、truncate和delete,虽然简单,但是真要使用或者面试时候问到还是需要有一定的总结,今天来简单讲讲他们直接的区别。在此之前先简单了解下什么是DDL和DML。
DDL(数据定义语言,Data Definition Language):DDL代表数据定义语言,是一种有助于创建数据库模式的SQL命令。DDL中常用的命令有:createdropaltertruncaterename等等。
DML(数据操作语言,Data Manipulation Language):DML代表数据操作语言,是一种有助于检索和管理关系数据库中数据的SQL命令。DML中常用的命令有:insertupdatedeleteselect等等。

2.drop

DROP命令用于删除整个表(结构和数据),或数据库等对象,特点如下:

  • 彻底删除DROP命令不仅删除表内的所有数据,还删除了表的结构定义。这个过程是不可逆的,除非有备份可以恢复。
  • 释放资源:执行DROP命令后,与该表相关的数据库资源会被释放。
  • 非事务性操作DROP操作通常不能回滚,执行DROP命令后,相关对象就被立即删除。
  • 自增ID:如果创建新表,自增ID会重新开始计数。

代码示例:

DROP TABLE employees;

注意:DROP语句执行后立即生效,无法找回。

3.truncate

TRUNCATE命令用于删除表中的所有行,其特点如下:

  • 快速清空表TRUNCATE比使用DELETE删除表中的所有行要快得多,因为它不逐行删除数据,而是通过释放存储这些数据的数据页来删除数据并重新初始化表。

  • 非事务性操作:尽管某些数据库管理系统可能允许TRUNCATE操作在事务中回滚,但在很多情况下,TRUNCATE并不记录详细的日志,因此不能像DELETE操作那样保证事务安全。

  • 不触发触发器:通常,执行TRUNCATE操作不会触发表的触发器。

  • 自动重置自增ID:对于有自增主键的表,TRUNCATE会重置自增计数器。

  • truncate会删除表中所有记录,并且将重新设置高水线和所有的索引。

    就是truncate会删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子),缺省情况下将空间释放到minextents的extent(就是表结构中的段内的区域),除非使用reuse storage(使用这句话,所在的extent空间不会被回收,只是将数据删除掉,数据删除之后的freespace空间,只能供本表使用,其他的不可以使用)。不会记录日志,所以执行速度很快,但不能通过rollback撤消操作(如果一不小心把一个表truncate掉,也是可以恢复的,只是不能通过rollback来恢复

示例:

TRUNCATE TABLE employees;

注意:TRUNCATE语句执行后立即生效,无法找回。

4.delete

DELETE命令用于删除表中的一行或多行记录,具有如下特点:

  • 选择性删除:可以通过WHERE子句指定删除哪些行。若不指定WHERE子句,则删除表中的所有行。

  • 事务性操作DELETE操作是事务安全的,这意味着你可以在一个事务中回滚DELETE操作。这在你意外删除了错误数据时非常有用。

  • 触发器:如果表上有触发器,执行DELETE操作会触发它们。

  • 性能:因为DELETE操作逐行删除数据,并记录日志,所以在删除大量数据时可能会比较慢。

  • delete语句不影响表所占用的extent(就是表结构的中的区),高水线(high watermark)保持原位置不变。 (高水位线就存在于段(segment)中,它用于标识段中已使用过的数据块与未使用的数据块二者间交界,扫描表数据的时候,高水位线以下的所有数据块都必须被扫描。)

  • 在 InnoDB 中,delete其实并不会真的把数据删除,mysql 实际上只是给删除的数据打了个标记为已删除,因此 delete 删除表中的数据时,表文件在磁盘上所占空间不会变小,存储空间不会被释放,只是把删除的数据行设置为不可见。虽然未释放磁盘空间,但是下次插入数据的时候,仍然可以重用这部分空间(重用 → 覆盖)。

  • delete执行时,会先将所删除数据缓存到rollback segement中,事务commit之后生效;delete from table_name删除表的全部数据,对于MyISAM 会立刻释放磁盘空间,InnoDB 不会释放磁盘空间;
    对于delete from table_name where xxx带条件的删除, 不管是InnoDB还是MyISAM都不会释放磁盘空间;
    delete操作以后使用 optimize table table_name则会立刻释放磁盘空间。不管是InnoDB还是MyISAM 。所以要想达到释放磁盘空间的目的,delete以后执行optimize table操作。

示例:

DELETE FROM employees WHERE department = 'Sales';

5.总结

在速度上,一般来说,drop> truncate > delete文章来源地址https://www.toymoban.com/news/detail-849893.html

  • 如果想删除部分数据用delete,注意带上where子句,回滚段要足够大;如果想删除表,当然用drop;
  • 如果想保留表而将所有数据删除,如果和事务无关(不能回滚),用truncate即可;
  • 如果和事务有关,或者想触发trigger,还是用delete;
  • 如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据。
  • truncate与drop是DDL语句,执行后无法回滚;delete是DML语句,可回滚。
  • truncate只能作用于表;delete,drop可作用于表、视图等。
  • truncate会清空表中的所有行,但表结构及其约束、索引等保持不变;drop会删除表的结构及其所依赖的约束、索引等。
  • truncate会重置表的自增值;delete不会。
  • truncate不会激活与表有关的删除触发器;delete可以。
  • truncate后会使表和索引所占用的空间会恢复到初始大小;delete操作不会减少表或索引所占用的空间,drop语句将表所占用的空间全释放掉。

到了这里,关于MySQL中drop、truncate和delete的区别的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【MySQL系列】ALTER语句详解,以及UPDATE,DELECT,TRUNCATE语句的使用+区别

    💐 🌸 🌷 🍀 🌹 🌻 🌺 🍁 🍃 🍂 🌿 🍄🍝 🍛 🍤 📃 个人主页 :阿然成长日记 👈点击可跳转 📆 个人专栏: 🔹数据结构与算法🔹C语言进阶 🚩 不能则学,不知则问,耻于问人,决无长进 🍭 🍯 🍎 🍏 🍊 🍋 🍒 🍇 🍉 🍓 🍑 🍈 🍌 🍐 🍍 前言: 上一篇博客讲

    2024年02月11日
    浏览(47)
  • IDEA、MySQL提示Truncated incorrect DOUBLE value报错解决方法

    “Truncated incorrect DOUBLE value” 的解决方法 主要是这四种 : 错误写法示例: 正确写法示例: 改成了 在字符串变量前后加了单引号

    2024年02月11日
    浏览(52)
  • MySQL面试系列-03

    事务是用户定义的一个数据库操作序列,这些操作要么全做要么全不做,是一个不可分割的工作单位,事务回滚是指将该事务已经完成的对数据库的更新操作撤销。 要同时修改数据库中两个不同表时,如果它们不是一个事务的话,当第一个表修改完,可能第二个表修改过程中

    2024年02月01日
    浏览(54)
  • MySQL面试题系列-6

    MySQL是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的RDBMS (Relational Database Management System,关系数据库管理系统)应用软件之一。 数据库的三范式 数据库的三范式是设计关系

    2024年04月11日
    浏览(75)
  • mysql面试题38:count(1)、count(*) 与 count(列名) 的区别

    该文章专注于面试,面试只要回答关键点即可,不需要对框架有非常深入的回答,如果你想应付面试,是足够了,抓住关键点 当使用COUNT函数进行数据统计时,有三种常见的用法:COUNT(1)、COUNT(*)和COUNT(列名)。它们的区别如下: COUNT(1):在COUNT函数中使用1作为参数,表示统计

    2024年02月07日
    浏览(30)
  • 【面试】Mysql主键索引普通索引索引和唯一索引的区别是什么?

    在 MySQL 中, 索引是在存储引擎层实现的, 所以并没有统⼀的索引标准, 由于 InnoDB 存储引擎在 MySQL数据库中使⽤最为⼴泛, 下⾯以 InnoDB 为例来分析⼀下其中的索引模型.在 InnoDB 中, 表都是根据主键顺序以索引的形式存放的, InnoDB 使⽤了 B+ 树索引模型,所以数据都是存储在 B+ 树

    2023年04月17日
    浏览(53)
  • 面试官:请说一下Mysql中count(1)、count(*)以及count(列)的区别?

    近期在Review项目代码时,发现同事们在查询MySQL行数时存在多样的方式,有的使用 COUNT(1) , 有的用 COUNT(id) , 还有人选择了 COUNT(*) 。这混杂的选择引发了我的思考。当然这三种count的方式也是众说纷纭,其中最大的分歧点就是 COUNT(*) 和 COUNT(1) 查询性能上,有人觉得 COUNT(*) 需要

    2024年02月19日
    浏览(39)
  • com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Out of range value for column

    一、报错信息 二、实体类及controller 三、请求  四、解决办法及原因分析 不存储这么大的数字或者修改数据库中的长度。原因分析:我要存储的长度不算小数点的10为长度,大于数据库中设计表的范围。数据库中的长度为10,小数点位数2位,也就是说,最大的数字为-99999999

    2024年02月12日
    浏览(39)
  • MySQL 中 Delete 使用

    Delete 是 SQL 中用于删除表格中记录的命令。通过使用 Delete 命令,您可以删除表格中的一行或多行记录,并将它们从表格中删除。在执行 Delete 命令时,务必保证所删除的数据符合表格中已有的约束条件,否则可能会引发不可预知的错误。 在 MySQL 中,Delete 命令的基本语法如下

    2024年02月08日
    浏览(26)
  • MySql Delete 使用及优化

    DELETE是一个DML语句,用于从表中删除行。 DELETE语句可以从with子句开始,以定义DELETE中可访问的公用表表达式。 单表语法 DELETE语句从tbl_name中删除行,并返回已删除的行数。要检查已删除的行数,请调用ROW_COUNT()函数。 可选WHERE子句中的条件标识要删除的行。如果没有WHE

    2024年02月11日
    浏览(28)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包