如何更改 MySQL 数据库中所有表的前缀？

Question

我的提供商安装到我的网站 Drupal CMS。现在我需要从旧站点复制所有数据。我的旧数据库中有没有前缀的表，但在新数据库中，所有表都有 dp_[table_name] 前缀。

Answer 1

zerkms 解决方案对我不起作用。我必须指定 information_schema 数据库才能查询 Tables 表。

SELECT 
    CONCAT('RENAME TABLE ', GROUP_CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', TABLE_SCHEMA, '`.`prefix_', TABLE_NAME, '`')) AS q
FROM 
    `information_schema`.`Tables` WHERE TABLE_SCHEMA='test';

编辑：

优化查询以仅调用 RENAME TABLE 一次。我遇到的问题是，连接的输出被截断为 341 个字符。这可以通过将 MySQL 变量group_concat_max_len 设置为更高的值来解决（如果您的服务器允许）：

SET group_concat_max_len = 3072; -- UTF8 assumes each character will take 3 bytes, so 3072/3 = 1024 characters.

Answer 2

PhpMyAdmin 现在允许您执行此操作。在“数据库”级别，选择“结构”选项卡以查看所有表。单击“全部检查”（在表格列表下方）。在“选择”下拉列表中选择：“替换表前缀”。

Answer 3

编写一个将为每个表运行 RENAME TABLE 的脚本。

SELECT 
  GROUP_CONCAT('RENAME TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', TABLE_SCHEMA, '`.`prefix_', TABLE_NAME, '`;' SEPARATOR ' ')
FROM 
  `TABLES` WHERE `TABLE_SCHEMA` = "test";

其中“test”是预期的数据库名称，

在此之后您可以进行长查询，如果您执行它，它将添加前缀;-)

Answer 4

只是稍微修改了一下，以解决前缀也出现在表名称中的情况。

SET @database   = "database_name"; 
SET @old_prefix = "old_prefix_"; 
SET @new_prefix = "new_prefix_";
   SELECT
    CONCAT(
        "RENAME TABLE ",
        TABLE_NAME,
        " TO ",
        CONCAT(@new_prefix, TRIM(LEADING @old_prefix FROM TABLE_NAME)),
        ';'
    ) AS "SQL" FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;

Answer 5

您可以简单地转储数据库，使用文本编辑器打开转储，将所有出现的“CREATE TABLE”替换为“CREATE TABLE dp_”并恢复数据库。需要几分钟才能完成。

Answer 6

如果有人还想知道如何执行此操作（因为它对我来说其他选项不起作用），您可以运行此命令（当然，更改您的值的前三个变量）：

SET @database   = "database_name"; 
SET @old_prefix = "old_prefix_"; 
SET @new_prefix = "new_prefix_";
   SELECT
    concat(
        "RENAME TABLE ",
        TABLE_NAME,
        " TO ",
        replace(TABLE_NAME, @old_prefix, @new_prefix),
        ';'
    ) AS "SQL" FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;

然后系统会提示您一堆更改数据库中的所有表所需的查询。您只需复制它，运行它，瞧！

Answer 7

SET @database   = "Database-Name-Here";
SET @old_prefix = "wp_";
SET @new_prefix = "ab_";
   SELECT
    CONCAT(
        "RENAME TABLE ",
        @database,
        ".",
        TABLE_NAME,
        " TO ",
        @database,
        ".",
        CONCAT(@new_prefix, TRIM(LEADING @old_prefix FROM TABLE_NAME)),
        ';'
    ) AS "SQL" FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;

将 "Database-Name-Here" 替换为您的数据库名称，将 "wp_" 替换为旧前缀，将 "ab_" 替换为新的前缀。
前面的代码将生成对 MySQL DB 的查询，而且上面代码创建的查询是用单引号引起来的，必须将其替换。但是，如果您正在使用 WordPress，则需要执行更多步骤，否则您的网站将被破坏。在上述代码创建的查询之后，必须执行以下查询。

update NEWPREFIX_usermeta set meta_key = 'NEWPREFIX_capabilities' where meta_key = 'OLDPREFIX_capabilities';
update NEWPREFIX_usermeta set meta_key = 'NEWPREFIX_user_level' where meta_key = 'OLDPREFIX_user_level';
update NEWPREFIX_usermeta set meta_key = 'NEWPREFIX_autosave_draft_ids' where meta_key = 'OLDPREFIX_autosave_draft_ids';
update NEWPREFIX_options set option_name = 'NEWPREFIX_user_roles' where option_name = 'OLDPREFIX_user_roles'

将 "NEWPREFIX" 替换为新前缀，将 "OLDPREFIX" 替换为旧前缀。