How to change all the guid in posts table?

admin管理员组

文章数量:1278653

Recently I have transferred my WordPress from development server to live server. I noticed that I have to change all the "guid" with live domain url. Is there any mysql query or simple function available to change it easily.

Recently I have transferred my WordPress from development server to live server. I noticed that I have to change all the "guid" with live domain url. Is there any mysql query or simple function available to change it easily.

• posts
• guids

Share Improve this question asked Dec 8, 2015 at 12:35 KvvaradhaKvvaradha 93633 gold badges1212 silver badges2424 bronze badges 7

• Consult this page, at the bottom of this URL changing block you will find name of some plugins. :) – Mayeenul Islam Commented Dec 8, 2015 at 12:53
• Thank you. But I want to change the guid only. I have done other things manually. I thought some mysql query is available to change it – Kvvaradha Commented Dec 8, 2015 at 13:11
• 1 I wish WordPress would not embed URLs in the GUID. They are not URLs. You shouldn't need to change them (or most of them). See: wordpress.stackexchange/a/90209/21376 – s_ha_dum Commented Dec 8, 2015 at 14:08
• @s_ha_dum. So no need to change it right.? – Kvvaradha Commented Dec 8, 2015 at 14:18
• Most likely not @Kvvaradha You can change them as it sounds as though you haven't deployed the site yet and if so it won't cause any trouble, but I doubt you actually need to change them. – s_ha_dum Commented Dec 8, 2015 at 14:21

 |  Show 2 more comments

6 Answers 6

Sorted by: Reset to default 26

It should be something like:

UPDATE wp_posts SET guid = REPLACE(guid, 'oldurl', 'newurl') WHERE guid LIKE 'http://oldurl/%';

• oldurl - Previous URL shown in wordpress settings > general options
• newurl - New URL

To improve the previous answers you should update all relevant tables such as (here table prefix is wp_):

UPDATE `wp_posts` SET guid = REPLACE(guid, 'oldsiteurl', 'newsiteurl') WHERE guid LIKE 'oldsiteurl%';

UPDATE `wp_postmeta` SET meta_value = REPLACE(meta_value, 'oldsiteurl', 'newsiteurl') WHERE meta_value LIKE 'oldsiteurl%';

UPDATE `wp_options` SET option_value = REPLACE(option_value, 'oldsiteurl', 'newsiteurl') WHERE option_value LIKE 'oldsiteurl%';

If you got any plugins that do redirections you should also change the permalinks there:

UPDATE `wp_redirection_404` SET url = REPLACE(url, 'oldsiteurl', 'newsiteurl') WHERE url LIKE 'oldsiteurl%';

Use the WP-CLI to search and replace. The plugin MigrateDB also has a find and replace on export for the next time you transition.

I had forgotten that I wrote a SQL generator a few years back for this precise task:

https://farinspace.github.io/wp-migrate-gen/

Essentially the relevant SQL is as follows:

UPDATE `wp_options` SET `option_value` = REPLACE(`option_value`, 'a', 'b') WHERE `option_value` NOT REGEXP '^([adObis]:|N;)';
UPDATE `wp_posts` SET `post_content` = REPLACE(`post_content`, 'a', 'b');
UPDATE `wp_posts` SET `post_excerpt` = REPLACE(`post_excerpt`, 'a', 'b');
UPDATE `wp_posts` SET `guid` = REPLACE(`guid`, 'a', 'b');
UPDATE `wp_comments` SET `comment_author_url` = REPLACE(`comment_author_url`, 'a', 'b');
UPDATE `wp_comments` SET `comment_content` = REPLACE(`comment_content`, 'a', 'b');
UPDATE `wp_links` SET `link_url` = REPLACE(`link_url`, 'a', 'b');
UPDATE `wp_postmeta` SET `meta_value` = REPLACE(`meta_value`, 'a', 'b') WHERE `meta_value` NOT REGEXP '^([adObis]:|N;)';
UPDATE `wp_usermeta` SET `meta_value` = REPLACE(`meta_value`, 'a', 'b') WHERE `meta_value` NOT REGEXP '^([adObis]:|N;)';
UPDATE `wp_termmeta` SET `meta_value` = REPLACE(`meta_value`, 'a', 'b') WHERE `meta_value` NOT REGEXP '^([adObis]:|N;)';
UPDATE `wp_commentmeta` SET `meta_value` = REPLACE(`meta_value`, 'a', 'b') WHERE `meta_value` NOT REGEXP '^([adObis]:|N;)';

The queries above will check for and skip PHP serialized data.

Using the generator, you can turn off "skip serialized data" if the old and new string lengths match.

WARNING: I would recommend that you always create a backup of your data before running any queries that add/remove/update content.

WordPress documentation warns about changing guid entries in the WP database: "It is critical that you do NOT change the contents of this field."

https://wordpress/support/article/changing-the-site-url/#important-guid-note

As you mentioned, there are a few variables that need to be changed in order for you to update to the new URL on your WordPress site.

1. Go and download Interconnect IT's Database Search & Replace Script here
2. Unzip the file and drop the folder in your live server where your WordPress is installed (the root) and rename the folder to replace (screenshot)
3. Navigate to the new folder you created in your browser (ex: http://web.site/replace) and you will see the search/replace tool
4. It should be pretty self-explanatory up to this point: enter your old URL in the search for… field and the new URL in the replace with… field

You can click the dry run button under actions to see what it will be replacing before you execute the script. Once you're done be sure to remove the /replace/ folder.

本文标签： How to change all the guid in posts table