REPLACE does not play with wildcards that way. I think you meant:
UPDATE [table]
SET [column] = REPLACE([column],'TLD.com','TLD.org')
WHERE [column] LIKE '%TLD.com%';
You have no WHERE clause, so it tried to update 618 rows, but it did not find any instances of %TLD.com% in that column. To see which rows should be affected, run a SELECT instead:
SELECT [column], REPLACE([column], 'TLD.com', 'TLD.org') AS new_value
FROM [table]
WHERE [column] LIKE '%TLD.com%';
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_schemaperformance_schemamysql
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 !!!
Best Answer
Assuming you have access to the sed command line application, doing something like:
will convert all strings with "olddomain.com" into "newdomain.com" from a dump we get from the database olddb and will import it into a newdb database, which was just created.
You may need to add extra parameters or a defaults client .my.cnf file for the connection options.
Be careful, this is an easy process but a bit dangerous, as it will change the string wherever it is found, be it in on table or column names and comments, and without checking if it is part of a longer url or not. This is why I have make it load it on a separate database so you can check that it is working as intended.
I use this every time to convert between table engines from mysql dumps:
Once you have seen that it is correct, just point your production database to the new place or rename your tables (the safest way to do that is again using mysqldump).
There are other options, that could involve scripting by querying
information_schema.columnsand usingUPDATE table SET column = REPLACE(column, 'olddomain.com', 'newdomain.com');. Those could give you more control, but would require a bit of extra scripting.