# Query to find and replace text in all tables and fields of a mysql db

I need to run a query to find and replace some text in all tables of a mysql database.

I found this query, but it only looks for the text in the tbl_name table and just in the column field.

update tbl_name set column=REPLACE(column, 'fuschia', 'fuchsia');


I need it to look in all tables and all fields: (everywhere in the database)

-
You can get an idea here –  dezso Jul 19 '12 at 12:30
This is a bit strange though, can you elaborate as why you need to do this, I am only curious. –  Emmad Kareem Jul 19 '12 at 12:57
Then I have to loop every table. Thanks, I get it done. @dezco: would you mind to change your comment to an answer so that I can accept it. –  chefnelone Jul 19 '12 at 14:12

You need to use the information_schema database to generate the script.

Collect all columns from every table of every database that have the following criteria:

• Exclude the following databases:
• information_schema
• performance_schema
• mysql
• COLUMN_TYPE values with one of the following characteristics:
• starts with CHAR(
• starts with VARCHAR(
• ends with TEXT (TEXT, MEDIUMTEXT, LONGTEXT)

Here is the query to get those columns

SELECT table_schema,table_name,column_name FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
AND (column_type LIKE 'char(%' OR column_type LIKE 'varchar(%' OR column_type LIKE '%text');


Using the above query, construct a set of queries that outputs SQL for converting fuschia to fuchsia:

SELECT CONCAT('UPDATE ',table_schema,'.',table_name,
' SET ',column_name,'=REPLACE(',column_name,',''fuschia'',''fuchsia'');')
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
AND (column_type LIKE 'char(%'
OR column_type LIKE 'varchar(%'
OR column_type LIKE '%text');


Take that query and send its output to a text file. Import text into mysql:

SQL="SELECT CONCAT('UPDATE ',table_schema,'.',table_name,"
SQL="${SQL}' SET ',column_name,'=REPLACE(',column_name,',''fuschia'',''fuchsia'');') " SQL="${SQL} FROM information_schema.columns WHERE "
SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema') " SQL="${SQL} AND (column_type LIKE 'char%' "
SQL="${SQL} OR column_type LIKE 'varchar('" SQL="${SQL} OR column_type LIKE '%text');"
mysql -u... -p... -ANe"\${SQL}" > GlobalReplace_fuschia_to_fuchsia.sql
less GlobalReplace_fuschia_to_fuchsia.sql


If the file looks good, feel free to execute it.

Give it a Try !!!

-