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

Question

我的供应商在我的网站上安装了Drupal CMS，现在我需要复制所有旧网站的数据。在我的旧数据库中，我有没有前缀的表，但在新的数据库中，所有表都有dp_[表名]前缀。

- SkyFox

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.

- Koen.

---

真的，这对我很有用，因为我正在使用Joomla在本地主机XAMPP上工作，并且我想更改为自动Joomla前缀。我按照您的步骤进行操作 - 我必须像您一样指定information_schema.Tables并在我的情况下设置SET group_concat_max_len = 10240;，因为有许多表格。+1 - Mohammed Joraid

还有一点，您可以使用相同的命令将表移动到其他数据库（例如备份数据库）中，如下所示： SELECT CONCAT('RENAME TABLE ',TABLE_NAME,' TO backup_db.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'testbot'; - sactiw

关于group_concat_max_len变量的编辑是个好点子！我之前也没有得到完整的句子，现在很好用！ - AlexGH

Answer 2

PhpMyAdmin现在可以帮助您完成这项操作。在“数据库”级别上，选择“结构”选项卡以查看所有表格。点击“全选”（在表格列表下方）。 在“选择项操作”下拉菜单中选择：“替换表格前缀”。

- foray

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"是期望的数据库名称。

之后，您可以执行长查询，并添加前缀，如果您执行它的话；-）

- zerkms

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;

- fsjk85

Answer 5

您可以简单地倒出数据库，使用文本编辑器打开倒出文件，将所有的“CREATE TABLE”替换为“CREATE TABLE dp_”，然后还原数据库。这只需要几分钟时间。

- Tomislav Nakic-Alfirevic

---

直到数据库小于几个千兆字节为止 ;-) - zerkms

我准备好了。 :) 对于大于1GB的数据库，请使用查询工具（例如SQL Workbench），在表列表中选择所有表，将内容复制到电子表格的A列中（在我的情况下是Calc），并将以下公式放入B列：=“rename table”& A1＆“to dp_”& A1＆“;”。将B1单元格中的公式粘贴到每个其他B列单元格中，重命名脚本将出现在B列中！ - Tomislav Nakic-Alfirevic

正如我在答案中所说 - 重命名的适当方法是使用RENAME TABLE（+ information_shema来检索表名） - zerkms

或者你可以使用Eclipse并将“CREATE TABLE”全部替换为“CREATE TABLE dp_”。 - steelshark

@Pooya 当然，你是对的，但我不认为扩展这种方法来涵盖那些内容会太费力了。它不会像我上面描述的那样只需要2分钟就能完成，但是遵循使用DDL / DML SQL脚本实现整体命名更改的原则，可能可以在10-15分钟内完成。 - Tomislav Nakic-Alfirevic

Answer 6

如果还有人想知道如何做到这一点（因为其他选项对我来说都不起作用），您可以运行以下内容（当然，需要根据您的值更改前三个变量）：

Original Answer翻译成"最初的回答"

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;

然后您将被提示需要的一堆查询，以便更改数据库中的所有表。您只需复制它，运行它，就完成了！

原始答案：最初的回答。

- Juli15

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”替换为您的旧前缀。

- Kashif Saeed