MYSQL中的删除语句

1. 背景介绍

MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言--结构化查询语言（SQL）进行数据库管理。

“SQL” ----是用于访问和处理数据库的标准的计算机语言，简称结构化查询语言

SQL中的删除语句一共有四种,分别对应不同的功能

2. 四种删除语句

delete

delete from table_name where some_column=some_value

用于删除表中的行

delete from table_name/delete * from table_name;

用于删除表中所有的行,也就是删除表中所有的数据

drop

drop table table_name;

用于删除整个表,包括表结构

drop database database_name;

用于删除数据库

alter

（alter语句属于drop语句的分支）

alter table table_name drop column column_name;

用于删除表中的列

alter table table_name drop index index_name;

用于删除表中的索引

truncate

truncate table table_name;

用于删除表内的数据

 

3. 常见问题

四种删除语句中哪些功能是相同的?

哪些功能是不同的?

删除后可以恢复吗?哪些删除是可以恢复的?怎么恢复？

 

四种删除语句中哪些功能是相同的?

1.truncate和不带where子句的delete，以及drop都能删除表内的数据

2.truncate和delete只删除表数据保留表结构

 

哪些功能是不同的?

delete

1、delete语句为数据操作语言（DML,data maintain Language）,这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 trigger,执行的时候将被触发。

  并且同时将该行的的删除操作记录在undo和redo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，

  可以通过rollback撤消操作。

2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。

3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

truncate

1.truncate是数据定义语言（DDL,data define language）,操作立即生效，原数据不放到 rollback segment中，不能回滚

2.truncate table一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。

并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

truncate table比 delete 速度快，且使用的系统和事务日志资源少。

delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate table 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

drop

1、drop是数据定义语言（DDL,data define language），会隐式提交，所以，不能回滚，不会触发触发器。

2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。

3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

 

1.在速度上,一般来说,drop> truncate > delete.

2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。

3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；如果想删除表，当然用drop；

 

如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；

如果和事务有关，或者想触发trigger，还是用delete；

如果是整理表内部的碎片，可以用truncate跟上reuse storage，再重新导入/插入数据。

 

删除后可以恢复吗?哪些删除是可以恢复的?

数据库删除后的数据想要恢复分三种情况:

1.有没有备份?

2.有没有开启binlog?

3.如果两者都没开,只能使用第三方恢复软件,或者找专业的数据恢复公司

用备份语句恢复

备份：mysqldump -u root -p database_name  > E:/filename.sql

恢复：mysql  -u root -p database_name  < E:/filename.sql

选择性备份：mysqldump -u root -p database_name table1 table2 ...  > E:/filename.sql

全部备份：mysqldump -u root -p --all-databases > E:/filename.sql

用binlog恢复

binlog是二进制日志，简单的理解就是将数据库中的数据以二进制的形式记录下来，默认形式是hostname.00000x(x=1,2,3...)。

此外，mysqlbinlog生成的备份文件是动态备份。binlog日志会将我们对数据的所有操作都记录下来，

它就像是一个工作在后台的监视器，以一定的时间间隔查看我们在此段时间内执行的操作，并将之记录下来。

如何开启log_bin？（window系统）

mysql安装完后有个默认目录：C:\ProgramData\MySQL\MySQL Server 5.7 ，其下有个my.ini文件，在安装过程中该文件已经被配置好了，

字符集（utf8），datadir ，端口号3306......配置的很详细， 要开启log_bin，很简单，只需要将my.ini中#log-bin 前的 # 去掉即可

（二进制logbin文件存储的位置默认为：C:\ProgramData\MySQL\MySQL Server 5.7\Data）

mysql重启完后，然后我们借助：show variables like 'log_bin'; 便可查看log_bin是否开启

 

参考文献

冯亚超师兄PPT

http://blog.csdn.net/ws0513/article/details/49980547?%3E

http://blog.csdn.net/qq_34722749/article/details/69388376

更多讨论

怎么恢复数据库的自增id从1开始?

truncate tablename......

与delete的区别？

delete删除之后id会继续延续上一个

truncate删除之后从1开始id

若想保持记录呢？

可以直接删除id那一列

然后再加上新的id从1开始

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

作者：Princiman
链接：https://www.jianshu.com/p/1c5c75b86356