我怎样才能改变我的MySQL数据库中的所有表的前缀?[英] How I can change prefixes in all tables in my MySQL DB?

本文是小编为大家收集整理的关于我怎样才能改变我的MySQL数据库中的所有表的前缀?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我的提供商已安装到我的网站Drupal CMS.现在,我需要从旧站点复制所有数据.我有旧DB中没有前缀的桌子,但是在新的DB中,所有表都有dp_[table_name]前缀.

推荐答案

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';

编辑:

优化查询以仅调用重命名表一次.我走进的事情是,串联输出以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.

其他推荐答案

phpMyAdmin允许您立即执行此操作.在"数据库"级别上,选择"结构"选项卡以查看所有表.单击"检查全部"(表列表下方).在"选择"下拉列表上选择:"替换表前缀".

其他推荐答案

编写一个将为每个表运行重命名表的脚本.

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

预计数据库名称的"测试"

之后,您可以长时间查询,如果执行它,将添加前缀; - )

本文地址:https://www.itbaoku.cn/post/597637.html

问题描述

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.

推荐答案

zerkms solution didn't work for me. I had to specify the information_schema database to be able to query the Tables table.

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_len to a higher value:

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

其他推荐答案

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'.

其他推荐答案

write a script that will run RENAME TABLE for each 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

after this you can long query that will add prefixes if you execute it ;-)