在 MySQL 中,我有多个表,其名称如下:
taggregate_temp_1364792160
taggregate_temp_1364795760
taggregate_temp_1364799360
taggregate_temp_1364802960
taggregate_temp_1364806560
taggregate_temp_1364810160
taggregate_temp_1364813760
taggregate_temp_1364817360
taggregate_temp_1364820960
taggregate_temp_1364824560
taggregate_temp_1364828160
taggregate_temp_1364831760
taggregate_temp_1364835360
taggregate_temp_1364838960
taggregate_temp_1364842560
taggregate_temp_1364846160
taggregate_temp_1364849760
taggregate_temp_1364853360
我需要立即删除所有以 taggregate_temp_ 开头的表
答案1
如果所有临时表都在mydb
,则运行以下命令:
SELECT CONCAT('DROP TABLE ',
GROUP_CONCAT(CONCAT(table_schema,'.',table_name)),';')
FROM information_schema.tables WHERE table_schema='mydb'
AND table_name like 'taggregate_temp%';
PREPARE s1 FROM @dropcmd; EXECUTE s1; DEALLOCATE PREPARE s1;
我已经做了我在 DBA StackExchange 上的另一篇文章中也使用了同样的技术。该帖子还演示了代码的执行。