Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
1  
You can get an idea here –  dezso Jul 19 '12 at 12:30
1  
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

1 Answer 1

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 !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.