TAGS
The following was originally published on WordCast and authored by Lorelle VanFossen. It is reprinted here as a reference guide.
These are just a few of the reasons why you need to know how to do a search and replace in the WordPress MySQL database. Some people choose to export their entire WordPress site’s data and edit it in a text editor, but for those with hundreds or thousands of posts, it’s easier to do it in the MySQL database.
Playing with your WordPress MySQL database is not for the weak of heart and faint of code. There is much to fear. The worse case scenario is that you blow up your site. The best scenario is that you fix some problems that have been plaguing you. I’ve some warnings below, but trust that even someone with little coding experience can do this, if you are very, very careful and take precautions.
The following search and replace and delete WordPress-related queries for MySQL are included:
This guide will tell you how to search and replace in the WordPress database manually, however it comes with some caveats. Please read through these carefully.
You test frequently between search and replaces to ensure your site is still functioning and no errors are reported. For multiple search and replace efforts, after testing, backup a new version just in case the next change you make breaks things. You only have to restore one or two steps back. You sincerely trust your ability to use code on your database. Do this only if you are willing to live with the consequences and wasted time and effort if you mess things up. There are a variety of tools you can use before you should dig into your WordPress database. The , , and WordPress Plugins may be considered as an alternative to manually executing a search and replace in the WordPress database. The is another non-WordPress Plugin option. These are WordPress-based tools, so they restrict search and replace access to specific WordPress tables. NOTE: These examples in this article feature a variety of table prefixes, such as wp_ (default), mywp_, and mywp_18_, are representative of a secure WordPress or multiple site network installation. Please check thoroughly for your table prefix and use that in the code. The Process of Searching and Replacing in the MySQL Database The process to search and replace in the MySQL database is as follows, with specific examples below. Go to phpMyAdmin, cPanel, or other method to open your WordPress database and find the tables for your specific WordPress installation. NOTE: Individual WordPress blog tables typically havea table prefix of wp_posts. Check yours to see if it uses a unique table prefix such as mywp_posts. If you are using the multisite version of WordPress (Blog Network or WordPressMU), the tables are designated by Blog ID number such as wp_18 or mywp_18_posts. Check thoroughly to ensure you are making changes to the correct tables and site. Use the following search and replace command structure: UPDATE tablename SET `fieldname` = REPLACE (`fieldname`, 'Item to replace here', 'Replacement text here'); NOTE: Around the table and/or fieldname are backticks created by pressing the key to left of number 1 on your keyboard. The apostrophe single quote marks must be around the text to replace and replacement text. These are required in recent versions of MySQL. Check that everything is correct – there is no going back or undoing. Click GO to begin the search and replace. The results will inform you of how many records were changed and permit you to view the changed records. The search andreplace features in MySQL are limited, making the process of doing global or wildcard changes frustrating. Things you cannot do with a MySQL search and replace include: Cross table search and replaces. Search and replace field names (values yes, names no). Search and replace using wildcards (but you can use the argument for some searches). Search for values in one row and use them in another. Here are examples of what you can do within the WordPress MySQL database. Search and Replace Blog Content To search and replace within the post content area of WordPress, use the above guides and the following code, checking that the table and field names match exactly to the blog you wish to change. UPDATE wp_posts SET `post_content` = REPLACE (`post_content`, 'Dancing Quietly in the Night', 'Walking Loudly in the Day'); To change a URL within the post content area of WordPress, such as a site you commonly link to changing domain names or you change your own, use the following. URLs such aswill only change the domain name to UPDATE wp_posts SET `post_content` = REPLACE (`post_content`, ' ' When you redesign your site, you often want to change something in the styles of the post content. For instance, many add the external style to outgoing links within their site to make these appear different from intrasite links, warning readers that this link will take them off their site so they may want to open the link in a new tab or not. The change to a link would be: <a href="title="Link offsite">link text</a> to <a class="external" href="title="Link offsite">link text</a> The problem with such a search and replace is the lack of a unique search term. If you just search and replace every instance of <a href=" with <a class="external" href=", every link on your site would be changed. So a little game needs to be carefully played to make this work. Identify intrasite links with a CSS class for internal. You can use any word you wish, such as home, homesite, sitelinks, orsomething more SEO friendly like MySiteName. Do a search and replace for all links on your site that includes your site’s domain name URL. UPDATE wp_posts SET `post_content` = REPLACE (`post_content`, '<a href=" '<a class="internal" href=" Only links with URLs linking to your site would be changed. Do another search and replace on all the other links to add the external CSS class. UPDATE wp_posts SET `post_content` = REPLACE (`post_content`, '<a href=" '<a class="external" href=" By making the internal links unique and different from outgoing links, an environment is created for a quick search and replace. When You’re Not Sure of the Unique Value to Search and Replace When working with a phrase or multiple words, it’s often hard to know if you set them in the sequence of word1 word2 word3 or word2 word1 word3. If you are having trouble seeing the redundant unique values, run a search to look for “like” terms, then make your search and replace decisions accordingly. Forinstance, if you want to change two CSS styles but you aren’t sure if you used them as class="style1 style2" or class="style2 style1, you can run a LIKE search to filter out the possibilities and create your search and replace accordingly. SELECT * FROM mywp_posts WHERE ( post_content LIKE '% style1"%' OR post_content LIKE '% style2%' OR post_content LIKE '%style1 %' OR post_content LIKE '%style2 %' ); This would give you a search result that you could study to see which sequence you used, or not, and create a search and replace accordingly so that the style orders would all be the same, such as: UPDATE wp_posts SET `post_content` = REPLACE (`post_content`, 'class="style2 style1"', 'class="style1 style2"'); If you then wanted to add a third style or take one away, you would have a consistent set across all of the database. Let’s add a style in this example. UPDATE wp_posts SET `post_content` = REPLACE (`post_content`, 'class="style1 style2"', 'class="style1 style2style3"'); Search and Replace Domain Names If you change your site’s domain name, it helps to change it everywhere. MySQL doesn’t permit cross table or field search and replace actions, so you have to do this one at a time. You have to change the domain name in the post content, comments, excerpts, user URLs, emails, and other post meta data tables. Here is a good list of the queries, initiated individually. UPDATE wp_posts SET `post_content` = REPLACE (`post_content`, 'www.example.com', 'www.newexample.com'); UPDATE wp_posts SET `post_excerpt` = REPLACE (`post_excerpt`, 'www.example.com', 'www.newexample.com'); UPDATE wp_users SET `user_url` = REPLACE (`user_url`, 'www.example.com', 'www.newexample.com'); UPDATE wp_users SET `user_email` = REPLACE (`user_email`, '@example.com', '@newexample.com'); UPDATE wp_options SET `option_value` = REPLACE (`option_value`, 'www.example.com', 'www.newexample.com'); UPDATE wp_postmeta SET `meta_value` = REPLACE
.
