在MySQL中,修改表名可以使用如下语句:
ALTER TABLE 原表名 RENAME TO 新表名;
不过上面的一句SQL语句一次只能修改一张表,如果要批次修改多个表,上面的方法就不适用了。这时,我们可以使用 information_schema 中的 TABLES ,来实现批次修改的目的。
在MySQL中,把 information_schema 看作是一个数据库,确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。在INFORMATION_SCHEMA中,有数个只读表。它们实际上是视图,而不是基本表,因此,你将无法看到与之相关的任何文件。
而 information_schema 中的 TABLES 表,则提供了关于数据库中的表的信息(包括视图)。在 TABLES 中详细描述了某个表属于哪个schema,表名称,表类型,表引擎,创建时间等信息。
通过以下语句,可以列出对应数据库中的标
SHOW TABLES;
结果如下图:
基于以上介绍,我们可以通过 CONCAT 方法,拼接多条sql,实现批次修改表前缀和名称的功能。
1. 拼接sql语句(将表名前缀 tn_ 修改为 db_)
SELECT CONCAT('ALTER TABLE ',table_name,' RENAME TO db_',SUBSTRING(table_name, 4),';') FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND table_name LIKE 'tn%';
执行以上sql,结果如下图:
2. 复制上面结果中的sql语句,在mysql客户端的查询执行器中,执行上述sql语句。成功后,再执行“SHOW TABLES;”语句,可以看到表名称已经修改,如下图:
注意:
修改表名称,表引擎不能为 FEDERATED。如果表引擎为 FEDERATED,执行修改表名称,会出错“Table storage engine for '*****' doesn't have this option”。
3. 批量删除表
有了上面批量修改表名称的方法,批量删除表的sql语句就比较容易,如下:
SELECT CONCAT('drop table ',table_name,';') FROM information_schema. TABLES WHERE TABLE_SCHEMA='test' AND table_name LIKE 'db_%';
执行查询,会自动生成出 drop table table_name 这样的SQL语句,同样执行后,即可批量删除表。