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 !!!
Let's say you want to update id
27
Run this first
SELECT REPLACE(content,'http://www.abc.com/data/img','http://www.def.com/data/img') newurl
FROM Content_T1 WHERE id = 27;
If the result is correct, you can run
UPDATE Content_T1
SET content = REPLACE(content,'http://www.abc.com/data/img','http://www.def.com/data/img')
WHERE id = 27;
SELECT content FROM Content_T1 WHERE id = 27;
If the result is correct and you are ready to fix all of them, run this query without the WHERE
clause:
UPDATE Content_T1
SET content = REPLACE(content,'http://www.abc.com/data/img','http://www.def.com/data/img');
Please, go to a test server and run this. When the results are correct ...
GIVE IT A TRY !!!
Best Answer
I have a rather ugly approach that will strip alphanumeric characters from a user variable
STRIPPING ALPHAS
STRIPPING ALPHAS EXECUTED
STRIPPING ALPHAS RESULT
STRIPPING NUMERICS
STRIPPING NUMERICS EXECUTED
STRIPPING NUMERICS RESULT
STRIPPING ALPHANUMERICS
STRIPPING ALPHANUMERICS EXECUTED
STRIPPING ALPHANUMERICS RESULT
EPILOGUE
This SELECT query is meant to hold up to 100 characters for stripping
You can replace
(SELECT 'abcdefghijklmnopqrstuvwxyz0123456789' chars) L,
with any list of characters you wish to strip from a user variable.GIVE IT A TRY !!!