Alter charset and collation in all columns in all tables in MySQL
- by The Disintegrator
I need to execute these statements in all tables for all columns.
alter table table_name charset=utf8;
alter table table_name alter column column_name charset=utf8;
Is it possible to automate this in any way inside MySQL?
I would prefer to avoid mysqldump
Update:
Richard Bronosky showed me the way :-)
The query I needed to execute in every table:
alter table DBname.DBfield CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
Crazy query to generate all other queries:
SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DBname';
I only wanted to execute it in one database.
It was taking too long to execute all in one pass. It turned out that it was generating one query per field per table. And only one query per table was necessary (distinct to the rescue). Getting the output on a file was how I realized it.
How to generate the output to a file:
mysql -B -N --user=user --password=secret -e "SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DBname';" > alter.sql
And finally to execute all the queries:
mysql --user=user --password=secret < alter.sql
Thanks Richard. You're the man!
