Is there a way to find / replace a string across all tables in a mysql database?

Question

I'm thinking this isn't possible without doing a dump, searching / replacing within the .sql file, and then reimporting it, but figured I'd ask anyway...

Basically, is there a way to search for "samplestring" within all of the fields, within all of the tables, within one database and replace it with "examplestring"?

Answer 1

I don't know of one. There especially isn't if you're willing/want to look at and modify column names and other non-data stuff.

If you don't have to do it very often, it's not that problematic.

mysqldump --username user --password pass database | sed 's/somestring/otherstring/g' | mysql -uroot -p

Answer 2

You can, but it requires using dynamic SQL (MySQL's Prepared Statements).

First, you need to get a list of the text based columns:

SELECT c.column_name, c.table_name
  FROM INFORMATION_SCHEMA.COLUMNS c
 WHERE c.table_schema = your_db_name
   AND c.data_type IN ('varchar') -- don't want to replace on an INT/etc

Then you need to iterate over that list to create the UPDATE statement(s)...