Character sets and collations for client connection

A connection refers to the session established when a client connects to a server for interactions. The client sends SQL statements, such as queries, by using the connection. The server sends responses, such as result sets and error messages, back to the client by using the connection.

System variables of character sets and collations

The following character set and collation system variables are related to the client-server interaction:

• The character_set_server and collation_server variables specify the character set and collation of the server.

• The character_set_database and collation_database variables specify the default character set and collation for the database.

The server also uses the following system variables:

• character_set_client specifies the character set in which the statements are sent by the client.

• character_set_connection specifies the character set to which received statements are converted. The server converts the character set of the statement sent by the client from character_set_client to character_set_connection.

• collation_connection specifies the character set and collation used by the connection. collation_connection is crucial for string comparison.

• character_set_results specifies the character set for the server to return query results to the client, including data results (such as column values), metadata (such as column names), and error messages. If you do not need to convert result sets or error messages, set character_set_results to NULL or binary by using the following syntax:

  SET character_set_results = NULL;
  SET character_set_results = binary;
  

To view all character set and collation system variables, execute the following statements:

obclient> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | latin1  |
| character_set_connection | latin1  |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | latin1  |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8mb4 |
+--------------------------+---------+
7 rows in set

obclient> SHOW SESSION VARIABLES LIKE 'collation\_%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | latin1_swedish_ci  |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set

Configure SQL statements for client character sets

After it establishes the connection, the client can change the character set and collation system variables of the current session. Execute the following SET statements:

• SET NAMES 'charset_name'

  This statement specifies the character set that the client uses in subsequent requests. It is equivalent to the following three statements:

  SET character_set_client = charset_name;
  SET character_set_results = charset_name;
  SET character_set_connection = charset_name;
  

  To specify a collation for collation_connection, add a COLLATE clause:

  SET NAMES 'charset_name' COLLATE 'collation_name'
  

• SET CHARACTER SET 'charset_name'

  This statement is similar to SET NAMES, but it sets character_set_connection and collation_connection to the default values of the character_set_database and collation_database. It is equivalent to the following three statements:

  SET character_set_client = charset_name;
  SET character_set_results = charset_name;
  SET collation_connection = @@collation_database;