WordPress数据库改名

发表于2013年10月01日 星期二 21:49:13由rexdf 1,052 浏览数
目录
[隐藏]

今天突然想把默认的那个wp_数据库表前缀改一下，按照网上的方案一般就是直接在原始数据库上面改。但是考虑到可能改坏数据库，因为我不希望这么做。当然改数据库表名可以使用phpMyAdmin或者使用”alter table 表名 rename to 新表名” 都是可以的，但是改完之后还需要修改几个值，我特地装了一个新的Wordpress来重新做了一个表来验证，下面语句应该是对的(假设想改成blog_)：

UPDATE blog_usermeta SET meta_key ='blog_capabilities' WHERE meta_key ='wp_capabilities';
UPDATE blog_usermeta SET meta_key ='blog_user_level' WHERE meta_key ='wp_user_level';
UPDATE blog_options SET option_name='blog_user_roles' WHERE option_name='wp_user_roles';

然后我记起以前在做一个SQL Server的时候用的方法，导出成为SQL脚本然后修改脚本，然后修改脚本，但是此法我暂时还没有调试成功，问题较多，主要是编码问题；因为我不需要更换数据库位置，所以突然明白只需要复制表就好了，数据库空间我基本没怎么用，于是最终采用了第二种方法。

1.SQL脚本修改法

通过phpMyAdmin导出wp_的表，二进制一定要选择十六进制格式，由于Wordpress Statistics插件的wp_statistics_visitor较大有4.5M，而且每分每秒都在增加行，开始一起导出所有wp_开头的数据库，使用wp_的通配符替换文本之后，无法执行，有12M+的大小。
然后把wp_statistics_visitor单独导出，wp_posts也单独导出了，然后余下的一起导出，分成三个文件。这时我也意思到存在一个问题，就是我曾经研究过Wordpress的数据库结构然后发过文章，所以wp_posts替换一定要使用严格的模式，比如替换 INSERT INTO `wp_posts` VALUES，而不是简单地替换wp_甚至wp_posts，当然这个的前提是我文章中没有这个语句，不然就得使用正则表达式替换行首了。
花了大约半个钟头把全部的前缀替换好了，包括那个需要改值的都一起替换了，实际上还是不止上面的三条，我发现的有六处(欢迎大家补充)：

UPDATE blog_usermeta SET meta_key ='blog_user-settings' WHERE meta_key ='wp_user-settings';
UPDATE blog_usermeta SET meta_key ='blog_user-settings-time' WHERE meta_key ='wp_user-settings-time';
UPDATE blog_usermeta SET meta_key ='blog_dashboard_quick_press_last_post_id' WHERE meta_key ='wp_dashboard_quick_press_last_post_id';
UPDATE blog_usermeta SET meta_key ='blog_user_level' WHERE meta_key ='wp_user_level';
UPDATE blog_usermeta SET meta_key ='blog_capabilities' WHERE meta_key ='wp_capabilities';
UPDATE blog_options SET option_name='blog_user_roles' WHERE option_name='wp_user_roles';

2.数据库复制法

这个方法优化了前面提到的各个问题直接写成了一个脚本，执行之后把wp_config.php中的$table_prefix改成blog即可了。

CREATE TABLE `blog_posts` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `post_author` bigint(20) unsigned NOT NULL default '0',
  `post_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL default 'publish',
  `comment_status` varchar(20) NOT NULL default 'open',
  `ping_status` varchar(20) NOT NULL default 'open',
  `post_password` varchar(20) NOT NULL default '',
  `post_name` varchar(200) NOT NULL default '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content_filtered` longtext NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL default '0',
  `guid` varchar(255) NOT NULL default '',
  `menu_order` int(11) NOT NULL default '0',
  `post_type` varchar(20) NOT NULL default 'post',
  `post_mime_type` varchar(100) NOT NULL default '',
  `comment_count` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  KEY `post_name` (`post_name`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=MyISAM AUTO_INCREMENT=1828 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1828 ;

insert into blog_posts select * from wp_posts;

CREATE TABLE `blog_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL auto_increment,
  `comment_id` bigint(20) unsigned NOT NULL default '0',
  `meta_key` varchar(255) default NULL,
  `meta_value` longtext,
  PRIMARY KEY  (`meta_id`),
  KEY `comment_id` (`comment_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM AUTO_INCREMENT=4631 DEFAULT CHARSET=utf8 AUTO_INCREMENT=4631 ;

insert into blog_commentmeta select * from wp_commentmeta;

CREATE TABLE `blog_comments` (
  `comment_ID` bigint(20) unsigned NOT NULL auto_increment,
  `comment_post_ID` bigint(20) unsigned NOT NULL default '0',
  `comment_author` tinytext NOT NULL,
  `comment_author_email` varchar(100) NOT NULL default '',
  `comment_author_url` varchar(200) NOT NULL default '',
  `comment_author_IP` varchar(100) NOT NULL default '',
  `comment_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `comment_content` text NOT NULL,
  `comment_karma` int(11) NOT NULL default '0',
  `comment_approved` varchar(20) NOT NULL default '1',
  `comment_agent` varchar(255) NOT NULL default '',
  `comment_type` varchar(20) NOT NULL default '',
  `comment_parent` bigint(20) unsigned NOT NULL default '0',
  `user_id` bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (`comment_ID`),
  KEY `comment_post_ID` (`comment_post_ID`),
  KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
  KEY `comment_date_gmt` (`comment_date_gmt`),
  KEY `comment_parent` (`comment_parent`)
) ENGINE=MyISAM AUTO_INCREMENT=1566 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1566 ;

insert into blog_comments select * from wp_comments;

CREATE TABLE `blog_links` (
  `link_id` bigint(20) unsigned NOT NULL auto_increment,
  `link_url` varchar(255) NOT NULL default '',
  `link_name` varchar(255) NOT NULL default '',
  `link_image` varchar(255) NOT NULL default '',
  `link_target` varchar(25) NOT NULL default '',
  `link_description` varchar(255) NOT NULL default '',
  `link_visible` varchar(20) NOT NULL default 'Y',
  `link_owner` bigint(20) unsigned NOT NULL default '1',
  `link_rating` int(11) NOT NULL default '0',
  `link_updated` datetime NOT NULL default '0000-00-00 00:00:00',
  `link_rel` varchar(255) NOT NULL default '',
  `link_notes` mediumtext NOT NULL,
  `link_rss` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`link_id`),
  KEY `link_visible` (`link_visible`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

insert into blog_links select * from wp_links;

CREATE TABLE `blog_options` (
  `option_id` bigint(20) unsigned NOT NULL auto_increment,
  `option_name` varchar(64) NOT NULL default '',
  `option_value` longtext NOT NULL,
  `autoload` varchar(20) NOT NULL default 'yes',
  PRIMARY KEY  (`option_id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=MyISAM AUTO_INCREMENT=90783 DEFAULT CHARSET=utf8 AUTO_INCREMENT=90783 ;

insert into blog_options select * from wp_options;

CREATE TABLE `blog_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL auto_increment,
  `post_id` bigint(20) unsigned NOT NULL default '0',
  `meta_key` varchar(255) default NULL,
  `meta_value` longtext,
  PRIMARY KEY  (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM AUTO_INCREMENT=3210 DEFAULT CHARSET=utf8 AUTO_INCREMENT=3210 ;

insert into blog_postmeta select * from wp_postmeta;

CREATE TABLE `blog_postviews_plus` (
  `count_id` varchar(32) collate utf8_unicode_ci NOT NULL,
  `add_time` int(10) unsigned NOT NULL,
  `tv` varchar(255) collate utf8_unicode_ci NOT NULL,
  `gt` varchar(255) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`count_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert into blog_postviews_plus select * from wp_postviews_plus;

CREATE TABLE `blog_statistics_useronline` (
  `ID` int(11) NOT NULL auto_increment,
  `ip` varchar(20) NOT NULL,
  `timestamp` int(10) NOT NULL,
  `date` datetime NOT NULL,
  `referred` text NOT NULL,
  `agent` varchar(255) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=168026 DEFAULT CHARSET=utf8 AUTO_INCREMENT=168026 ;

insert into blog_statistics_useronline select * from wp_statistics_useronline;

CREATE TABLE `blog_statistics_visit` (
  `ID` int(11) NOT NULL auto_increment,
  `last_visit` datetime NOT NULL,
  `last_counter` date NOT NULL,
  `visit` int(10) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=210 DEFAULT CHARSET=utf8 AUTO_INCREMENT=210 ;

insert into blog_statistics_visit select * from wp_statistics_visit;

CREATE TABLE `blog_term_relationships` (
  `object_id` bigint(20) unsigned NOT NULL default '0',
  `term_taxonomy_id` bigint(20) unsigned NOT NULL default '0',
  `term_order` int(11) NOT NULL default '0',
  PRIMARY KEY  (`object_id`,`term_taxonomy_id`),
  KEY `term_taxonomy_id` (`term_taxonomy_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into blog_term_relationships select * from wp_term_relationships;

CREATE TABLE `blog_term_taxonomy` (
  `term_taxonomy_id` bigint(20) unsigned NOT NULL auto_increment,
  `term_id` bigint(20) unsigned NOT NULL default '0',
  `taxonomy` varchar(32) NOT NULL default '',
  `description` longtext NOT NULL,
  `parent` bigint(20) unsigned NOT NULL default '0',
  `count` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`term_taxonomy_id`),
  UNIQUE KEY `term_id_taxonomy` (`term_id`,`taxonomy`),
  KEY `taxonomy` (`taxonomy`)
) ENGINE=MyISAM AUTO_INCREMENT=155 DEFAULT CHARSET=utf8 AUTO_INCREMENT=155 ;

insert into blog_term_taxonomy select * from wp_term_taxonomy;

CREATE TABLE `blog_terms` (
  `term_id` bigint(20) unsigned NOT NULL auto_increment,
  `name` varchar(200) NOT NULL default '',
  `slug` varchar(200) NOT NULL default '',
  `term_group` bigint(10) NOT NULL default '0',
  PRIMARY KEY  (`term_id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=145 DEFAULT CHARSET=utf8 AUTO_INCREMENT=145 ;

insert into blog_terms select * from wp_terms;

CREATE TABLE `blog_usermeta` (
  `umeta_id` bigint(20) unsigned NOT NULL auto_increment,
  `user_id` bigint(20) unsigned NOT NULL default '0',
  `meta_key` varchar(255) default NULL,
  `meta_value` longtext,
  PRIMARY KEY  (`umeta_id`),
  KEY `user_id` (`user_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM AUTO_INCREMENT=118 DEFAULT CHARSET=utf8 AUTO_INCREMENT=118 ;

insert into blog_usermeta select * from wp_usermeta;

CREATE TABLE `blog_users` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `user_login` varchar(60) NOT NULL default '',
  `user_pass` varchar(64) NOT NULL default '',
  `user_nicename` varchar(50) NOT NULL default '',
  `user_email` varchar(100) NOT NULL default '',
  `user_url` varchar(100) NOT NULL default '',
  `user_registered` datetime NOT NULL default '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) NOT NULL default '',
  `user_status` int(11) NOT NULL default '0',
  `display_name` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`ID`),
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

insert into blog_users select * from wp_users;

CREATE TABLE `blog_wm_timeline` (
  `mid` int(11) NOT NULL,
  `tid` char(32) collate utf8_unicode_ci NOT NULL,
  `md5` binary(16) default NULL,
  `gmtime` int(10) unsigned NOT NULL,
  `text` text character set utf8 NOT NULL,
  `other` text character set utf8,
  PRIMARY KEY  (`mid`,`tid`),
  UNIQUE KEY `md5` (`md5`),
  KEY `mid` (`mid`,`gmtime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert into blog_wm_timeline select * from wp_wm_timeline;

CREATE TABLE `blog_statistics_visitor` (
  `ID` int(11) NOT NULL auto_increment,
  `last_counter` date NOT NULL,
  `referred` text NOT NULL,
  `agent` varchar(255) NOT NULL,
  `ip` varchar(20) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=60866 DEFAULT CHARSET=utf8 AUTO_INCREMENT=60866 ;

insert into blog_statistics_visitor select * from wp_statistics_visitor;


UPDATE blog_usermeta SET meta_key ='blog_user-settings' WHERE meta_key ='wp_user-settings';
UPDATE blog_usermeta SET meta_key ='blog_user-settings-time' WHERE meta_key ='wp_user-settings-time';
UPDATE blog_usermeta SET meta_key ='blog_dashboard_quick_press_last_post_id' WHERE meta_key ='wp_dashboard_quick_press_last_post_id';
UPDATE blog_usermeta SET meta_key ='blog_user_level' WHERE meta_key ='wp_user_level';
UPDATE blog_usermeta SET meta_key ='blog_capabilities' WHERE meta_key ='wp_capabilities';
UPDATE blog_options SET option_name='blog_user_roles' WHERE option_name='wp_user_roles';

 

本文链接：WordPress数据库改名

转载声明：本站文章若无特别说明，皆为原创，转载请注明来源：Rexdf，谢谢！^^


此条目发表在网站建设分类目录，贴了标签。将固定链接加入收藏夹。

发表回复

您的电子邮箱地址不会被公开。

CAPTCHA

*

Actionscript3 | AppleScript | Bash shell | C | C# | C++ | CSS | ColdFusion | Delphi | Diff | Erlang | Groovy | HTML | Java | JavaFX | JavaScript | PHP | Pascal | Patch | Perl | Plain Text | PowerShell | Python | Ruby | Ruby on Rails | SQL | Sass | Scala | Scss | Shell | Visual Basic | Visual Basic .NET | XHTML | XML | XSLT |

:zsmilebig: :zsadbig: :zwiredbig: :zgreenhappy: more »

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据。