简单记录
说修改了
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
作用参考：
https://www.jianshu.com/p/f1bfdb02007d
也可在不设置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.
根本原因：
客户端5.7由于 mysql_autodetect_character_set函数中MYSQL_DEFAULT_CHARSET_NAME为latin1，但是8.0中已经变成MYSQL_DEFAULT_CHARSET_NAME UTF8MB4.
但是这个问题主要由于5.7中utf8就是utf8自动检测函数mysql_autodetect_character_set 检测linux locale中的信息，得到的utf8，则使用utf8。而8.0mysql_autodetect_character_set 检测到utf8会做映射为utf8mb4，如果设置export LANG="en_US" 则自动检测获取的是 ISO-8859-1 也就是latin1 单字节字符集。当然默认是 export LANG="en_US.utf8"。函数调用在mysql_autodetect_character_set中
(gdb) p csp->my_name
$3 = 0x4e4d8f "latin1"
(gdb) p csp->os_name
$4 = 0x4e4e84 "ISO-8859-1"
(gdb) p csname
$5 = 0x7fffb2f69b3c "ISO-8859-1"
#0 mysql_autodetect_character_set (mysql=0x9ea0a0 <mysql>) at /home/mysql/soft/percona-server-5.7.29-32/sql-common/client.c:3174
#1 0x0000000000464fbf in mysql_init_character_set (mysql=0x9ea0a0 <mysql>) at /home/mysql/soft/percona-server-5.7.29-32/sql-common/client.c:3220
#2 0x000000000046db2a in mysql_set_character_set (mysql=0x9ea0a0 <mysql>, cs_name=0x48efbb "auto") at /home/mysql/soft/percona-server-5.7.29-32/sql-common/client.c:6123
#3 0x0000000000410239 in init_connection_options (mysql=0x9ea0a0 <mysql>) at /home/mysql/soft/percona-server-5.7.29-32/client/mysql.cc:5153
#4 0x000000000040ff7a in sql_real_connect (host=0x0, database=0x0, user=0x0, password=0x0, silent=0) at /home/mysql/soft/percona-server-5.7.29-32/client/mysql.cc:5017
#5 0x000000000041036a in sql_connect (host=0x0, database=0x0, user=0x0, password=0x0, silent=0) at /home/mysql/soft/percona-server-5.7.29-32/client/mysql.cc:5183
#6 0x0000000000407c57 in main (argc=3, argv=0x9f23d8) at /home/mysql/soft/percona-server-5.7.29-32/client/mysql.cc:1332
(gdb)
网友评论