WithCoderWithCoderWithCoder

MySQL批量删除表 修改表前缀与表名sql语句

    在MySQL中,修改表名可以使用如下语句:

ALTER TABLE 原表名 RENAME TO 新表名;

    不过上面的一句SQL语句一次只能修改一张表,如果要批次修改多个表,上面的方法就不适用了。这时,我们可以使用 information_schema 中的 TABLES ,来实现批次修改的目的。

    在MySQL中,把 information_schema 看作是一个数据库,确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。在INFORMATION_SCHEMA中,有数个只读表。它们实际上是视图,而不是基本表,因此,你将无法看到与之相关的任何文件。

    而 information_schema 中的 TABLES 表,则提供了关于数据库中的表的信息(包括视图)。在 TABLES 中详细描述了某个表属于哪个schema,表名称,表类型,表引擎,创建时间等信息。 

    通过以下语句,可以列出对应数据库中的标

SHOW TABLES;

    结果如下图:

    1-200F9100439340.png

    基于以上介绍,我们可以通过 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,结果如下图:

    1-200F91013131D.png

    2. 复制上面结果中的sql语句,在mysql客户端的查询执行器中,执行上述sql语句。成功后,再执行“SHOW TABLES;”语句,可以看到表名称已经修改,如下图:

     1-200F91025224L.png

    注意:

        修改表名称,表引擎不能为 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语句,同样执行后,即可批量删除表。


欢迎分享交流,转载请注明出处:WithCoder » MySQL批量删除表 修改表前缀与表名sql语句