Cry MySQL How to...
Change the charset and collation order for a MySQL database
The default charset for MySQL is "Latin1", which is mostly sufficient for most Western European languages (and the default collation sequence is "latin1_swedish_ci").
I encountered an issue where a live MySQL database was using the default charset and collation, but problems were being encountered because it should have been created using the "utf8" charset and "utf8_general_ci" collation. For this reason these notes show how to set or change the charset to "utf8" and the collation to "utf8_general_ci" - if this is now what you want then you should be able to simply substitute instead your desired charset and collation order.
These notes cover how to change the charset and collation order for a single MySQL database. Unfortunately whilst changing the charset and collation order for a single table is straight forward, its a little more difficult to change it globally for an entire database but a script is provided for this.
These notes cover:
To see the default charset that MySQL will use for newly created tables use:
show variables like "character_set_database";
To see the default collation sequence that MySQL will use for newly created tables use:
show variables like "collation_database";
To explicity set the default charset and collation order when first creating a database use:
create database db_name character set utf8 collate utf8_general_ci;
For an existing database the default charset and collation order is changed using:
alter database db_name character set utf8 collate utf8_general_ci;
Be aware that changing the default charset and collation order only affects tables that are subsequently created. It does not affect any of the existing tables.
To see what charset and collation a given table is using, use:
select character_set_name, collation_name
being sure to substitute the name of the table you are interested in for "table_name" in the above.
If you are interested in a table which is in a database other
than the current database then change:
Whilst its not used very often, be aware that each field can have its own charset and collation order. To view the charset and collation order of a table field use:
select character_set_name, collation_name from
Be sure to substitute the name of the table and field in
the above. The above assumes the current database, so replace the
To change the character set and collation order for a single table use:
alter table db_name.table_name convert to character set utf8 collate utf8_general_ci
Needless to say, whilst this is fine for a table or two its not very practical converting a table at a time if you have a large number of tables!
The following SQL creates a procedure to reset the charset and collation status for all tables that need it. The last line of the provided SQL calls the procedure.
-- Copyright (c) 2011 www.cryer.co.uk
Change the last line to specify a different charset or collation order.
Be aware that this may take a while to run, depending on the number (and size) of tables to be updated.
This SQL does not change the default charset and collation order for the database, so you might want to do that as a separate step.
These notes have been tested against MySQL version 5.1.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.