A simple global find/replace for mySQL

This isn’t my area of expertise by any means, but I stumbled across a very simple technique today that may very well save you hours and hours of tedious nastyness one day.

The situation was this: I had a table that contained a record with a bunch of text stored in it. I needed to turn _some_ of the text into a link. The problem was that each record contained more than one link to be added. There were 10 links in all, with some records containing one link, and some containing six.

I know enough about mySQL to use phpMyadmin to do a sql dump and run that dump. That’s it. I asked a couple of my buddies online if they could help, and “the master”:http://www.snook.ca/jonathan stepped up. Long story short, we weren’t able to get a sql query working to add the relevant links – I had to head to a meeting, and Jon did have to make a living at some point.

I had backed the db up by running a sql dump (in phpmyadmin, go to export, then making sure both *structure* and *data* are selected, select *save as file* and hit go. Save the file). When I went to restore the data after having mucked it up, I realized that rather than trying to craft a sql query to make the changes, I could just use any old text editor with a FIND/REPLACE function to edit the sql dump.

Five minutes later, I had made all the changes I needed. Next it was simply a matter of highlighting the records and sql commands for the table I had edited, pasting them into the sql query text box (under the SQL tab) and hitting go.

Done, done, and done.

I can think of about a hundred other uses for this. For example, I have a client who’s name uses an umlaut (the two little dots) over the letter o. Their site is riddled with misspellings. Since it’s built using a CMS, I was dreading having to go through each and every article to find the offending character and replace it with the proper unicode. I was dreading it so much I wasn’t going to even mention it! Now, 10 minutes and its done.

Are there any downsides to this? Clearly anything that gets written to the db between the time that one exports the sql dump and runs the query will be lost. In my case, it’s a non-issue. Anything else that I (and my army of fellow db-ignoramuses) should be looking out for?