Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
add comment

4 Answers

up vote 10 down vote accepted

First of all, don't just take my word for it! Test my suggestion out with this:

select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema' limit 10; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema' limit 10;

If you feel good with the outcome of that, remove the limit clauses and save the output to an SQL script or, get fancy and pipe the output directly to mysql similar to what I demonstrate here. That would look like this:

mysql -B -N --host=prod-db1 --user=admin --password=secret -e "select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema'; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema';" | mysql --host=prod-db1 --user=admin --password=secret

When you start thinking about using valid SQL to generate valid SQL, it changes the whole game. You will be amazed by how many uses you find for it.

share|improve this answer
    
Almost there! But can't get the syntax right –  The Disintegrator Sep 16 '09 at 14:26
    
ok I've got it. I'm adding the correct sintax to the answer. Your idea rules –  The Disintegrator Sep 16 '09 at 15:17
add comment

Actually, you can use CONVERT TO on a table to have it convert all columns within that table to the charset and collation.

SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'databasename';

Also, it makes more sense to me to select the actual database you want to do this on. So this:

... WHERE TABLE_SCHEMA = 'databasename';

rather than this:

... WHERE TABLE_SCHEMA != 'information_schema';

But I guess if you really wanted to do it on all tables, you could use the former. Seems a bit heavy handed to me though. :)

share|improve this answer
add comment

To change collation on all columns i used

SELECT CONCAT(  'ALTER TABLE ',  `TABLE_NAME` ,  ' CHANGE `',  `COLUMN_NAME` ,  '` `',`COLUMN_NAME` ,  '` ',  `DATA_TYPE` ,  '(',  `CHARACTER_MAXIMUM_LENGTH` ,  ') CHARACTER SET utf8 COLLATE utf8_swedish_ci ;' ) FROM  `COLUMNS` WHERE  `TABLE_SCHEMA` =  <schema> AND  `COLLATION_NAME` !=  'utf8_swedish_ci' ORDER BY  `TABLE_NAME` ,  `ORDINAL_POSITION` ;
share|improve this answer
add comment

You can use the information_schema database to find the column and table you have to alter.

Example:

SELECT table_name, columun_name FROM information_schema.`COLUMNS`
WHERE table_schema='your database' AND collation_name LIKE 'latin%';

Then you can automate the alter with a SQL script, store procedure or with your preferred development language.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.