We talk about working with character set and collation of MySQL today. In MySQL, if we want to store Chinese, Japanese or other languages other than English, we may need to set the relative character set for the database, tables and columns. Also, when we connect to MySQL. we may need to set the character set for the connection. Now I summarize some commands used to see what are the character set and collation of our database and how to change them as needed. On command prompt window, we need to log in to the mysql client with the mysql -u [username] -p command first.

Now we may want to check some variables about character set and collation for our database client and server, for example, connection character set. We can type following commands:

SHOW VARIABLES LIKE 'char%';

SHOW VARIABLES LIKE 'collation%';

The command will give us some information like

+--------------------------+---------------------------------------------------------+

| Variable_name | Value |

+--------------------------+---------------------------------------------------------+

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | latin1 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.1\share\charsets\ |

+--------------------------+---------------------------------------------------------+

We can easily understand that the character set we are using for the database engine. also we can change these variables by using

SET variable_name=value /* SET character_set_connection=utf8; */

Next come to the database character set and collation, we run

SHOW CREATE DATABASE database_name

We can find our default character set in the comment of the output. If we want to change the character set and collation of the database, we run

ALTER DATABASE database_name CHARACTER SET charset_name COLLATE collation_name

We can also set the character set and collation when we create the new database

CREATE DATABASE database_name CHARACTER SET charset_name COLLATE collation_name

For database tables, the commands are similar, we run

SHOW CREATE TABLE table_name

At the end of the output, we may find the DEFAULT CHARSET or COLLATE, if we want to change them, we run

ALTER TABLE table_name CONVERT TO CHARACTER SET charset_name COLLATE collation_name

we can also set the character set and collation when we create a table, we run

CREATE TABLE table_name (column_list) CHARACTER SET charset_name COLLATE collation_name

For columns, we need to run

SHOW FULL COLUMNS IN table_name

the third column is the collation. We can change them with

ALTER TABLE table_name MODIFY col_name data_type CHARACTER SET charset_name COLLATE collation_name