MySQL:按前缀批量删除表格

想要实现mysql>drop table like "prefix_%"

没有直接可用的命令,不过可以通过mysql语法来组装,

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' )
AS statement FROM information_schema.tables
WHERE table_schema = 'database_name' AND table_name LIKE 'myprefix_%';

然后执行该组装后的命令,比如从slowquery表中删除掉所有tmp_开头的表:

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema.tables WHERE table_schema = 'slowquery' AND table_name LIKE 'tmp_%';

输出结果:

drop table tmp_201301,tmp_201302,tmp_201351,tmp_201352; 

执行之即可。

我自己现实中的例子:

/*
自动生成删除动态表SQL语句
用法:拷贝到查询分析器中执行,把结果拷贝出来再执行就OK了。
*/
SELECT REPLACE(GROUP_CONCAT(a.dropSql),',','') AS dropSql
FROM
(
SELECT CONCAT( 'drop table ', table_name, ';' ) AS dropSql
FROM information_schema.tables
WHERE table_name LIKE 'slavetablemgr%' UNION SELECT CONCAT( 'drop table ', table_name, ';' ) AS dropSql
FROM information_schema.tables
WHERE table_name LIKE 'DevicePlugin_%' UNION SELECT CONCAT( 'drop table ', table_name, ';' ) AS dropSql
FROM information_schema.tables
WHERE table_name LIKE 'historyrecordhour%' UNION SELECT CONCAT( 'drop table ', table_name, ';' ) AS dropSql
FROM information_schema.tables
WHERE table_name LIKE 'historyrecordminute%' UNION SELECT CONCAT( 'drop table ', table_name, ';' ) AS dropSql
FROM information_schema.tables
WHERE table_name LIKE 'historyrecordsecond%'
) a;
上一篇:Chrome 中的 JavaScript 断点设置和调试技巧 (转载)


下一篇:Unity 2D游戏开发高速入门第1章创建一个简单的2D游戏