在 MySQL 中删除动态管理的表

问题描述

我有一个包含许多表的 MySQL 数据库，其中一些是由软件动态创建的.动态表具有一致的命名方案:

I have a MySQL database that contains many tables, some of them created dynamically by the software. The dynamic tables have a consistent naming scheme:

dynamic_1
dynamic_2
...

但是，我不一定知道这些表有多少.

However, I don't necessarily know how many of these tables there are.

我希望能够在不删除整个数据库的情况下删除所有这些动态表.我希望能够纯粹在 SQL 中做到这一点.(存储过程没问题.)这甚至可能吗?

I'd like to be able to delete all of these dynamic tables without dropping the entire database. I'd like to be able to do this purely in SQL. (Stored procedures are ok.) Is this is even possible?

快速附录.

Haim 和 Alexandre 的解决方案效果很好，但我们都错过了一个特殊情况.如果有 -no- 动态表怎么办?在这种情况下，@v 将为 NULL，并且当我们尝试执行时会出现错误.我添加了第二个变量来处理这种情况:

The solution from Haim and Alexandre works great, but there's one special case we all missed. What if there are -no- dynamic tables? In that case @v will be NULL and we get an error when we try to execute. I added a second variable to handle this case:

SET @v = (SELECT CONCAT('drop table ', GROUP_CONCAT(a.table_name)) FROM information_schema.tables a where a.table_schema = DATABASE() AND a.table_name like 'dynamic_%');
SET @y = (SELECT IF (@V IS NOT NULL, @V, 'select 1'));
PREPARE s FROM @y;
EXECUTE s;

推荐答案

您可以运行此查询并获取您需要运行的所有 sql 查询；

you can run this query and get all the sql queries that you need to run;

select concat( 'drop table ', a.table_name, ';' )
from information_schema.tables a
where a.table_name like 'dynamic_%';

您可以将其插入到文件中

you can insert it to file like

INTO OUTFILE '/tmp/delete.sql';

根据亚历山大评论更新

SET @v = ( select concat( 'drop table ', group_concat(a.table_name))
    from information_schema.tables a
    where a.table_name like 'dynamic_%'
    AND a.table_schema = DATABASE()
;);
 PREPARE s FROM @v;
EXECUTE s;

这篇关于在 MySQL 中删除动态管理的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！