如何更改 MySQL 数据库中所有表的前缀？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2466101/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How I can change prefixes in all tables in my MySQL DB?
提问by SkyFox
My provider installed to my site Drupal CMS. Now I need copy all my data from old site. I have tables without prefixes in my old DB, but in new DB all tables have dp_[table_name]prefix.
我的提供程序安装到我的站点 Drupal CMS。现在我需要从旧站点复制我的所有数据。我的旧数据库中有没有前缀的表，但在新数据库中所有表都有dp_[table_name]前缀。
回答by Koen.
zerkms solution didn't work for me. I had to specify the information_schemadatabase to be able to query the Tablestable.
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';
Edit:
编辑：
Optimized the query to only call RENAME TABLE once. Something I walked into was the fact that the concatenated output got truncated at 341 characters. This can be solved (if allowed by your server) by setting the MySQL variable group_concat_max_lento a higher value:
优化查询，只调用 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.
回答by zerkms
write a script that will run RENAME TABLE for each table.
编写一个脚本，为每个表运行 RENAME TABLE。
SELECT
GROUP_CONCAT('RENAME TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', TABLE_SCHEMA, '`.`prefix_', TABLE_NAME, '`;' SEPARATOR ' ')
FROM
`TABLES` WHERE `TABLE_SCHEMA` = "test";
where "test" is expected database name
其中“test”是预期的数据库名称
after this you can long query that will add prefixes if you execute it ;-)
在此之后，您可以长查询，如果您执行它，将添加前缀;-)
回答by foray
PhpMyAdmin allows you to do this now. At the "Database" level select the Structure tab to see all the tables. Click 'check all' (below the table listing). On the 'With selected' dropdown choose: 'Replace table prefix'.
PhpMyAdmin 现在允许您执行此操作。在“数据库”级别选择“结构”选项卡以查看所有表。单击“全部检查”（在表格列表下方）。在“选择”下拉列表中选择：“替换表前缀”。
回答by Tomislav Nakic-Alfirevic
You can simply dump the database, open the dump with a text editor, replace all occurrences of "CREATE TABLE " with "CREATE TABLE dp_" and restore the database. It takes a couple of minutes to do.
您可以简单地转储数据库，使用文本编辑器打开转储，将所有出现的“CREATE TABLE”替换为“CREATE TABLE dp_”并恢复数据库。需要几分钟才能完成。
回答by Juli15
If there's someone out there yet wondering how to do this (as it did not work form me the other options) you can run this (changing the first three variables for your values, of course):
如果有人还想知道如何做到这一点（因为它在我的其他选项中不起作用），您可以运行它（当然，更改您的值的前三个变量）：
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;
And then you will be prompted with a bunch of queries needed in order to change all the tables in your database. You simply have to copy that, run it and voilá!
然后系统会提示您进行一系列查询，以便更改数据库中的所有表。你只需要复制它，运行它，瞧！
