MySQL基本操作——删除数据各类情况总结

  • 一、删除单张表&按条件删除表中行数据
  • 二、删除数据库中所有表且不保留表结构
  • 三、一次性删除整个数据库中所有表数据并保留表结构
  • 附录：information_schema.tables基本介绍
  • 1. 查询所有的数据库信息
  • 2. 查询数据库和数据表信息
  • 3. 数据表大小以及索引大小
  • 4. 判断myisam数据表是否已压缩
  • 通过Linux指令直接获取数据库和数据表信息：


一、删除单张表&按条件删除表中行数据

MYSQL中TRUNCATE和DELETE都能够清理表中的数据，具体试用场景及二者之间的区别如下：

mysql数据删除后 数据表没变小 mysql删除表里的数据_database

1. 条件删除：

DELETE是可以带WHERE的，所以支持条件删除；而TRUNCATE只能删除整个表。

# delete - 条件删除
DELETE FROM student WHERE id = 1;
# delete - 删除整个表的数据
DELETE FROM student;
# truncate - 删除整个表的数据
TRUNCATE TABLE student;

2. 事务回滚：

由于DELETE是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollback segment中，可以被回滚；而TRUNCATE是数据定义语言（DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚。

START TRANSACTION;  # 开启事务
ROLLBACK;    # 事务回滚
  • DELETE回滚实验

可以看到DELETE是可以回滚成功的。

  • TRUNCATE回滚实验

    可以看到TRUNCATE是不能回滚成功的。

3. 清理速度：

效率：delete效率低于truncate，delete是一行一行地删除，truncate会重建表结构

在数据量比较小的情况下，DELETE和TRUNCATE的清理速度差别不是很大。但是数据量很大的时候就能看出区别。由于第二项中说的，TRUNCATE不需要支持回滚，所以使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项，固然会慢，但是相对来说也较安全。

4. 高水位重置：

随着不断地进行表记录的DML操作，会不断提高表的高水位线（HWM），DELETE操作之后虽然表的数据删除了，但是并没有降低表的高水位，随着DML操作数据库容量也只会上升，不会下降。所以如果使用DELETE，就算将表中的数据减少了很多，在查询时还是很和DELETE操作前速度一样。
而TRUNCATE操作会重置高水位线，数据库容量也会被重置，之后再进行DML操作速度也会有提升。

参考链接：MYSQL中TRUNCATE和DELETE的区别

二、删除数据库中所有表且不保留表结构

# 删除某个数据库
DROP DATABASE 数据库名

如果不需要保留数据库中所有表的结构，那么答案很简单，执行命令drop database 数据库名 即可达到目的。

三、一次性删除整个数据库中所有表数据并保留表结构

如果需要保留该数据库中所有表的结构，只想删除所有表中的数据，又该怎么解决呢？有人会说可以多执行几次truncate操作就OK啦，没错，多执行几次truncate确实可以达到目的，但是，如果要删除的数据库中有很多张表，几十张上百张表，执行上百次truncate操作显然不是好的办法？

解决方法：

删除的办法其实还是执行truncate方法，只是不需要每次手动的输入truncate命令进行删除。通过sql命令的方式生成所有的truncate语句并写入到【指定的.sql脚本文件】

第一步：查询指定数据库下所有Tables，并组装成【删除Table的SQL语句】

在MySQL中，执行如下SQL查询语句：

SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') FROM information_schema.TABLES WHERE TABLE_SCHEMA='zn_django01'   # TABLE_SCHEMA修改为欲删除的数据库name

mysql数据删除后 数据表没变小 mysql删除表里的数据_mysql_02


第二步：将第一步查询的临时表数据导出，保存为truncate_test.sql文件

该步操作也可以和第一步合并，在MySQL中，执行如下SQL查询语句，实现查询并导出临时表文件。

SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') FROM information_schema.TABLES WHERE TABLE_SCHEMA='zn_django01' into outfile '/tmp/truncate_test.sql';

在MySQL中执行select....into outfile......操作时，默认只能将文件写入到tmp路径下。

第三步：将第二步生成的.sql脚本拷贝到当前文件夹下面

将第二步生成的.sql脚本拷贝到当前文件夹下面，执行如下shell语句：

mv /tmp/truncate_test.sql $current_dir/

第四步：在终端运行第三步生成的.sql文件

然后执行.sql脚本将数据库中所有表中数据删除，执行语句如下：

source $current_dir/truncate_test.sql

参考链接：一次性删除Mysql数据库中所有表的数据，保留表结构

附录：information_schema.tables基本介绍

information_schema.tables基本介绍：

对于mysql和Infobright等数据库，information_schema数据库中的表都是只读的，不能进行更新、删除和插入等操作，也不能加触发器，因为它们实际只是一个视图，不是基本表，没有关联的文件。
information_schema.tables存储了数据表的元数据信息，下面对常用的字段进行介绍：

  • table_schema: 记录 数据库名 ；
  • table_name: 记录 数据表名 ；
  • engine : 存储 引擎 ；
  • table_rows: 关于表的粗略行估计；
  • data_length : 记录 表的大小 （单位字节）；
  • index_length : 记录 表的索引的大小 ；
  • row_format: 可以查看 数据表是否压缩过 ；

1. 查询所有的数据库信息

select distinct TABLE_SCHEMA from information_schema.TABLES;

效果等价于：

show DATABASES;

mysql数据删除后 数据表没变小 mysql删除表里的数据_database_03

2. 查询数据库和数据表信息

SELECT TABLE_SCHEMA ,table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA='zn_django01'

效果等价于：

use zn_django01;
show tables;

查询结果如下：

mysql数据删除后 数据表没变小 mysql删除表里的数据_mysql数据删除后 数据表没变小_04

mysql数据删除后 数据表没变小 mysql删除表里的数据_数据库_05

3. 数据表大小以及索引大小

（1）获取指定数据库的大小：

select (sum(DATA_LENGTH) + sum(INDEX_LENGTH)) as size from information_schema.TABLES where table_schema='zn_django01'

（2）获取time_zone相关表的大小：

select (sum(DATA_LENGTH) + sum(INDEX_LENGTH)) as size from information_schema.TABLES where table_schema='zn_django01' and table_name like 'time_%';

4. 判断myisam数据表是否已压缩

select distinct row_format,engine from information_schema.tables where engine='myisam'

查询结果如下：

mysql数据删除后 数据表没变小 mysql删除表里的数据_数据库_06

  • Fixed: 表示已压缩；
  • Dynamic：表示未压缩；

通过Linux指令直接获取数据库和数据表信息：

通过Linux指令直接获取数据库和数据表信息：

mysql -uroot -pxxxx -D information_schema -e "select TABLE_SCHEMA ,table_name from tables where table_schema like 'hsm_syslog_%'"

参数说明：

  • -D：表示数据库名称；
  • -e：表示需要执行的指令

参考链接：【mysql元数据库】使用information_schema.tables查询数据库和数据表信息