Home / server / Questions / 65043
Accepted
The Disintegrator
Asked: 2009-09-14 21:39:21 +0800 CST

Alter charset and collation in all columns in all tables in MySQL

  • 772

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!

mysql

6 Answers

  1. Best Answer

    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.

    • 20

  2. 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. :)

    • 8

  3. 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` ;
    • 2

  4. You can use the information_schema database to find the column and table you have to alter. You can find them with:

    SELECT table_name, column_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.

    • 0

  5. I ended up doing the following:

    • dumped the database to a sql file, using mysqldump db_name > db_name.sql
    • replace the charset in the sql file using awk sed -i -e 's/old_charset/new_charset/g' db_name.sql
    • import this new database file mysql < db_name

    I wouldn't use this in production, but I needed it in a local dev environment.

    Alternativelly, I could have dumped the SQL structure and data separately, and then went into the SQL file to change the charsets there.

    Keeping in mind that if you're converting between incompatible charsets, this might mess up your data.

    • 0

  6. Alter all columns in all tables where character set = utf8mb3

    select concat('ALTER TABLE ',TABLE_Name, ' MODIFY ',COLUMN_NAME, ' ' , COLUMN_TYPE, ' CHARACTER SET utf8mb4;') command from information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DB NAME HERE' and CHARACTER_SET_NAME = 'utf8mb3';

    Then Run result.

    • 0