假设有数据库 whmblog,原本表前缀位whm_,现在要全部改成blog_
假设数据库whmblog有这些数据表
![MySQL统一修改数据表名前缀.png MySQL统一修改数据表名前缀.png](https://img-blog.csdnimg.cn/img_convert/27b8ac47967cccafca486579d444944e.png)
一、生成替换命令
说明,
命令中的 5:表示从whm_后面字符开始( 5 = ("whm_"的字符串长度+1));
table_schema:指定数据库名称(如whmblog);
SELECT CONCAT( 'ALTER TABLE ', table_name, ' RENAME TO blog_', substring(table_name, 5),';') FROM information_schema.tables where table_schema='whmblog' and table_name LIKE 'whm_%';
二、批量替换
执行第一步的命令后,会生成替换表名的命令。
![MySQL批量修改数据表前缀.png MySQL批量修改数据表前缀.png](https://img-blog.csdnimg.cn/img_convert/88d7f8f69d76cff060d3adeb2058e413.png)
批量执行下面的命令语句,就完成了批量修改表前缀。
ALTER TABLE whm_admin_menu RENAME TO blog_admin_menu;
ALTER TABLE whm_articles RENAME TO blog_articles;
ALTER TABLE whm_asset RENAME TO blog_asset;
ALTER TABLE whm_auth_access RENAME TO blog_auth_access;
ALTER TABLE whm_auth_rule RENAME TO blog_auth_rule;
ALTER TABLE whm_comment RENAME TO blog_comment;
ALTER TABLE whm_currency RENAME TO blog_currency;
ALTER TABLE whm_hook RENAME TO blog_hook;
ALTER TABLE whm_hook_plugin RENAME TO blog_hook_plugin;
ALTER TABLE whm_link RENAME TO blog_link;
ALTER TABLE whm_nav RENAME TO blog_nav;
ALTER TABLE whm_nav_menu RENAME TO blog_nav_menu;
ALTER TABLE whm_option RENAME TO blog_option;
ALTER TABLE whm_plugin RENAME TO blog_plugin;
ALTER TABLE whm_portal_category RENAME TO blog_portal_category;
ALTER TABLE whm_portal_category_post RENAME TO blog_portal_category_post;
ALTER TABLE whm_portal_post RENAME TO blog_portal_post;
ALTER TABLE whm_portal_record RENAME TO blog_portal_record;
ALTER TABLE whm_portal_tag RENAME TO blog_portal_tag;
ALTER TABLE whm_portal_tag_post RENAME TO blog_portal_tag_post;
ALTER TABLE whm_recycle_bin RENAME TO blog_recycle_bin;
ALTER TABLE whm_role RENAME TO blog_role;
ALTER TABLE whm_role_user RENAME TO blog_role_user;
ALTER TABLE whm_route RENAME TO blog_route;
ALTER TABLE whm_slide RENAME TO blog_slide;
ALTER TABLE whm_slide_item RENAME TO blog_slide_item;
ALTER TABLE whm_theme RENAME TO blog_theme;
ALTER TABLE whm_theme_file RENAME TO blog_theme_file;
ALTER TABLE whm_third_party_user RENAME TO blog_third_party_user;
ALTER TABLE whm_tool_category RENAME TO blog_tool_category;
ALTER TABLE whm_tool_like RENAME TO blog_tool_like;
ALTER TABLE whm_user RENAME TO blog_user;
ALTER TABLE whm_user_action RENAME TO blog_user_action;
ALTER TABLE whm_user_action_log RENAME TO blog_user_action_log;
ALTER TABLE whm_user_favorite RENAME TO blog_user_favorite;
ALTER TABLE whm_user_login_attempt RENAME TO blog_user_login_attempt;
ALTER TABLE whm_user_score_log RENAME TO blog_user_score_log;
ALTER TABLE whm_user_token RENAME TO blog_user_token;
ALTER TABLE whm_verification_code RENAME TO blog_verification_code;
执行完上面的命令之后,我们可以看到数据库中为表名前缀都改了
![TIM截图20200513164020.png TIM截图20200513164020.png](https://img-blog.csdnimg.cn/img_convert/e90089efb10031641bc305d9632bf918.png)