How to Find & Replace in MySQL

Sometimes, we need to find and replace certain strings or pieces of data in MySQL on the fly, for example if we’ve changed a Top Level domain (TLD) and want to update a bunch of links all in one go. For those times, the following command can be issued:



The code from the Gist above can be used, for example, to update strings contained in WordPress posts, such as a lot of links which you’d like to send to another domain. If you’d like specific code examples for doing this, please leave a public comment below, and I’ll update with this info.

UPDATE – for WordPress (and other CMS) – watch out for Serialized arrays

It is worth pointing out the cautionary tale here, and to say that, in WordPress, and WooCommerce, you should be very careful about doing a find and replace in this manner in the MySQL database – because of serialized arrays.

Serialized arrays are a special form of data storage which include a charcter count number in them. If you simply find and replace without updating the character count, you can run into problems. So don’t use this method in a WordPress website… do it another way!

You can use the plugins mentioned further down in this page to do this without running any SQL queries on your live database… including using dry runs first.

Other ways to find and replace which work with WordPress & WooCommerce sites

Thankfully, there are some nice plugins which allow you to find and replace within WordPress in a nice way, quietly updating the serialized array in the correct manner, and generally causing things not to fail, at all.

Recommended search and replace plugins for WordPress

Two we like for this task are:

Both plugins are made by Delicious Brains and they both do similar things, and do it “right”. So double thumbs up as a method for finding and replacing data in WordPress. This works really well if you just changed your URL or you made a change in structure and want to update all your internal linking, for example.

1 thought on “How to Find & Replace in MySQL”

  1. This is always a useful bit of code in case you need to update a lot of pages or posts in a WordPress (or other CMS) database all at the same time without having to go in and manually edit every single one!

    Reply

Leave a comment

No links of any kind allowed in comments - if you add any links, your comment will automatically be deleted.