mysql 一次删除多个备份表

show tables时，发现备份的表有点多，想要一个sql就删除

总不能drop table xx  ; 写多次吧。

方式一

1.生成删除某个数据库下所有的表SQL

-- 查询构建批量删除表语句（根据数据库名称）
select concat('drop table ', TABLE_NAME, ';') from information_schema.TABLES
where TABLE_SCHEMA = 'you_database';

2.生成删除某个数据库下指定的表名SQL

-- 查询构建批量删除表语句（根据数据库中的表名称模糊查询）
select concat('drop table ', TABLE_NAME, ';') from information_schema.TABLES
where TABLE_SCHEMA = 'you_database' and TABLE_NAME like 'delete_table_%';

3.复制查出来的删除sql语句，并批量执行。

操作：

SELECT CONCAT('DROP TABLE IF EXISTS ', GROUP_CONCAT(table_name SEPARATOR ', '), ';') 
FROM information_schema.tables
WHERE table_schema = 'mysqldb' AND table_name LIKE '%alarm_result_202%';

整个结果就来了

DROP TABLE IF EXISTS alarm_result_202108, alarm_result_202109, lalarm_result_202110, alarm_result_202111, alarm_result_202112, alarm_result_202201, alarm_result_202202, alarm_result_202203;

方式2

SELECT CONCAT('DROP TABLE IF EXISTS ', GROUP_CONCAT(table_name SEPARATOR ', '), ';') 
INTO @sql
FROM information_schema.tables
WHERE table_schema = '数据库名' AND table_name LIKE '要删除的表名';
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

mysql> SELECT CONCAT('DROP TABLE IF EXISTS ', GROUP_CONCAT(table_name SEPARATOR ', '), ';') 
    -> INTO @sql
    -> FROM information_schema.tables
    -> WHERE table_schema = 'mysqldb' AND table_name LIKE '%alarm_result_202%';
Query OK, 1 row affected (0.00 sec)

mysql>  
mysql> PREPARE stmt FROM @sql;
EXECUTE stmt;
Query OK, 0 rows affected (0.02 sec)
Statement prepared

mysql> EXECUTE stmt;
DEALLOCATE PREPARE stmt;Query OK, 0 rows affected (0.39 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)