Thursday, September 10, 2015

Example about MySQL character set support

MySQL Character set supports 4 levels: Server, Database, Table and Column.

server1>  create table tc(c1 varchar(20) character set latin1, c2 varchar(20) character set utf8);
Query OK, 0 rows affected (0.03 sec)

server1> insert into tc values('abc','abc');
Query OK, 1 row affected (0.01 sec)

server1> insert into tc values ('中文','中文');
Query OK, 1 row affected, 1 warning (0.01 sec)

server1> select * from tc;
+------+--------+
| c1   | c2     |
+------+--------+
| abc  | abc    |
| ??   | 中文   |
+------+--------+
2 rows in set (0.00 sec)

server1> select hex(c1),hex(c2) from tc;
+---------+--------------+
| hex(c1) | hex(c2)      |
+---------+--------------+
| 616263  | 616263       |
| 3F3F    | E4B8ADE69687 |
+---------+--------------+
2 rows in set (0.00 sec)


server1> show variables like '%character%';
+--------------------------+--------------------------------------------------------------------+
| Variable_name            | Value                                                              |
+--------------------------+--------------------------------------------------------------------+
| character_set_client     | utf8                                                               |
| character_set_connection | utf8                                                               |
| character_set_database   | latin1                                                             |
| character_set_filesystem | binary                                                             |
| character_set_results    | utf8                                                               |
| character_set_server     | latin1                                                             |
| character_set_system     | utf8                                                               |
| character_sets_dir       | /mysql/mysql-advanced-5.6.26-linux-glibc2.5-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------------------+
8 rows in set (0.00 sec)