【批量清空表】“CONCAT”“TRUNCATE”与“information_schema”的使用

最新推荐文章于 2022-10-09 11:49:50 发布

蛙鳜鸡鹳狸猿

最新推荐文章于 2022-10-09 11:49:50 发布

阅读量2.7k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： # MySQL / MariaDB 文章标签： CONCAT TRUNCATE MySQL DELETE information_schema

本文链接：https://blog.youkuaiyun.com/sweeper_freedoman/article/details/51137181

MySQL / MariaDB 专栏收录该内容

87 篇文章

订阅专栏

本文详细介绍了如何使用SQL的CONCAT和information_schema功能高效地批量清空数据库表记录，包括拼接SQL语句、关闭外键检查、执行SQL及重新开启检查等步骤。同时对比了TRUNCATE和DELETE语句的区别，并提供了反单引号的使用技巧。

【背景】

工作中涉及到经常要为QA同学批量清空表记录，这里记录一下我的操作过程和遇到的问题，最后做一下小结。

【过程】

①.拼SQL

这个很简单，用“CONCAT”从“information_schema”里面获取“TABLE_NAME”拼成要执行的一句句SQL。

SELECT CONCAT("TRUNCATE TABLE `", t.TABLE_NAME, '`;') FROM information_schema.`TABLES` t
WHERE t.TABLE_NAME NOT IN(
    ……
    )
AND t.TABLE_NAME NOT LIKE "……"
AND t.TABLE_NAME REGEXP "……"
AND t.TABLE_SCHEMA = "……";

②.关闭外键检查

SET FOREIGN_KEY_CHECKS = 0;

③.执行过程①拼出来的SQL

④.打开外键检查

SET FOREIGN_KEY_CHECKS = 1;

【小结】

①“CONCAT” and “information_schema”

以前批量写SQL要么在Notepad里面手动批量写一下，要么去Excel里面用公式拼一下，而现在基本上都是用“CONCAT”，是一个强大的（批量）写SQL的SQL语句。关于“CONCAT”的详细介绍参见我的另一篇博文http://blog.youkuaiyun.com/sweeper_freedoman/article/details/54577186。“CONCAT”与MySQL的系统库“information_schema”结合，数据库操作简直6的飞起。

②“DELETE” or “TRUNCATE”

“DELETE”和“TRUNCATE”都是用来清空表数据，但一般情况下我都是用“TRUNCATE”。关于“TRUNCATE”的详细介绍参见我的另一篇博文http://blog.youkuaiyun.com/sweeper_freedoman/article/details/52553427。这篇文章我自己翻译了MySQL5.6官方文档关于“TRUNCATE”语句的部分。本来还想翻译“DELETE”语句部分，但是该部分太长了而且我英语不咋滴。但有一点，二者最大的区别就是“TRUNCATE”是DDL而“DELETE”是DML，它们之间的其他区别都是因此产生的。

③开关外键检查

为了保证数据的完整性和一致性，表之间的外键关联很有作用，但是在执行DML时，外键关联经常会掣肘。在批量删的过程中先关闭检查可以防止报错。参考上面关于“TRUNCATE”详细介绍的链接内容，有外键关系的InnoDB父子表须改用“DELETE”进行操作。

④反单引号' ` '的使用

[SQL] 
TRUNCATE TABLE question_1.0;
[Err] 1146 - Table 'question_1.0' doesn't exist

看看这个报错，从“information_schema”里面查询到的“TABLE_NAME”怎么可能不存在，唯一的可能性就是表名在SQL中被隔断了，这就涉及到MySQL分隔符反单引号“ `”的使用了。其实从操作系统层面、到数据库、再到编程语言，都有反单引号“ ` ”这家伙的烙印。关于它的详细介绍参见我的另一篇博文http://blog.youkuaiyun.com/sweeper_freedoman/article/details/52550743。