前两天博客域名从intermilan.cn换成了xuun.net，把后台的网址URL简单改改就可以了,没想到所有的上传图片都无法显示。鼠标移到图片上一看地址，域名还是老域名，看来光改改全局变量还是不够的，老的图片附件地址和上传的附件都一起捆绑保存到了mysql数据库里了(wordpress这点上还需要好好改进下啊)。
这也难不倒我，不就是几句SQL语言嘛，以前好歹也研究过，正打算找下语法复习下的时候，发现。。。网上已经有别人写过了。。。汗下 那就贴上来吧 呵呵 下面的所有标点符号都要改用英文半角，注意下下
首先介绍下SQL替换命令
UPDATE 表名 SET 字段 = REPLACE(字段,’替换内容’,'替换值’);
示例如下：
UPDATE wp_options SET option_value = REPLACE(option_value,'www.mystyle.info','mystyle.info');
注意上面的标点要都要用英文半角。其中wp_options就是表名，option_value就是表wp_options里的一个字段，wp_options里有siteurl和home的值。
一般只要执行以下命令，就可完成域名的修改：
修改option_value里的站点url和主页地址：
UPDATE wp_options SET option_value = replace(option_value, 'http://www.old-domain.com', 'http://www.new-domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';
更正文章中内部链接及附件的地址：
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.old-domain.com', 'http://www.new-domain.com');
更正wordpress文章默认的永久链接：
UPDATE wp_posts SET guid = replace(guid, 'http://www.old-domain.com','http://www.new-domain.com');
PS：后来一想，phpmyadmin里面直接备份一份到本地，随便一个文本文件把新旧域名复制粘贴下就ok了。。。。更简单了
下面再贴一篇文章留作存档
How to Move WordPress Blog to New Domain or Location
For blogger who self-hosts the WordPress blog publishing system on a web hosting server with own registered domain name, sometimes, you may decide to reorganize the blog link URL to make it tidier or to reflect new focus or theme of the blog. If you decide to change the URL or link location of your WordPress blog due to changing of domain name (such as from http://www.old-domain.com/ to http://www.new-domain.com/) or the blog to another directory location (such as from http://www.domain.com/ to http://www.domain.com/blog/), there are some steps that should be done to ensure the proper migration and no breaking links.
The tricky part when moving WordPress blog to another location is that WordPress is using absolute path in URL link instead of relative path in URL link location when stores some parameters in database. Within blog posts’ contents itself, users may also use the old URLs when creating reference backlinks. All these values in the database will need to be changed when WordPress is moved. The following guide will show you which database fields that has references or values related to blog’s URLs that you want to modify. Note that this guide is not about how to move WordPress blog from one server or host to another new hosting service.
Once the blog has been moved (all files copy over in case of moving location or server or new domain name properly propagated across Internet for new domain name), the first thing to change is to tell WordPress the new blog location (wp-config.php should be no changes, and .htaccess file should be also no changes. If for some reason mod_rewrite rules for friendly URLs no longer works, you can always regenerate the .htaccess file via WP Administration’s Update Permalinks page). This value can be changed via WordPress Options page, but if you no longer able to access to old blog URL, you have to modify the value via MySQL database.
Note: The guide uses SQL statements based on MySQL replace() function to modify the database. To run SQL queries, login to MySQL database that houses WordPress tables via phpMyAdmin or login to the DB server and run MySQL client as root.
To update WordPress options with the new blog location, use the following SQL command:
UPDATE wp_options SET option_value = replace(option_value, 'http://www.old-domain.com', 'http://www.new-domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';
After that you will need to fix URLs of the WordPress posts and pages, which translated from post slug, and stored in database wp_posts table as guid field. The URL values in this field are stored as absolute URLs instead of relative URLs, so it needs to be changed with the following SQL query:
UPDATE wp_posts SET guid = replace(guid, 'http://www.old-domain.com','http://www.new-domain.com');
If you have linked internally within blog posts or pages with absolute URLs, these links will point to wrong locations after you move the blog location. Use the following SQL commands to fix all internal links to own blog in all WordPress posts and pages:
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.old-domain.com', 'http://www.new-domain.com');
Browse through WordPress blog to check if everything is okay. You also need to re-login to WP Administration as authentication cookie has now became invalid due to different domain.
发表回复