A "connection" refers to a session established when a client application connects to the server to initiate interaction. The client sends SQL statements (such as queries) through the session connection. The server sends responses (such as result sets or error messages) back to the client via the connection.
System variables for character sets and collations
The following system variables related to character sets and collations are involved in client-server interactions:
The
character_set_serverandcollation_serversystem variables specify the character set and collation of the server.The
character_set_databaseandcollation_databasesystem variables specify the default character set and collation for a database.
In addition, the server also uses the following system variables:
The
character_set_clientsystem variable specifies the character set used by the client to send statements.The
character_set_connectionsystem variable specifies the character set to be used for converting statements received from clients. The server converts statements sent by clients fromcharacter_set_clienttocharacter_set_connection.The
collation_connectionsystem variable is used to set the character set and collation for a connection.collation_connectionis important for string comparison.The
character_set_resultssystem variable specifies the character set used by 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 the result set or error messages, setcharacter_set_resultstoNULLor binary. The syntax is as follows:SET character_set_results = NULL; SET character_set_results = binary;
The following statement can be used to view system variables for all character sets and collations:
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 for configuring the client character set
After a connection is established, the client can change the system variables for the character set and collation of the current session. You can use the following SET statements to modify these variables:
The
SET NAMES 'charset_name'statement.Specifies the character set to be used by the client in subsequent requests. This 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 theCOLLATEclause:SET NAMES 'charset_name' COLLATE 'collation_name'The
SET CHARACTER SET 'charset_name'statement.Similar to
SET NAMES, but it setscharacter_set_connectionandcollation_connectiontocharacter_set_databaseandcollation_database, respectively, which are the character set and collation of the default database. This is equivalent to the following three statements:SET character_set_client = charset_name; SET character_set_results = charset_name; SET collation_connection = @@collation_database;