最新文章
关注我
在线咨询
x
有什么可以帮到你
点击咨询
MySQL批量修改数据表前缀
- 分类：MySQL技术
- 时间：2020-05-13
- 共240人围观
简介在项目中，我们的数据表名都会带前缀，一般情况下，数据库中的数据表不可能只有一个，如果要统一修改数据表前缀， 要怎么实现呢？
假设有数据库 whmblog，原本表前缀位whm_，现在要全部改成blog_
假设数据库whmblog有这些数据表
一、生成替换命令
说明，
命令中的 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_%';
二、批量替换
执行第一步的命令后，会生成替换表名的命令。
批量执行下面的命令语句，就完成了批量修改表前缀。
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;
执行完上面的命令之后，我们可以看到数据库中为表名前缀都改了
