Character set and collation for client connections

A "connection" refers to the session established when a client program connects to the server, initiating interaction with the server. Clients send SQL statements (such as queries) through the 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 client-server interactions:

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

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

Additionally, the server 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 client-sent statements 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 no conversion of result sets or error messages is needed, set character_set_results to NULL or binary, as shown in the following example:

  SET character_set_results = NULL;
  SET character_set_results = binary;

The following statement displays all character set and collation system variables:

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 (0.009 sec)

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 (0.002 sec)

SQL statements for configuring client character sets

After a connection is established, a 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 sets the character set for 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;