How to change the character set (and/or collation) in a MySQL database for all tables and columns

There's no one-line command to do this in MySQL, but there's a command for changing a table's default character set (and/or collation) and convert all it's columns to the new character set (and/or collation). It's like this:
ALTER TABLE `database_name`.`table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

However if you've a hundred tables or so, doing this one by one is a tedious task. Since MySQL 5.x you can query a list of tables (and columns, etc.) from the database named information_schema. With the following query you can create the SQL statements to convert the character set (and/or collation) for all tables easily:
SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as stmt
FROM `information_schema`.`TABLES` t
WHERE 1
AND t.`TABLE_SCHEMA` = 'database_name'
ORDER BY 1

Replace "database_name" with the name of your database and edit the statement template in the parameters of the CONCAT function to have the target character set (and/or collation) of your choice. After that you can just take the statements and batch execute them in the MySQL client of your choice (eg. phpmyadmin, Navicat, the official MySQL client, etc.). Of course, you're strongly advised to make a backup of your database before you start messing around with character set (and/or collation) conversions. Smile

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Changing collate of all tables in the database

I read your article about how to change collate of all tables in the database. Thanks for your help. It is very quick and simple and helpful. Keep it up Smile

Sorry, for my database it

Sorry, for my database it doesn't function. I tried in phpmyadmin. No error msg but nothing has change.

Re: Sorry, for my database it

There can be just a million things wrong in your particular case. You might want to search on the given statement and see whether there're any known bugs for your MySQL version that might have caused the problem, or you might find tips on what to check/verify.

Thank You, Thank You, Thank You, Thank You, Thank You!!!!

Sorry for my English , but i want to thank you.
You are the best, man !
Two weeks i trying to change collation of all my tables at once.
There is other scripts in the web but too complex beginners.
This is the easiest way to do it.
Thank you again!

Thanks, man, you saved me

Thanks, man, you saved me some hour of work with your idea!
Anyway, I suggest to add a "AND t.`TABLE_TYPE` = 'BASE TABLE'" to the query,
so only real tables (and not views, for ex.) will be processed.

Cheers.

You are rock

Hi,

Thanks! Thanks! Thanks!
I am frustrated with this issue for a whole day. And even my hosting company's support can do nothing on this.
You provide an extraordinary information for code idiot like me.


Thanks again for your work.

Ringo

worked like a charm and saved

worked like a charm and saved my day. thank you.
useful stuff like this is hard to find, though. Cool

Didn't wok for me

Just tried. Query passed bit it didn't change current tables

Re: didn't work for me

The second query is not even supposed to change anything. It creates the queries for all your tables that you should execute to change the tables' charactersets.

I don't understand what

I don't understand what you're saying but it sounds like this is what I'm doing wrong?

So if I'm doing:

SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as stmt
FROM `information_schema`.`TABLES` t
WHERE 1
AND t.`TABLE_SCHEMA` = 'database_name'
ORDER BY 1

that's not enough? then where do I add the other statement?

Syndicate content