项目场景:
使用Navicat工具直接在界面中删除,只能单张表删除,不能多选
解决方案:
我们可以通过MySQL的语句来批量删除多个表,其中you_database替换成你要查询的数据库名字 delete_table改成你要删除匹配的数据表。
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 ',table_name,';') from information_schema.tables where table_schema = 'rummy_log' and table_name like 'agent_game_bonus_flow_202111%'; drop table agent_game_bonus_flow_20211110; drop table agent_game_bonus_flow_20211111; drop table agent_game_bonus_flow_20211112; drop table agent_game_bonus_flow_20211113; drop table agent_game_bonus_flow_20211114; drop table agent_game_bonus_flow_20211115; drop table agent_game_bonus_flow_20211116; drop table agent_game_bonus_flow_20211117; drop table agent_game_bonus_flow_20211118; drop table agent_game_bonus_flow_20211119; drop table agent_game_bonus_flow_20211120; drop table agent_game_bonus_flow_20211121; drop table agent_game_bonus_flow_20211122; drop table agent_game_bonus_flow_20211123; drop table agent_game_bonus_flow_20211124; drop table agent_game_bonus_flow_20211125; drop table agent_game_bonus_flow_20211126; drop table agent_game_bonus_flow_20211127; drop table agent_game_bonus_flow_20211128; drop table agent_game_bonus_flow_20211129; drop table agent_game_bonus_flow_20211130;