Character set and collation for client connections

A "connection" refers to the session established when a client program connects to the server, which initiates interaction with the server. The client sends SQL statements (such as queries) through this session. The server sends responses (such as result sets or error messages) back to the client through the connection.

System variables for character set and collation

The following system variables are related to character set and collation for interactions between clients and the server:

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

• character_set_database and collation_database specify the default character set and collation of the database.

In addition, the server also uses the following system variables:

• character_set_client specifies the character set for statements sent by the client.

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

• collat​​ion_connection specifies the character set and collation used for the connection. collat​​ion_connection is important for string comparisons.

• character_set_results specifies the character set for query results returned by the server 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. The syntax is as follows:

  SET character_set_results = NULL;
  SET character_set_results = binary;
  

The following statement can be used to view all system variables related to character set and collation:

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

SQL statements to configure the client character set

After a connection is established, the client can change the character set and collation system variables for the current session. You can use the following SET statements to change these variables:

• SET NAMES 'charset_name' statement.

  This statement sets the character set to be used 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 the collation for collation_connection, add the COLLATE clause:

  SET NAMES 'charset_name' COLLATE 'collation_name'
  

• SET CHARACTER SET 'charset_name' statement.

  Similar to SET NAMES, but it sets character_set_connection and collation_connection to character_set_database and collation_database, which are the character set and collation of the default 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;