MySQL5 中文编码设定

MySQL 4.1+ 开始支持多语言，从 4.0 升级，最简单的设定：

 [client]
default-character-set=gbk

[mysqld]
default-character-set=gbk
default-collation=gbk_chinese_ci

[mysqldump]
default-character-set=gbk

[mysql]
default-character-set=gbk 

重启，检查编码：

#/etc/init.d/mysql restart
$mysql -uroot
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | gbk                        |
| character_set_connection | gbk                        |
| character_set_database   | gbk                        |
| character_set_filesystem | binary                     |
| character_set_results    | gbk                        |
| character_set_server     | gbk                        |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

mysql>

貌似一切 OK 了？ Wait, 用 PHP 试一把，类似结果：

 |character_set_client | latin1 |
|character_set_connection | latin1 |
|character_set_database | gbk |
|character_set_filesystem | binary |
|character_set_results | latin1 |
|character_set_server | gbk |
|character_set_system | utf8 |
|character_sets_dir | /usr/share/mysql/charsets/ | 

character_set_client 、|character_set_connection、character_set_results 被重置了！ 如果你留意 MySQL 的 ChnageLog 你会发现，MySQL 5.0.13 添加了 skip-character-set-client-handshake 配置选项(Changes in release 5.0.13 )， ChangeLog 如是说： A new command line argument was added to mysqld to ignore client character set information sent during handshake, and use server side settings instead, to reproduce 4.0 behavior。 SO，加上看会有什么效果：

 [mysqld]
default-character-set=gbk
default-collation=gbk_chinese_ci
skip-character-set-client-handshake

#/etc/init.d/mysql restart

$php5 test_charset.php
|character_set_client | gbk |
|character_set_connection | gbk |
|character_set_database | gbk |
|character_set_filesystem | binary |
|character_set_results | gbk |
|character_set_server | gbk |
|character_set_system | utf8 |
|character_sets_dir | /usr/share/mysql/charsets/ |

此时 MySQL 的编码模式完全和 MySQL 4 兼容了。So far so good，直到，你需要多编码并存时，你会发现，skip-character-set-client-handshake 不允许客户端重新设置编码。 yep, right, blame MySQL。 最终的选择是，remove skip-character-set-client-handshake 设置项，fix your legacy code.

#在 select 数据库之前
SET NAMES @@YOUR_CHARACTER_SET

UPDATE 2007-01-10: MySQL > 5.0.27 已经修正了客户端充值字符集问题，不再需要skip-character-set-client-handshake 选项。