mysql ucs2,MySQL：客户端字符集问题

简单记录

说修改了

character_set_server = utf8mb4

后如下使用mysql客户端连接：

mysql> show variables like '%char%';

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

| Variable_name | Value |

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

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /opt/my_mysql/share/charsets/ |

| ft_query_extra_word_chars | OFF |

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

问题原因：

函数

bool thd_init_client_charset(THD *thd, uint cs_number)

{

CHARSET_INFO *cs;

/*

Use server character set and collation if

- opt_character_set_client_handshake is not set

- client has not specified a character set

- client character set is the same as the servers

- client character set doesn't exists in server

*/

if (!opt_character_set_client_handshake ||// 如果设置了skip-character-set-client-handshake则直接跳过客户端的字符集设置，否则使用服务端的设置，这些设置在服务端均继承来之character_set_server

!(cs= get_charset(cs_number, MYF(0))) || // 0

!my_strcasecmp(&my_charset_latin1,

global_system_variables.character_set_client->name,

cs->name)) //0

{

if (!is_supported_parser_charset(

global_system_variables.character_set_client))

{

/* Disallow non-supported parser character sets: UCS2, UTF16, UTF32 */

my_error(ER_WRONG_VALUE_FOR_VAR, MYF(0), "character_set_client",

global_system_variables.character_set_client->csname);

return true;

}

thd->variables.character_set_client=

global_system_variables.character_set_client;

thd->variables.collation_connection=

global_system_variables.collation_connection;

thd->variables.character_set_results=

global_system_variables.character_set_results;

}

else

{

if (!is_supported_parser_charset(cs))

{

/* Disallow non-supported parser character sets: UCS2, UTF16, UTF32 */

my_error(ER_WRONG_VALUE_FOR_VAR, MYF(0), "character_set_client",

cs->csname);

return true;

}

thd->variables.character_set_results=

thd->variables.collation_connection=

thd->variables.character_set_client= cs;

}

return false;

}

因此如果设置了skip-character-set-client-handshake选项，则可以在忽略掉客户端字符集的字符集设置，直接使用服务端的字符集。涉及3个参数

character_set_results

collation_connection

character_set_client

也可在不设置skip-character-set-client-handshake的情况下载客户端指定字符集参数，比如mysql客户端有这样的选项。

[root@mgr3 ~]# /opt/my_mysql/bin/mysql --default-character-set=utf8mb4

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 5.7.22-22-debug-log Source distribution

Copyright (c) 2009-2018 Percona LLC and/or its affiliates

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%char%';

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

| Variable_name | Value |

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

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /opt/my_mysql/share/charsets/ |

| ft_query_extra_word_chars | OFF |

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

9 rows in set (0.06 sec)

mysql>

可以debug thd_init_client_charset函数，观察其中的cs_number.