You could try to query the table information_schema.columns and find every table in your MySQL DB Instance that have character fields. Here is the table's layout:
mysql> show create table information_schema.columns\G
*************************** 1. row ***************************
Table: COLUMNS
Create Table: CREATE TEMPORARY TABLE `COLUMNS` (
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
`ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
`COLUMN_DEFAULT` longtext,
`IS_NULLABLE` varchar(3) NOT NULL DEFAULT '',
`DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
`CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
`NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
`CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
`COLLATION_NAME` varchar(32) DEFAULT NULL,
`COLUMN_TYPE` longtext NOT NULL,
`COLUMN_KEY` varchar(3) NOT NULL DEFAULT '',
`EXTRA` varchar(27) NOT NULL DEFAULT '',
`PRIVILEGES` varchar(80) NOT NULL DEFAULT '',
`COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
Here is a query to get every character-based column type for all user-defined tables:
mysql> select DISTINCT COLUMN_TYPE from information_schema.columns
-> WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
-> AND (COLUMN_TYPE REGEXP 'text$'
-> OR COLUMN_TYPE REGEXP '^varchar'
-> OR COLUMN_TYPE REGEXP '^char');
+--------------+
| COLUMN_TYPE |
+--------------+
| mediumtext |
| varchar(512) |
| varchar(20) |
| longtext |
| text |
| varchar(200) |
| varchar(255) |
| varchar(100) |
| tinytext |
| varchar(25) |
| varchar(64) |
| varchar(32) |
| varchar(60) |
| varchar(50) |
| varchar(250) |
| varchar(150) |
| varchar(10) |
| varchar(45) |
| char(5) |
+--------------+
19 rows in set (0.09 sec)
You could join that query to a second query. The second query would have the name of every table with those column types:
SELECT BB.table_schema,BB.table_name,BB.COLUMN_NAME FROM
(select DISTINCT COLUMN_TYPE from information_schema.columns
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND (COLUMN_TYPE REGEXP 'text$'
OR COLUMN_TYPE REGEXP '^varchar'
OR COLUMN_TYPE REGEXP '^char')) AA
INNER JOIN
(SELECT table_schema,table_name,COLUMN_NAME,COLUMN_TYPE
FROM information_schema.columns
WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')) BB
USING (COLUMN_TYPE);
Now, message the output to make it write a generic script for you:
SELECT CONCAT('UPDATE ',db,'.',tb,' SET ',fld,
'=REPLACE(',fld,',''oldchar'',''newchar'');')
UpdateCommand
FROM (SELECT db,tb,fld FROM
(
SELECT table_schema db,table_name tb,COLUMN_NAME fld FROM
(select DISTINCT COLUMN_TYPE from information_schema.columns
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND (COLUMN_TYPE REGEXP 'text$'
OR COLUMN_TYPE REGEXP '^varchar'
OR COLUMN_TYPE REGEXP '^char')) AAA
INNER JOIN
(SELECT table_schema,table_name,COLUMN_NAME,COLUMN_TYPE
FROM information_schema.columns
WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')) BBB
USING (COLUMN_TYPE)
) AA) A;
Output should look something like this:
+-----------------------------------------------------------------------------------------------+
| UpdateCommand |
+-----------------------------------------------------------------------------------------------+
| UPDATE example.user SET user_name=REPLACE(user_name,'oldchar','newchar'); |
| UPDATE garbage.rolando SET post_content=REPLACE(post_content,'oldchar','newchar'); |
| UPDATE garbage.rolando SET post_title=REPLACE(post_title,'oldchar','newchar'); |
| UPDATE garbage.rolando SET post_excerpt=REPLACE(post_excerpt,'oldchar','newchar'); |
| UPDATE garbage.rolando SET post_status=REPLACE(post_status,'oldchar','newchar'); |
| UPDATE garbage.rolando SET comment_status=REPLACE(comment_status,'oldchar','newchar'); |
| UPDATE garbage.rolando SET ping_status=REPLACE(ping_status,'oldchar','newchar'); |
| UPDATE garbage.rolando SET post_password=REPLACE(post_password,'oldchar','newchar'); |
| UPDATE garbage.rolando SET post_name=REPLACE(post_name,'oldchar','newchar'); |
Last step for generic script generation is to output the query to a text file like this:
mysql -u... -p... -A --skip-column-names -e"SELECT CONCAT('UPDATE ',db,'.',tb,' SET ',fld, '=REPLACE(',fld,',''oldchar'',''newchar'');') UpdateCommand FROM (SELECT db,tb,fld FROM ( SELECT table_schema db,table_name tb,COLUMN_NAME fld FROM (select DISTINCT COLUMN_TYPE from information_schema.columns WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND (COLUMN_TYPE REGEXP 'text$' OR COLUMN_TYPE REGEXP '^varchar' OR COLUMN_TYPE REGEXP '^char')) AAA INNER JOIN (SELECT table_schema,table_name,COLUMN_NAME,COLUMN_TYPE FROM information_schema.columns WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) BBB USING (COLUMN_TYPE) ) AA) A;" > CharUpdateScript.sql
You can customize the generic script from there.
You can have the information_schema build the entire query for you and execute it as Dynamic SQL (Prepared Statements as @a1ex07 first mentioned):
SELECT CONCAT('SELECT * FROM (SELECT bar_id,bar_weight FROM ',
GROUP_CONCAT(tb SEPARATOR
' WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM '),
' WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5')
INTO @foo_query FROM
(
select CONCAT(table_schema,'.',table_name) tb
from information_schema.tables
where table_name like 'foo_reference%'
) A;
SELECT @foo_query\G
PREPARE stmt FROM @foo_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
First, let's make some sample tables with sample data:
DROP DATABASE IF EXISTS timpost1;
DROP DATABASE IF EXISTS timpost2;
CREATE DATABASE timpost1;
CREATE DATABASE timpost2;
use timpost1
CREATE TABLE cookie_cutter
(
foo_id int,
bar_id int,
bar_weight int,
primary key (foo_id)
) ENGINE=MyISAM;
CREATE TABLE timpost1.foo_reference1 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost1.foo_reference2 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost1.foo_reference3 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference1 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference2 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference3 LIKE timpost1.cookie_cutter;
INSERT INTO timpost1.foo_reference1 VALUES (1,17,10),(2,3,20);
INSERT INTO timpost1.foo_reference2 VALUES (1,27,90),(2,3,20);
INSERT INTO timpost1.foo_reference3 VALUES (1,37,40),(2,3,20);
INSERT INTO timpost2.foo_reference1 VALUES (1,47,70),(2,3,20);
INSERT INTO timpost2.foo_reference2 VALUES (1,57,20),(2,3,20);
INSERT INTO timpost2.foo_reference3 VALUES (1,67,50),(2,3,20);
Here are the sample tables being made and loaded:
mysql> DROP DATABASE IF EXISTS timpost1;
Query OK, 4 rows affected (0.03 sec)
mysql> DROP DATABASE IF EXISTS timpost2;
Query OK, 3 rows affected (0.02 sec)
mysql> CREATE DATABASE timpost1;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE DATABASE timpost2;
Query OK, 1 row affected (0.00 sec)
mysql> use timpost1
Database changed
mysql> CREATE TABLE cookie_cutter
-> (
-> foo_id int,
-> bar_id int,
-> bar_weight int,
-> primary key (foo_id)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE timpost1.foo_reference1 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE timpost1.foo_reference2 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE timpost1.foo_reference3 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE timpost2.foo_reference1 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE timpost2.foo_reference2 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE timpost2.foo_reference3 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO timpost1.foo_reference1 VALUES (1,17,10),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost1.foo_reference2 VALUES (1,27,90),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost1.foo_reference3 VALUES (1,37,40),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost2.foo_reference1 VALUES (1,47,70),(2,3,20);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost2.foo_reference2 VALUES (1,57,20),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost2.foo_reference3 VALUES (1,67,50),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
Now let's build the query and execute it:
mysql> SELECT CONCAT('SELECT * FROM (SELECT bar_id,bar_weight FROM ',
-> GROUP_CONCAT(tb SEPARATOR
-> ' WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM '),
-> ' WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5')
-> INTO @foo_query FROM
-> (
-> select CONCAT(table_schema,'.',table_name) tb
-> from information_schema.tables
-> where table_name like 'foo_reference%'
-> ) A;
Query OK, 1 row affected (0.02 sec)
mysql> SELECT @foo_query\G
*************************** 1. row ***************************
@foo_query: SELECT * FROM (SELECT bar_id,bar_weight FROM timpost1.foo_reference1 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost1.foo_reference2 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost1.foo_reference3 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference1 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference2 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference3 WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5
1 row in set (0.01 sec)
mysql> PREPARE stmt FROM @foo_query;
Query OK, 0 rows affected (0.01 sec)
Statement prepared
mysql> EXECUTE stmt;
+--------+------------+
| bar_id | bar_weight |
+--------+------------+
| 27 | 90 |
| 47 | 70 |
| 67 | 50 |
| 37 | 40 |
| 57 | 20 |
+--------+------------+
5 rows in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
mysql>
CAVEAT
Notice that the query will cross check all databases in the mysql instance that starts with foo_reference. In the above example, both timpost1 and timpost2 were checked.
Also, notice that the subquery will get 6 rows, and the limit 0,5
properly displays the first 5 rows of the subquery.
Give it a Try !!!
Best Answer
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:
information_schema
performance_schema
mysql
COLUMN_TYPE
values with one of the following characteristics:CHAR(
VARCHAR(
TEXT
(TEXT
,MEDIUMTEXT
,LONGTEXT
)Here is the query to get those columns
Using the above query, construct a set of queries that outputs SQL for converting
fuschia
tofuchsia
:Take that query and send its output to a text file. Import text into mysql:
If the file looks good, feel free to execute it.
Give it a Try !!!