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

Added escaping around column name for the case of column name is registered MySQL keyword like `KEY` or `USER`. Fixed a typo 'varchar(' -> 'varchar(%' in sh script. Fixed a typo 'char%' -> 'char(%'

Source Link

edit approved Sep 10, 2015 at 11:09

Dionio

3
3

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'\`=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%''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 !!!

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

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

Post Undeleted by RolandoMySQLDBA

occurred Aug 28, 2014 at 19:29

Post Deleted by RolandoMySQLDBA

occurred Oct 13, 2013 at 3:05

added 33 characters in body

Source Link

edited Oct 18, 2012 at 19:20

RolandoMySQLDBA

184.4k
33
323
531

You canneed to use the information_schemainformation_schema database to generate the script.

First collectCollect all columns from every table of every database that have thisthe following criteria:

Exclude the following databases:
information_schemainformation_schema
performance_schemaperformance_schema
mysqlmysql
COLUMN_TYPECOLUMN_TYPE values with one of the following characteristics:

starts with CHAR(

starts with VARCHAR(
ends with TEXT (TEXT, MEDIUMTEXT, LONGTEXT)

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

You can use the information_schema to generate the script

First collect all columns from every table of every database that have this 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 !!!

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

added 165 characters in body; added 2 characters in body

Source Link

edited Jul 19, 2012 at 18:00

RolandoMySQLDBA

184.4k
33
323
531

You can use the information_schema to generate the script

First collect all columns from every table of every database that have this criteria

Exclude the following databases
information_schema
performance_schema
mysql
COLUMN_TYPE values with one of the following characteristics

starts with VARCHARCHAR(

starts with VARCHAR(
ends with TEXT 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 'varchar%''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 'varchar%''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 'varchar%''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 !!!

You can use the information_schema to generate the script

First collect all columns from every table of every database that have this criteria

Exclude the following databases
information_schema
performance_schema
mysql
COLUMN_TYPE values with one of the following characteristics

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 '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 '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 'varchar%' 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 !!!

You can use the information_schema to generate the script

First collect all columns from every table of every database that have this 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 !!!

Source Link

created Jul 19, 2012 at 17:51

RolandoMySQLDBA

184.4k
33
323
531

Loading

Stack Exchange Network

Return to Answer