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.

First of all: in this query

First of all: in this query you've to replace 'database_name' with name of the database for which you want to change the characterset.
Eg.
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` = 'my_sample_database'
ORDER BY 1

Second: execute this query (using some sort of MySQL client, like the official MySQL client or phpMyAdmin or Navicat, etc.). It'll output a list of SQL statements (one for each table in the selected database). You've to run each ALTER TABLE statement to change the characterset for all those tables.

First query worked

Just to add that first query worked. I altered table by table

Thank you!

Excellent stuff, thank you.

lifesaver

9 years on and this knowledge is still helpful !

Syndicate content