如何批量修改mysql的表前缀名称

方法/步骤

命令行登陆MySQL，进入相应的数据库，本例是shopping（注：用其他工具也可以，比如Navicat for MySQL）。

输入查询表语句查看有哪些表：

select table_name FROM information_schema.tables

查询结果有许多表，以shopping开头的是我自己建立的，其他的是数据库系统自己建立的。

输入下面语句，查询以shopping开头的数据表：

select table_name FROM information_schema.tables where table_name like 'shopping_%'

输入下面语句：

Select CONCAT( 'ALTER TABLE ', table_name, ' RENAME TO buy_', substring(table_name,9 ),'' )FROM information_schema.tablesWhere table_name LIKE 'shopping_%'

注意：9表示从“shopping_”后面开始

拷贝出以alter开头的所有语句，用Navicat for MySQL工具更直观。

ALTER TABLE shopping__admin RENAME TO buy___adminALTER TABLE shopping__adminlog RENAME TO buy___adminlogALTER TABLE shopping__article RENAME TO buy___articleALTER TABLE shopping__basket RENAME TO buy___basketALTER TABLE shopping__book RENAME TO buy___bookALTER TABLE shopping__comment RENAME TO buy___commentALTER TABLE shopping__gb RENAME TO buy___gbALTER TABLE shopping__gou RENAME TO buy___gouALTER TABLE shopping__huo RENAME TO buy___huoALTER TABLE shopping__hw RENAME TO buy___hwALTER TABLE shopping__logo RENAME TO buy___logoALTER TABLE shopping__newsnsort RENAME TO buy___newsnsortALTER TABLE shopping__newssort RENAME TO buy___newssortALTER TABLE shopping__nsort RENAME TO buy___nsortALTER TABLE shopping__pay RENAME TO buy___payALTER TABLE shopping__payonline RENAME TO buy___payonlineALTER TABLE shopping__pinpai RENAME TO buy___pinpaiALTER TABLE shopping__ps RENAME TO buy___psALTER TABLE shopping__pub RENAME TO buy___pubALTER TABLE shopping__quan RENAME TO buy___quanALTER TABLE shopping__rep RENAME TO buy___repALTER TABLE shopping__rule RENAME TO buy___ruleALTER TABLE shopping__search RENAME TO buy___searchALTER TABLE shopping__sort RENAME TO buy___sortALTER TABLE shopping__sub RENAME TO buy___subALTER TABLE shopping__system RENAME TO buy___systemALTER TABLE shopping__totime RENAME TO buy___totimeALTER TABLE shopping__type RENAME TO buy___typeALTER TABLE shopping__user RENAME TO buy___userALTER TABLE shopping__usertype RENAME TO buy___usertypeALTER TABLE shopping__wrzcnet_ad RENAME TO buy___wrzcnet_adALTER TABLE shopping__wrzcnet_link RENAME TO buy___wrzcnet_linkALTER TABLE shopping__zp RENAME TO buy___zp

运行拷贝的语句：

执行完后，在用上面的查询语句查询一下：

select table_name FROM information_schema.tables where table_name like 'shopping_%'

发现没有数据，说明修改成功；修改查询语句：

select table_name FROM information_schema.tables where table_name like 'buy_%'

以wordpress数据库表为例。

先登入你的phpmyadmin中，选中你的wordpress数据库,选择sql出现如下图，然后输入sql命令Select CONCAT('ALTER TABLE ', table_name, ' RENAME TO ', table_name,'' )

FROM information_schema.tables

Where table_name LIKE 'wp_%'

得到结果：

ALTER TABLE wp_xyz RENAME TO wp_xyz

ALTER TABLE wp_opq RENAME TO wp_opq

(假设数据里有两个表： wp_xyz 、 wp_opq，前缀都是wp )

2、复制上面的结果到记事本，寻找RENAME TO wp_批量替换成

RENAME TO cool_wp（将前缀改成cool_前缀）

3、再复制记事本的内容，粘帖到phpmyadmin的sql运行框内，执行。

给你个方案：

php调用这个sql

select concat('rename table ',table_name,' to abc',right(table_name,14),'') from information_schema.tables where table_schema='dbname' and table_name like '%_category_tags'

生成的结果集如下：

+---------------------------------------------------------------------+

| concat('rename table ',table_name,' to abc',right(table_name,14),'') |

+---------------------------------------------------------------------+

| rename table 12_category_tags to abc_category_tags |

| rename table t2_category_tags to abc_category_tags |

| rename table df_category_tags to abc_category_tags |

| rename table ts_category_tags to abc_category_tags |

| rename table ttt_category_tags to abc_category_tags |

| rename table u_category_tags to abc_category_tags |

| rename table yu_category_tags to abc_category_tags |

+---------------------------------------------------------------------+

这些是修改表名的一些sql，你把这些值保存在php数组中，然后循环调用

但是有个问题哦，表名不能都改成abc_category_tags的，要么就是在原有表名前追加abc，这样表名就不会重复了

有问题可以追问。

---