WordPress更换域名的数据库SQL语句 批量查询快速替换

经常会遇到类似的问题,因为主机老是搬来搬去的。下面把这个SQL语句记录在这里,你也可以随便复制,我保持网站可以访问。

1
2
3
UPDATE wp_options SET option_value = REPLACE(option_value,'old domain','new domain') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET post_content = REPLACE(post_content,'old domain','new domain');
UPDATE wp_posts SET guid = REPLACE(guid,'old domain','new domain');

值得注意的是,域名的后面不要带上斜杠—— “/”,比如将qq.com 替换成 pianpai.com ,就是下面的语句。

1
2
3
UPDATE wp_options SET option_value = REPLACE(option_value,'qq.com','pianpai.com') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET post_content = REPLACE(post_content,'qq.com','pianpai.com');
UPDATE wp_posts SET guid = REPLACE(guid,'qq.com','pianpai.com');

总共替换三个地方。
值得注意的是:如果你的数据表前缀是自定义的不是wp_,那么你需要改下这个语句里面的wp_为你自定义的。比如我的叫go_ 那上面的那句语句应该叫做

1
2
3
UPDATE go_options SET option_value = REPLACE(option_value,'old domain','new domain') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE go_posts SET post_content = REPLACE(post_content,'old domain','new domain');
UPDATE go_posts SET guid = REPLACE(guid,'old domain','new domain');

2020.3.10补充

1
2
3
4
5
6
7
8
UPDATE wp_options SET option_value = REPLACE(option_value,'old domain','new domain') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET post_content = REPLACE(post_content,'old domain','new domain');
UPDATE wp_posts SET guid = REPLACE(guid,'old domain','new domain');
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value,'old domain','new domain'); //替换所有meta_value
UPDATE wp_comments SET comment_author_url = REPLACE(comment_author_url,'old domain','new domain');

//如果需要替换自定义域的某个字段的值,主要在于meta_key
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value,'old domain','new domain') WHERE meta_key = "zidingyi_mingcheng";

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this post. TrackBack URL