MySQL's connection character set and proofing

Some character set and collation system variables are related to client and server interactions. Some of the things that have been mentioned in the previous chapters are:

• Server character sets and proofing rules can be used as values for Character_set_server and collation_server variables.

• The character set and collation rules for the default database can be used as values for character_set_database and collation_database variables.

Character set and proofing rule variables are also involved in connection processing of the client and server. Each client has a connection-related character set and proofing rule variable.

Consider what a "connection" is: It is what happens when you connect to a server. The client sends SQL statements, such as queries, sent to the server via a connection. The server sends a response to the client via a connection, such as a result set. For client connections, this causes problems with connection character sets and collation rules that can be resolved through system variables:

• What character set is used in a query when the query leaves the client?

The server uses the Character_set_client variable as the character set used in queries sent by the client.

• What character set should the server convert to when it receives a query?

When converting, the server uses character_set_connection and collation_connection system variables. It converts queries sent by clients from Character_set_client system variables to character_set_connection (unless the string literal has an cited preposition like _latin1 or _utf8). Collation_connection is important for comparing literal strings. For string comparisons of column values, it is not important because the columns have a higher collation precedence.

• What character set should the server convert to the client before sending a result set or returning an error message?

The character_set_results variable instructs the server to return query results to the character set used by the client. Include result data, such as column values and result metadata (such as column names).

You can adjust the settings for these variables, or you can rely on default values (so you can skip this chapter).

There are two statements that affect the connection character set:

SET NAMES 'charset_name'
SET CHARACTER SET charset_name