How to change the character set (and/or collation) in a MySQL database for all tables and columns
Wed, 2008.04.09 - 10:18 — müzso
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:
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
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.
ALTER TABLE `database_name`.`table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ciHowever 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 1Replace "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.
All of my own contents here (if not noted otherwise) are licensed under the
Creative Commons Attribution-NonCommercial-ShareAlike 4.0 License. My posts reflect my personal opinion and shall not be associated with any of my employers.
Creative Commons Attribution-NonCommercial-ShareAlike 4.0 License. My posts reflect my personal opinion and shall not be associated with any of my employers.
Comments
Changing collate of all tables in the database
Sorry, for my database it
Re: Sorry, for my database it
Thank You, Thank You, Thank You, Thank You, 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
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
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
useful stuff like this is hard to find, though.
Didn't wok for me
Re: didn't work for me
I don't understand what
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?
First of all: in this query
Eg.
SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as stmtFROM `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 TABLEstatement to change the characterset for all those tables.First query worked
Thank you!
lifesaver