Character Sets and Collations

Using this example, you can change character set and collation for a MySQL database table(s).

Most likely you will be need to do this if you haven’t specified character set and collation at the time of database/table creation and default character set/collation applied are not desirable.

Setting MySQL default character set and collation in my.cnf

Below are settings for MySQL version 5.5.9 and onwards.

Put them in /etc/mysql/my.cnf is correct sections. Please be careful as some settings might be already present.

[mysqld]character-set-server=utf8 collation-server = utf8_unicode_ciinit-connect='SET NAMES utf8'init_connect='SET collation_connection = utf8_unicode_ci' skip-character-set-client-handshake  #此处是忽略客户端的字符集，使用服务器的设置

Next, restart mysql and log into mysql shell:

mysql> show variables like "%character%";show variables like "%collation%";

Sample output as:

+--------------------------+----------------------------+| Variable_name            | Value                      |+--------------------------+----------------------------+| character_set_client     | utf8                       || character_set_connection | utf8                       || character_set_database   | utf8                       || character_set_filesystem | binary                     || character_set_results    | utf8                       || character_set_server     | utf8                       || character_set_system     | utf8                       || character_sets_dir       | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)+----------------------+-----------------+| Variable_name        | Value           |+----------------------+-----------------+| collation_connection | utf8_general_ci || collation_database   | utf8_general_ci || collation_server     | utf8_general_ci |+----------------------+-----------------+3 rows in set (0.00 sec)

Checking current character set and collation for database/table/columns

For Database:

SELECT default_character_set_name, default_collation_name FROM information_schema.SCHEMATA  WHERE schema_name = "databasename";

It will show output as:

+----------------------------+------------------------+| default_character_set_name | default_collation_name |+----------------------------+------------------------+| latin1                     | latin1_swedish_ci      |+----------------------------+------------------------+

For Tables:

SELECT T.table_name, T.table_collation, CCSA.character_set_name FROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSAWHERE CCSA.collation_name = T.table_collationAND T.table_schema = "databasename";

Sample output as below:

+-----------------------------------------------------+-------------------+--------------------+| table_name                                          | table_collation   | character_set_name |+-----------------------------------------------------+-------------------+--------------------+| rtc_wp_rtAccountToken                               | latin1_swedish_ci | latin1             || rtc_wp_rtAccountVerify                              | latin1_swedish_ci | latin1             || rtc_wp_rt_crm_mail_messageids                       | latin1_swedish_ci | latin1             || rtc_wp_w3tc_cdn_queue                               | latin1_swedish_ci | latin1             || gp_meta                                             | utf8_general_ci   | utf8               |+-----------------------------------------------------+-------------------+--------------------+

For Columns:

SELECT table_name, column_name, character_set_name, collation_name FROM information_schema.`COLUMNS` C WHERE character_set_name != 'NULL' AND table_schema = "db_name"

Sample Output:

+------------------------+--------------+--------------------+-------------------+| table_name             | column_name  | character_set_name | collation_name    |+------------------------+--------------+--------------------+-------------------+| rtc_wp_rtAccountToken  | accesstoken  | latin1             | latin1_swedish_ci || rtc_wp_rtAccountToken  | refreshtoken | latin1             | latin1_swedish_ci || rtc_wp_rtAccountVerify | email        | latin1             | latin1_swedish_ci || rtc_wp_rtAccountVerify | type         | latin1             | latin1_swedish_ci || rtc_wp_rtAccountVerify | code         | latin1             | latin1_swedish_ci |+------------------------+--------------+--------------------+-------------------+

Converting character set and collations

MAKE BACKUP

We are serious. Just use mysqldump rather than regretting it later

Changing Database Character Sets and Collations

This is simplest:

ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci;

Replace your database name with db_name. Also after running query verify if database-level defaults are changed indeed.

Changing Tables Character Sets and Collations

Below is a syntax to covert character set ofwp_posts and wp_postmetatables.

alter table wp_posts convert to character set utf8 collate utf8_unicode_ci;alter table wp_postmeta convert to character set utf8 collate utf8_unicode_ci;

If you want to covert all your MySQL tables, then run a command like below on database db_wordpress

mysql -e "SELECT concat('alter table ', TABLE_NAME , ' convert to character set utf8 collate utf8_unicode_ci;')FROM information_schema.TABLESWHERE table_schema = 'db_wordpress'AND TABLE_COLLATION = 'latin1_swedish_ci'" |tail -n+2 > collation.sql

After you run above query, check collation.sql content to verify if all rows are correct. If collation.sql is empty, you probably do not have a table using MyISAM engine.

If all looks good, run following to convert all mysql tables to InnoDB.

mysql db_wordpress < collation.sql

Changing Column Character Sets and Collations

Below is syntax to convert columns to utf8

alter table table_name change col_name col_name col_data_type character set utf8;

Please note that we have to use same col_name twice!
col_data_type can be found form a sql query like…

mysql> SELECT table_name, column_name, data_type, character_set_name, collation_name FROM information_schema.`COLUMNS` WHERE  table_schema = "db_name" AND table_name = "table_name" AND column_name = "col_name";

Sample output:

+--------------+--------------+-----------+| table_name   | column_name  | data_type |+--------------+--------------+-----------+| wp_posts     | post_content | longtext  |+--------------+--------------+-----------+

Example for wordpress’s wp_posts table

alter table wp_posts change post_content post_content LONGTEXT CHARACTER SET utf8;

Please be very careful for column conversion. Specially if you have non-english characters stored in database. In that case, you can refer to this WordPress Codex section.