MySQL字符集一个隐藏参数

Contents

最近在研究字符集相关的问题，无意中看到了MySQL字符集一个隐藏的参数

--character-set-client-handshake
Do not ignore character set information sent by the client. To ignore client information and use the default server character set, use --skip-character-set-client-handshake; this makes MySQL behave like MySQL 4.0.

为了兼容 mysql 4.0 的习惯增加的这个参数，character-set-client-handshake默认为1，读取default_character_set里设置的值。如果忽略客户端字符集设置，mysqld启动时加上

--skip-character-set-client-handshake

验证：
my.cnf参数设置
default_character_set = latin1
character_set_server = utf8mb4

启动不读取default_character_set的值设置

character-set-client-handshake=0
or
skip-character-set-client-handshake=1

重启mysql让配置生效，查看结果

show variables like 'character%';
+--------------------------+-----------------------------------+
| Variable_name            | Value                             |
+--------------------------+-----------------------------------+
| character_set_client     | utf8mb4                           |
| character_set_connection | utf8mb4                           |
| character_set_database   | gbk                               |
| character_set_filesystem | binary                            |
| character_set_results    | utf8mb4                           |
| character_set_server     | utf8mb4                           |
| character_set_system     | utf8                              |
| character_sets_dir       | /home/mysql/mysql/share/charsets/ |
+--------------------------+-----------------------------------+
8 rows in set (0.01 sec)

可以看到character_set_client，character_set_connection，character_set_results读取的是character_set_server的值utf8mb4

如果启动读取default_character_set的值设置

character-set-client-handshake=1
or
skip-character-set-client-handshake=0

重启mysql让配置生效，查看结果

show variables like 'character%';
+--------------------------+-----------------------------------+
| Variable_name            | Value                             |
+--------------------------+-----------------------------------+
| character_set_client     | latin1                            |
| character_set_connection | latin1                            |
| character_set_database   | gbk                               |
| character_set_filesystem | binary                            |
| character_set_results    | latin1                            |
| character_set_server     | utf8mb4                           |
| character_set_system     | utf8                              |
| character_sets_dir       | /home/mysql/mysql/share/charsets/ |
+--------------------------+-----------------------------------+
8 rows in set (0.13 sec)

可以看到character_set_client，character_set_connection，character_set_results读取的是default_character_set的值latin1

这时需要手工设置
set names xxx;
才会将client、connection、results的字符集改过来。

set names utf8;

Query OK, 0 rows affected (0.02 sec)

show variables like 'character%';
+--------------------------+-----------------------------------+
| Variable_name            | Value                             |
+--------------------------+-----------------------------------+
| character_set_client     | utf8                              |
| character_set_connection | utf8                              |
| character_set_database   | gbk                               |
| character_set_filesystem | binary                            |
| character_set_results    | utf8                              |
| character_set_server     | utf8mb4                           |
| character_set_system     | utf8                              |
| character_sets_dir       | /home/mysql/mysql/share/charsets/ |
+--------------------------+-----------------------------------+
8 rows in set (0.00 sec)

其实这个参数也是有好处的。比如启用 skip-character-set-client-handshake 选项后，就可以避免客户端程序误操作，使用其他字符集连接进来并写入数据，从而引发乱码问题。