How to easily search/replace in MySQL database?

February 6, 2007 9:09 AM

Is there an easy, preferably open source way of editing MySQL table contents? I was hoping for something which would let me use Excel (or OO Calc) to edit the live database. I need to search/replace many items in a poorly imported database.

I've tried Google, but I find there are many convoluted answers and old software floating around. OO base is pretty neat, and I got it to work with my MySQL, but I can't easily search/replace within the base application. Importing to Calc hangs my OO.

It's taking me longer to figure out how to search/replace than doing it one entry at a time. Yes, I have backed up the database before tinkering.

posted by Yavsy to computers & internet (10 comments total)

Is this a case where there are string values within some fields that need replacing?

If so, MySQL has a string replace function called replace (not to be confused with the record replacing function of the same name):

Some examples here.
posted by justkevin at 9:21 AM on February 6, 2007

What platform are you working on? Windows? Mac has CocoaMySQL and Aqua Data Studio. I think Windows has Webyog and Navicat, both of which will help you...
posted by SpecialK at 9:23 AM on February 6, 2007

Um, or, you know, UPDATE set field = 'new_value' WHERE field = 'search_value'
Or, LIKE '%search_value%'

Etc.
posted by disillusioned at 9:36 AM on February 6, 2007

phpMyAdmin
posted by ReiToei at 9:39 AM on February 6, 2007

Mac has CocoaMySQL and Aqua Data Studio

Aqua Data Studio is Windows (Java) too.

Navicat is commercial, I think.

There's also the possibility of using MS Access if you're on windows. See here and here. I'm not sure, but this might also work with Excel since it's my understanding Excel can pull/push to a database via odbc as well.

A few years ago, I used mysqlcc to do some casual editing, but I'm not sure the tool still exists anymore in the form it took then. There's a different set of tools on the MySQL website, and the only place I can find something by the name mysqlcc is this sourceforge page.
posted by weston at 9:47 AM on February 6, 2007

I'd suggest exporting the whole thing as a text file (using either the built-in mysqldump program, or PHPMyAdmin, or any other GUI tool), loading it into a text editor and doing the search and replace there, then re-importing it.

Alternately, a command like this will do search and replace in place:

UPDATE table SET column=REPLACE(column,'search string','replacement string');

(NB no easy undo method for the above)
posted by cillit bang at 10:15 AM on February 6, 2007

Why not use the MySQL Query Browser? It's specifically made for MySQL, and it's GPL'd. It allows you to edit fields of a table directly, and also does a lot of things for which it's not worth remembering the query syntax.
posted by Plutor at 11:19 AM on February 6, 2007

If there are bulk changes to make, I second experimenting with editing a dump as a text document. It's somewhat gnarly in practice -- grep is more dangerous when you're familiar with the syntax than when you're a raw newbie -- but you benefit from seeing whole screens full of edits instantly, and you're not affecting the source data until you import your changes.
posted by ardgedee at 12:51 PM on February 6, 2007

If you like MSFT Access, as your first example suggested, then you might like to add a ODBC source in your Windows control panel and then use that ODBC source from MSFT Access. Simple.

You could also use the MySQL CSV table type. Create a new table, CREATE TABLE ... SELECT FROM ... with an engine type of "CSV" and then copy that data file from the storage engine and load it with Excel. When finished, export the CSV (pick the right format) and copy it back to the engine area and hopefully MySQL should read it.

(I'm not speaking for MySQL with this advice. For the second
paragraph, I'd be rather surprised if it worked without some effort. YMMV.)
posted by cmiller at 2:23 PM on February 6, 2007

I second the MySQL Query Browser. It's what i use and it works great.
posted by escher at 2:50 PM on February 6, 2007