PHP/MySQL Full Database Search and Replace
by irms
I wrote this because I often need to move data-driven sites from development to production and addresses change. (WordPress sites, WordPress MU, Drupal, and so on.) This means I have to go through and change every occurrence of the url as it appears in the database. So when the first few solutions for changing the site url aren’t doing the trick, I have to resort to the following MySQL snippet for updating every single table.
To find a string in a certain field and replace it with another string:
update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');
Well, I got tired of doing that and wrote a handy snippet to find and replace across an entire database.
There are no checks or safeguards! This is a quick and dirty script to be used at your own risk!
But if it’s useful, please feel free to say so in the comments.
UPDATE
Added two checkboxes:
- Set the script to never timeout (use with caution!)
- Do a case-insensitive find and replace. (use with caution!)
Download the updated .rar here
UPDATE 2
I just found a much better script than mine for moving WordPress installs, check it out here, and make sure you let them know that it was useful to you:
http://spectacu.la/search-and-replace-for-wordpress-databases/
Thanks for this. I installed it and tried it, about 3/4 of the way through it threw out some error (I cleared the screen so I don’t remember). Anyway it disabled my site and I had to restore it from a backup. I will save this though and hopefully it’ll work in the future.
Hi there, sorry about the delay in answering. If your database is large, the script probably timed out and that’s *probably* the error you saw. Try adding set_time_limit(0); to the top of the script. Thanks!
This is excellent. I only have one problem, when i want to replace a word it is case sensitive. For example when i want to replace Dog with Cat, I type in “Dog” then it does replace with “Cat” but i want it to replace all occurrences of “Dog”: dog, Dog, DOG. PLEASE HELP.
Providing you’re using INNODB tables, it’s handy to roll the whole operation into a transaction so that you don’t have to do a restore just because the transformation didn’t finish all the way through.
@Lyle: Try converting the first parameter of the replace function to lower case:
replace(LOWER([field_name]),’dog’,’cat’)
Now if you wanted to replace ‘Dog’ with ‘Cat’ and ‘dog’ with ‘cat’, you’ll just have to write as many variations as you need…
MySQL has regexp support, but I don’t see a regexp-supporting variation of replace(), which would have been handy.
Won’t hurt to mention that if you use it on your web server, it’s a good thing to remove it when you’re done….
Oh, and… This script, slightly modified, would also be handy to run from the command line:
$ php db-search-and-replace.php ‘dbname’ ‘host’ ‘user’ ‘pass’ ‘find-string’ ‘replace-string’
Simplest way to get the values of the passed parameters is in the $argv array (automatically populated when running from command line), a more elegant way is to use a PEAR class (I use Console_Getargs but there are others), or to prompt the user after starting the script.
Hi Lyle, Hi Mattais,
Those are great suggestions. I added an option to cause the script to run indefinitely. You should use this with great caution, and will only work if your web host supports set_time_limit(0)
The other is to do a search and replace that is case-insensitive.
I consolidated the scripts to a single file, for my own benefit, but Mattias is right. Very little tweaking, one can run the script from the command line. If anyone is interested in this version, I’d be happy to scribble that out.
Thanks!
irms
File not found. Please look into this
Hi there Ayden,
Should be fixed now.
Thanks!
irms
BEAUTIFULLY simple script. Thank you SO very much for putting that out there. Just saved me a massive amount of time on a WordPress move.
Thanks Steve! Glad you found it useful!
Simple, elegant and effective- bravo!
Thanks Cliff! Glad it was useful to you.
irms
Great script, but when I ran it, it converted EVERYTHING in my database to lowercase. Do you have any ideas on why that might be? (Lucky I did a backup!)
Hi Grant, Hi All,
I just found this script, which, I think, is much better than mine. Use it, and if you like it, make sure you let the author know.
http://spectacu.la/search-and-replace-for-wordpress-databases/
Very good sharing this.
I am generally not the kind of person to submit my opinion on people’s write ups, but for your write up I just needed to do it. I have been searching through your blog a lot recently and I’m really impressed, I think you might really emerge as one of the main opinions for your market. Not sure what your load is like in life, but if you started commiting more effort to writing here, I’d bet you would start getting a mass of visitors eventually. With advertisements, it could emerge as a sweet passive revenue stream. Just something to think about. Good luck!
[…] string’);If you need a database-wide search-and-replace, you could try this script (I haven’t tested/used it myself).Beware of the following gotchas:wrong query syntax may ruin […]
I landed here because I’m trying to find a way to replace one specific string pattern from the entire MySQL database (not just one table at a time).
Can you help me with that?
I know you probably haven’t updated this for a while. It’s 2018 and I’d like you to know that your script has helped me sort out a very sticky problem (db wide find replace). And after trying many different ‘solutions’ yours is the one that has worked. Will certainly be adding this script to my toolbox for future use. A Very BIG Thank You 😀