mysql 批量删除相同前缀的表 批量修改表名

Posted on Posted in mysql

虚拟主机只给了一个库,不同的工程用了不同的前缀,最后可以批量清理

select CONCAT( 'drop table ', table_name, ';' ) FROM information_schema.tables Where table_name LIKE 'prefix_%';

此语句会生成一串 drop table 语句,需要再执行这些语句才可以删除

相同道理,批量修改表名

Select CONCAT( 'ALTER TABLE ', table_name, 'RENAME TO ', table_name,';' ) FROM information_schema.tables Where table_name LIKE 'prefix_%';

此时会生成

ALTER TABLE de_a RENAME TO de_a; 
ALTER TABLE de_b RENAME TO de_b;

再批量替换 RENAME TO de

Leave a Reply

Your email address will not be published. Required fields are marked *

15 − seven =