Character Sets and collations

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

Most likely you are need to does this if you haven ' t specified character set and collation in 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 is already present.

[Mysqld]
Character-set-server=utf8 
collation-server = utf8_unicode_ci
init-connect= ' Set NAMES UTF8 '
init_ connect= ' SET collation_connection = Utf8_unicode_ci ' 
skip-character-set-client-handshake  #此处是忽略客户端的字符集, Using the server's settings

Next, restart MySQL and log into the 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'll 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 ' CCSA
WHERE ccsa.collation_name = t.table_collation
and 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 |< c12/>| 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 are 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. TABLES
WHERE table_schema = ' db_wordpress ' and
table_collation = ' Latin1_swedish_ci ' |
tail-n+2 > Collation.sql

After your run above query, check collation.sql content to verify if all rows are correct. If Collation.sql is empty, you probably does 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 "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;

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