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;  

执行输出的结果即可。

转载于:https://www.cnblogs.com/mengrulsy/articles/4713479.html