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

前言

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

在 MySQL 中有三种删除数据的方式，分别是 delete、drop 和 truncate。这三种方式用起来简单，但是背后的原理和注意事项估计大部分开发人员并不清楚，下面我就来讲解以下原理以及注意事项。

一、delete

Delete 是数据库 DML 操作语言，在删除数据的时候只会删除数据而不会删除数据所在表的表结构，并且在删除数据的时候会执行事务和触发 trigger。而且如果你所使用的数据库引擎是 InnoDB 的话 MySQL 会在执行删除数据操作的时候只把数据标注为已删除而不去真正的删除数据，因此在这种情况下如果你使用 Delete 删除数据的时候会发现表大小并没有改变。

但是当你的数据库引擎是 MyISAM 的情况下执行 delete from table  语句会真正的删除表中的数据并释放空间的，如果你想让 MyISAM 引擎也能实现 InnoDB 引擎假删除的效果的话，你需要在 Delete 语句后面加上 Where 条件即可，例如 delete from user where age<18 这段语句执行后符合条件的数据将会标注为删除，并且所占空间不释放。

这时肯定有杠精的小朋友会说：“我就想使用 InnoDB 引擎，我也想释放被删除数据所占用的空间。”这种想法 MySQL 为我们提供了解决方案，那就是 optimize table 语句，我们来看一下例子：我们用 Delete 语句删除表中所有的数据后我们来查看以下现在这张表所占的空间大小（如下图）

从上图中我们可以看到我们删除了 book 表中的所有数据，但是该表所占用的空间大小几乎没发生变化。这时我们执行 optimize table 语句后再次查看表的空间大小，我们发现表的大小变成了 0.02M，只剩下了表结构的大小了（如下图）

我们在用 Delete 语句删除数据的时候需要注意的是 delete 操作是一行一行的执行的，并且会将每行的删除日志记录在 redo 和 undo 表空间中以方便日后回滚和重做，这样就产生大量的日志进而占用大量的空间。因此当我们在进项大规模删除数据的时候不建议使用 Delete 语句。

Tip:这里有一个比较有意思的地方：虽说被删除数据所占用的空间没有被释放，但是当我们执行 Insert 操作的时候这部分未被释放的空间依然是可以被重用的。

二、truncate 和 drop

truncate 是数据库 DDL 语言，它和 delete 最大的区别是它不走事务数据也不放在 rollback segment 里，同时也不会触发 trigger， 因此执行 truncate 语句后会立即删除指定的数据且无法恢复，并释放数据所占用的空间（不管使用的数据库引擎是 InnoDB 还是 MyISAM ）。truncate 语句一般用于快速清空表中数据并需要重置 auto_increment 值的情况。

关于重置 auto_increment 值需要有如下两点注意：

  • 在使用 InnoDB 引擎的情况下如果使用 truncate autoincrement会重置为 1 ，但是如果使用 delete 则 autoincrement 在重启 MySQL 之前仍然保持当前值，重启值后重置为 1 ；
  • 在使用 MyISAM 引擎的情况下如果使用 truncate autoincrement会重置为 1 ，但如果使用 delete 则 autoincrement 仍然保持当前值。

drop 同样是数据库 DDL 语言，它和 truncate 一样一旦执行将立即删除表和依赖、触发器、索引，但是会保留和该表相关的存储过程以及函数，并将状态改为 invalid 。

总结

上面讲这么多简单总结一下：如果把表比喻成一个包子的话，那么 delete 就是把馅从包子里拿出来放在别的盘子里，truncate 就是把馅从包子里拿出来吃掉，drop 就是直接把包子吃掉。

删库的技能学会了吗？

赞 (0)

相关推荐

  • 详解SQL中drop、delete和truncate的异同

    第一:相同点: truncate和不带where子句的delete,以及drop 都会删除表内的数据 第二:不同点: 1. truncate和delete只删除数据不删除表的结构(定义)     drop 语句将删除表的结构被依赖的约束(constrain).触发器(trigger).索引(index);依赖于该表的存储过程/函数将保留,但是变为 invalid 状态. 2. delete 语句是数据库操作语言(dml),这个操作会放到 rollback segement 中,事务提交之后才生效

  • 浅析删除表的几种方法（delete、drop、truncate）

    delete from 表名: 删除所有记录,表结构还在,写日志,可以恢复的,速度慢 drop talbe 表名: 删除表的结构和数据 truncate talbe 表名 删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快

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

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

  • SQL删除语句DROP、TRUNCATE、 DELETE 的区别

    DROP: DROP TABLE test; 删除表test,并释放空间,将test删除的一干二净. TRUNCATE: TRUNCATE test; 删除表test里的内容,并释放空间,但不删除表的定义,表的结构还在. DELETE: 1.删除指定数据 删除表test中年龄等于30的且国家为US的数据 DELETE FROM test WHERE age=30 AND country='US'; 2.删除整个表 仅删除表test内的所有内容,保留表的定义,不释放空间. DELETE FROM

  • 数据库中删除语句Drop、Delete、Truncate的相同点和不同点的比较(实例说明)

    数据库删除语句的分别介绍: Delete:用于删除表中的行(注:可以删除某一行:也可以在不删除表的情况下(即意味着表的结构.属性.索引完整)删除所有行) 语法:删除某一行:Delete From 表名称 Where 列名称=值 删除所有行: Delete From 表名称 或 Delete * From 表名称 Drop:用于删除表(注:将表的结构.属性.索引也会被删除.) 语法:Drop Table 表名称 Truncate:用于删除表内的数据(注:仅删除表内的数据,不删除表本身,相当于Del

  • 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,执行的时候将被触发,如果删除大数据量的表速

  • 秒懂drop、truncate和delete的区别

    (1)DELETE语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作. TRUNCATETABLE则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存,删除行是不能恢复的.并且在删除的过程中不会激活与表有关的删除触发器.执行速度快. (2)表和索引所占空间. 当表被TRUNCATE后,这个表和索引所占用的空间会恢复到初始大小, DELETE操作不会减少表或索引所占用的空间. drop语句将表所占用的空间全释放掉. (3)一般

  • sqlserver中drop、truncate和delete语句的用法

    sqlserver中drop、truncate和delete语句的用法

    虽然小编不建议大家去用命令删除数据库表中的东西,但是这些删除命令总有用的着的地方. 说到删除表数据的关键字,大家记得最多的可能就是delete了 然而我们做数据库开发,读取数据库数据.对另外的两兄弟用得就比较少了 现在来介绍另外两个兄弟,都是删除表数据的,其实也是很容易理解的 老大------drop 出没场合:drop table  tb --tb表示数据表的名字,下同 绝招:删除内容和定义,释放空间.简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表, 例如:一个班就是一个表

  • drop,truncate与delete的区别

    注意:这里说的delete是指不带where子句的delete语句 相同点 truncate和不带where子句的delete, 以及drop都会删除表内的数据 不同点: 1. truncate和 delete只删除数据不删除表的结构(定义) drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态. 2.delete语句是dml,这个操作会放到rollback segement中,

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

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

  • 删库跑路?使用xtraback备份MySQL数据库的方法

    一.mysqldump备份方式是采用逻辑备份.最大的缺陷就是备份和恢复的速度都慢,对于一个50G的数据库而言,这个速度还是可以接受的,但是如果数据库非常大,那在使用mysqdump备份就不是太合适了.. 这时候就需要一种很好用又高效的工具,xtraback 就是其中的一款,号称免费版的innodb hotbackup xtraback特点如下: 备份过程快速,可靠 备份过程不会打断正在执行的事务 能够基于压缩等功能节约磁盘空间和流量 自动实现备份检验 还原速度快 二.安装xtraback 1)下

  • mysql常用sql与命令之从入门到删库跑路

    启动与停止 启动mysql服务 sudo /usr/local/mysql/support-files/mysql.server start 停止mysql服务 sudo /usr/local/mysql/support-files/mysql.server stop 重启mysql服务 sudo /usr/local/mysql/support-files/mysql.server restart 进入mysql目录文件 cd /usr/local/mysql/support-files 进入

  • 关于Spring的@Transaction导致数据库回滚全部生效问题(又删库跑路)

    关于Spring的@Transaction导致数据库回滚全部生效问题(又删库跑路)

    1 前言 很多需要使用事务的场景,都只是在方法上直接添加个@Transactional注解 但是,你以为这真的够了吗? 事务如果未达到完美效果,在开发和测试阶段都难以被发现,因为你难以考虑到太多意外场景.但当业务数据量发展,就可能导致大量数据不一致的问题,就会造成前人栽树后人踩坑,需要大量人力排查解决问题和修复数据. 2 如何确认Spring事务生效了? 使用@Transactional一键开启声明式事务, 这就真的事务生效了?过于信任框架总有"意外惊喜".来看如下案例 领域层 实体

  • 防止删库跑路及高级代码投毒技巧

    目录 引言 删库跑路 可操作性 1. 权限控制 2. 数据备份 易发现性 影响后果 代码投毒 最近的案例 可操作性 预防策略 最后 引言 事情是这样的,最近在做开源软件供应链安全相关的项目,之前没了解这方面知识的时候感觉服务器被黑,数据库被删,网站被攻,这些东西都离我们太遥远了,因为感觉好像都轮不到我们,直到我开始做这个项目,才发现网络安全,软件安全问题真的是无处不在. 今天我们来聊聊删库跑路和代码投毒. 我们从可操作性及易发现性还有后果及预防策略上来探讨一下. 删库跑路 在软件行业,“删库跑路

  • mysql如何能有效防止删库跑路

    mysql如何能有效防止删库跑路

    目录 安全模式设置 测试 1.无where的update和delete 2.非索引键的delete 3.索引键的delete 总结 大家肯定听说过,有些开发者由于个人失误,在delete或者update语句的时候没有添加where语句,导致整个表数据错乱. mysql安全模式:mysql发现delete.update语句没有添加where或者limit条件时会报错.整个sql将无法执行,有效防止了误删表的情况. 安全模式设置 在mysql中通过如下命令查看状态: show variables l

  • 深入浅析MySQL从删库到跑路_高级（一）——数据完整性

    一.数据完整性简介 1.数据完整性简介 数据冗余是指数据库中存在一些重复的数据,数据完整性是指数据库中的数据能够正确反应实际情况. 数据完整性是指数据的可靠性和准确性,数据完整性类型有四种: A.实体完整性:实体的完整性强制表的标识符列或主键的完整性(通过唯一约束,主键约束或标识列属性). B.域完整性:限制类型(数据类型),格式(通过检查约束和规则),可能值范围(通过外键约束,检查约束,默认值定义,非空约束和规则). C.引用完整性:在删除和输入记录时,引用完整性保持表之间已定义的关系.引用完

  • 原来我一直安装 Python 库的姿势都不对呀

    平常我都是直接执行 pip install 安装的第三方库,很多教程也是这么介绍的,一直以来我都认为这是标准的.正确的安装 Python 第三方库的姿势.直到我最近看到Python核心开发者Brett Cannon 写的一篇文章. 简单总结来说,他认为其实最正确的安装方式是使用 python-m pip 来执行 pip 命令,无论什么情况下均是如此. 他在文章里大致提出了以下几个建议: 建议用 python-m pip 安装三方库 务必使用虚拟环境,不要安装至系统 Python 建议用 pipx

  • python GUI库图形界面开发之PyQt5信号与槽多窗口数据传递详细使用方法与实例

    在pyqt5编程过程中,经常会遇到输入或选择多个参数的问题,把多个参数写到一个窗口中,主窗口会显得很臃肿,所以,一般是添加一个按钮,调用对话框,在对话框中进行参数的选择,关闭对话框将参数返回给主窗口 pyqt提供了一些标准的对话框类,用于输入数据,修改数据,更改应用的设置等,常见的有QFileDialog,QInputDialog,QColorDialog, QFontDialog等,在不同的窗口之间传参数有两种常用的方式,一种在自定义对话框之间通过属性传参,另一种在窗口之间使用信号与槽机制传参

  • Vuex的基本概念、项目搭建以及入坑点

    前言:Vuex是一个专门为Vue.js应用程序开发的状态管理模式, 它采用集中式存储管理所有组件的公共状态, 并以相应的规则保证状态以一种可预测的方式发生变化. Vuex的四大核心 1.state 驱动应用的数据源 2.mutations 基因突变 类如C# 属性get set 3.actions 行为 4.getters 读取器 上图中绿色虚线包裹起来的部分就是Vuex的核心, state 中保存的就是公共状态, 改变 state 的唯一方式就是通过 mutations 进行更改. 可能你现在

随机推荐