Selecting a character set for MySQL and MariaDB clients

Дата публикации: 29-03-2026 00:26:00

 MySQL and MariaDB have many character-set related options, perhaps too many:character_set_clientcharacter_set_connectioncharacter_set_databasecharacter_set_filesystemcharacter_set_resultscharacter_set_servercharacter_set_systemThis is a topic that I don\'t know much about and I am still far from an expert. My focus has been other DBMS topics. But I spent time recently on this topic while explaining what looked like a performance regression, but really was just a new release of MySQL using a charset that is less CPU-efficient than the previous charset that was used.DebuggingThe intial sequence to understand what was going on was:mysql -e \'SHOW GLOBAL VARIABLES like \"character_set_%\"mysql -e \'SHOW SESSION VARIABLES like \"character_set_%\"run \"SHOW SESSION VARIABLES\" from my benchmark clientNote:the output from steps 1 and 2 was differentwith SHOW GLOBAL VARIABLES I got character_set_client =latin1 but with SHOW SESSION VARIABLES I got character_set_client =utf8mb3. This happens. One reason is that some MySQL client binaries autodetect the charset based on the value of LANG or LC_TYPE from your Linux env. Another reason is that if autodetection isn\'t done then the clients can use the default charset that was set at compile time. That charset is then passed to the server during connection handshake (see thd_init_client_charset). So it is likely that character_set_client as displayed by SHOW GLOBAL VARIABLES isn\'t what your client will use.the output from steps 2 and 3 was differentautodetection is only done when mysql_options() is called with a certain flag (see below). And that is not done by the MySQL driver in sysbench, nor is it done by Python\'s MySQLdb. So my benchmark clients are likely selecting the default charset and don\'t do autodetection. And that default is determined by the version of the MySQL client library, meaning that default can change over the years. For the source that implements this, search for MYSQL_AUTODETECT_CHARSET_NAME and read sql-common/client.c.The following enables autodetection and should be called before calling mysql_real_connect():    mysql_options(...,                   MYSQL_SET_CHARSET_NAME,                  MYSQL_AUTODETECT_CHARSET_NAME);Note that adding the following into my.cnf isn\'t a workaround for clients that don\'t do autodetect.    [client]    default-character-set=...NotesThese are from my usage of MySQL 5.7.44, 8.0.45 and 8.4.8 along with MariaDB 10.6.25, 10.11.16 and 11.4.10. All were compiled from source as was sysbench. I installed MySQLdb and the MySQL client library via apt for Ubuntu 24.04.The values for character_set_client, character_set_results and character_set_connection were measured via the MySQL command-line client running SHOW GLOBAL VARIABLES and SHOW SESSION VARIABLES and then the benchmark clients running SHOW SESSION VARIABLES.The reason for sharing this is to explain the many possible values your session might use for character_set_client, character_set_results and character_set_connection. And using the wrong value might waste CPU.What per-session values are used for character_set_client|results|connection?* my.cnf has character_set_server=latin1* per SHOW GLOBAL VARIABLES each is set to =latin1* values below measured via SHOW SESSION VARIABLES
Values for character_set_client|results|connection... with \"mysql\" command line client... this is easy to change with --default-character-set command line option or equivalent option in my.cnfdbms5.7.44 utf88.0.45 utf8mb48.4.8 utf8mb4
10.6.25 utf8mb310.11.16 utf8mb311.4.10 utf8mb3
Values for character_set_client|results|connection... with sysbench
client library versiondbms 5.7 8.0 8.4 10.6 10.11 11.45.7.44 latin1 latin1 latin1 NA NA NA8.0.45 latin1 utf8mb4 utf8mb4 NA NA NA8.4.8 latin1 utf8mb4 utf8mb4 NA NA NA
10.6.25 latin1 latin1 latin1 utf8mb4 utf8mb4 utf8mb410.11.16 latin1 latin1 latin1 utf8mb4 utf8mb4 utf8mb411.4.10 latin1 utf8mb4 utf8mb4 utf8mb4 utf8mb4 utf8mb4
Values for character_set_client|results|connection... with insert benchmark (Python MySQLdb and /lib/x86_64-linux-gnu/libmysqlclient.so.21... I am not what version is libmysqlclient.so.21, this is on Ubuntu 24.04
dbms5.7.44 latin18.0.45 utf8mb48.4.8 utf8mb4
10.6.25 latin110.11.16 latin111.4.10 utf8mb4
Selecting a character set for MySQL and MariaDB clients appeared first on MariaDB.org

Основное содержимое страницы с новостью.

Схожие новости

#	Наименование новости	Тональность	Информативность	Дата публикации
1	Sysbench vs MariaDB on a small server: using the same charset for all versions	0	8.32	06-04-2026
2	Sysbench vs MySQL on a small server: another way to view the regressions	0	9.01	09-04-2026
3	Sysbench vs MySQL on a small server: no new regressions, many old ones	0	8.8	24-03-2026
4	MySQL 9.7.0 vs sysbench on a small server	0	8.98	10-04-2026
5	CPU-bound sysbench on a large server: Postgres, MySQL and MariaDB	0	5.53	04-04-2026
6	The Insert Benchmark vs MariaDB 10.2 to 13.0 on a 24-core server	0	12.6	08-04-2026
7	MariaDB Encryption ( data at rest )	0	5.71	25-02-2024
8	The Insert Benchmark vs MariaDB 10.2 to 13.0 on a 32-core server	0	6.43	08-04-2026
9	MySQL Protocol: Collations	0	8	06-05-2024
10	gcc vs clang for sysbench on a small server with Postgres, MySQL and MariaDB	0	7.24	31-03-2026

Классификация: . Схожих патентов: 0. Схожих новостей: 10. Тональность: 0. Информативность: 7.47. Источник: mariadb.org.