A “connection” is what a client program makes when it connects to the server, to begin a session within which it interacts with the server. The client sends SQL statements, such as queries, over the session connection. The server sends responses, such as result sets or error messages, over the connection back to the client.
Several character set and collation system variables relate to a client's interaction with the server. Some of these have been mentioned in earlier sections:
The
character_set_server
andcollation_server
system variables indicate the server character set and collation. See Section 12.3.2, “Server Character Set and Collation”.The
character_set_database
andcollation_database
system variables indicate the character set and collation of the default database. See Section 12.3.3, “Database Character Set and Collation”.
Additional character set and collation system variables are involved in handling traffic for the connection between a client and the server. Every client has session-specific connection-related character set and collation system variables. These session system variable values are initialized at connect time, but can be changed within the session.
Several questions about character set and collation handling for client connections can be answered in terms of system variables:
What character set are statements in when they leave the client?
The server takes the
character_set_client
system variable to be the character set in which statements are sent by the client.NoteSome character sets cannot be used as the client character set. See Impermissible Client Character Sets.
What character set should the server translate statements to after receiving them?
To determine this, the server uses the
character_set_connection
andcollation_connection
system variables:The server converts statements sent by the client from
character_set_client
tocharacter_set_connection
. Exception: For string literals that have an introducer such as_utf8mb4
or_latin2
, the introducer determines the character set. See Section 12.3.8, “Character Set Introducers”.collation_connection
is important for comparisons of literal strings. For comparisons of strings with column values,collation_connection
does not matter because columns have their own collation, which has a higher collation precedence (see Section 12.8.4, “Collation Coercibility in Expressions”).
What character set should the server translate query results to before shipping them back to the client?
The
character_set_results
system variable indicates the character set in which the server returns query results to the client. This includes result data such as column values, result metadata such as column names, and error messages.To tell the server to perform no conversion of result sets or error messages, set
character_set_results
toNULL
orbinary
:SET character_set_results = NULL; SET character_set_results = binary;
For more information about character sets and error messages, see Section 12.6, “Error Message Character Set”.
To see the values of the character set and collation system variables that apply to the current session, use this statement:
SELECT * FROM performance_schema.session_variables
WHERE VARIABLE_NAME IN (
'character_set_client', 'character_set_connection',
'character_set_results', 'collation_connection'
) ORDER BY VARIABLE_NAME;
The following simpler statements also display the connection variables, but include other related variables as well. They can be useful to see all character set and collation system variables:
SHOW SESSION VARIABLES LIKE 'character\_set\_%';
SHOW SESSION VARIABLES LIKE 'collation\_%';
Clients can fine-tune the settings for these variables, or depend on the defaults (in which case, you can skip the rest of this section). If you do not use the defaults, you must change the character settings for each connection to the server.
The character_set_client
system
variable cannot be set to certain character sets:
ucs2
utf16
utf16le
utf32
Attempting to use any of those character sets as the client character set produces an error:
mysql> SET character_set_client = 'ucs2';
ERROR 1231 (42000): Variable 'character_set_client'
can't be set to the value of 'ucs2'
The same error occurs if any of those character sets are used in
the following contexts, all of which result in an attempt to set
character_set_client
to the
named character set:
The
--default-character-set=
command option used by MySQL client programs such as mysql and mysqladmin.charset_name
The
SET NAMES '
statement.charset_name
'The
SET CHARACTER SET '
statement.charset_name
'
When a client connects to the server, it indicates which
character set it wants to use for communication with the server.
(Actually, the client indicates the default collation for that
character set, from which the server can determine the character
set.) The server uses this information to set the
character_set_client
,
character_set_results
,
character_set_connection
system
variables to the character set, and
collation_connection
to the
character set default collation. In effect, the server performs
the equivalent of a SET NAMES
operation.
If the server does not support the requested character set or collation, it falls back to using the server character set and collation to configure the connection. For additional detail about this fallback behavior, see Connection Character Set Error Handling.
The mysql, mysqladmin, mysqlcheck, mysqlimport, and mysqlshow client programs determine the default character set to use as follows:
In the absence of other information, each client uses the compiled-in default character set, usually
utf8mb4
.Each client can autodetect which character set to use based on the operating system setting, such as the value of the
LANG
orLC_ALL
locale environment variable on Unix systems or the code page setting on Windows systems. For systems on which the locale is available from the OS, the client uses it to set the default character set rather than using the compiled-in default. For example, settingLANG
toru_RU.KOI8-R
causes thekoi8r
character set to be used. Thus, users can configure the locale in their environment for use by MySQL clients.The OS character set is mapped to the closest MySQL character set if there is no exact match. If the client does not support the matching character set, it uses the compiled-in default. For example,
utf8
andutf-8
map toutf8mb4
, anducs2
is not supported as a connection character set, so it maps to the compiled-in default.C applications can use character set autodetection based on the OS setting by invoking
mysql_options()
as follows before connecting to the server:mysql_options(mysql, MYSQL_SET_CHARSET_NAME, MYSQL_AUTODETECT_CHARSET_NAME);
Each client supports a
--default-character-set
option, which enables users to specify the character set explicitly to override whatever default the client otherwise determines.NoteSome character sets cannot be used as the client character set. Attempting to use them with
--default-character-set
produces an error. See Impermissible Client Character Sets.
With the mysql client, to use a character set
different from the default, you could explicitly execute a
SET NAMES
statement every time
you connect to the server (see
Client Program Connection Character Set Configuration). To
accomplish the same result more easily, specify the character
set in your option file. For example, the following option file
setting changes the three connection-related character set
system variables set to koi8r
each time you
invoke mysql:
[mysql]
default-character-set=koi8r
If you are using the mysql client with
auto-reconnect enabled (which is not recommended), it is
preferable to use the charset
command rather
than SET NAMES
. For example:
mysql> charset koi8r
Charset changed
The charset
command issues a
SET NAMES
statement, and also
changes the default character set that mysql
uses when it reconnects after the connection has dropped.
When configuration client programs, you must also consider the environment within which they execute. See Section 12.5, “Configuring Application Character Set and Collation”.
After a connection has been established, clients can change the
character set and collation system variables for the current
session. These variables can be changed individually using
SET
statements, but two more convenient statements affect the
connection-related character set system variables as a group:
SET NAMES '
charset_name
' [COLLATE 'collation_name
']SET NAMES
indicates what character set the client uses to send SQL statements to the server. Thus,SET NAMES 'cp1251'
tells the server, “future incoming messages from this client are in character setcp1251
.” It also specifies the character set that the server should use for sending results back to the client. (For example, it indicates what character set to use for column values if you use aSELECT
statement that produces a result set.)A
SET NAMES '
statement is equivalent to these three statements:charset_name
'SET character_set_client = charset_name; SET character_set_results = charset_name; SET character_set_connection = charset_name;
Setting
character_set_connection
tocharset_name
also implicitly setscollation_connection
to the default collation forcharset_name
. It is unnecessary to set that collation explicitly. To specify a particular collation to use forcollation_connection
, add aCOLLATE
clause:SET NAMES 'charset_name' COLLATE 'collation_name'
SET CHARACTER SET '
'charset_name
SET CHARACTER SET
is similar toSET NAMES
but setscharacter_set_connection
andcollation_connection
tocharacter_set_database
andcollation_database
(which, as mentioned previously, indicate the character set and collation of the default database).A
SET CHARACTER SET
statement is equivalent to these three statements:charset_name
SET character_set_client = charset_name; SET character_set_results = charset_name; SET collation_connection = @@collation_database;
Setting
collation_connection
also implicitly setscharacter_set_connection
to the character set associated with the collation (equivalent to executingSET character_set_connection = @@character_set_database
). It is unnecessary to setcharacter_set_connection
explicitly.
Some character sets cannot be used as the client character
set. Attempting to use them with SET
NAMES
or SET CHARACTER
SET
produces an error. See
Impermissible Client Character Sets.
Example: Suppose that column1
is defined as
CHAR(5) CHARACTER SET latin2
. If you do not
say SET NAMES
or
SET CHARACTER SET
, then for
SELECT column1 FROM t
, the server sends back
all the values for column1
using the
character set that the client specified when it connected. On
the other hand, if you say SET NAMES 'latin1'
or SET CHARACTER SET 'latin1'
before issuing
the SELECT
statement, the server
converts the latin2
values to
latin1
just before sending results back.
Conversion may be lossy for characters that are not in both
character sets.
Attempts to use an inappropriate connection character set or collation can produce an error, or cause the server to fall back to its default character set and collation for a given connection. This section describes problems that can occur when configuring the connection character set. These problems can occur when establishing a connection or when changing the character set within an established connection.
Connect-Time Error Handling
Some character sets cannot be used as the client character set; see Impermissible Client Character Sets. If you specify a character set that is valid but not permitted as a client character set, the server returns an error:
$> mysql --default-character-set=ucs2
ERROR 1231 (42000): Variable 'character_set_client' can't be set to
the value of 'ucs2'
If you specify a character set that the client does not recognize, it produces an error:
$> mysql --default-character-set=bogus
mysql: Character set 'bogus' is not a compiled character set and is
not specified in the '/usr/local/mysql/share/charsets/Index.xml' file
ERROR 2019 (HY000): Can't initialize character set bogus
(path: /usr/local/mysql/share/charsets/)
If you specify a character set that the client recognizes but
the server does not, the server falls back to its default
character set and collation. Suppose that the server is
configured to use latin1
and
latin1_swedish_ci
as its defaults, and that
it does not recognize gb18030
as a valid
character set. A client that specifies
--default-character-set=gb18030
is
able to connect to the server, but the resulting character set
is not what the client wants:
mysql> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
...
| character_set_results | latin1 |
...
+--------------------------+--------+
mysql> SHOW SESSION VARIABLES LIKE 'collation_connection';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
+----------------------+-------------------+
You can see that the connection system variables have been set
to reflect a character set and collation of
latin1
and
latin1_swedish_ci
. This occurs because the
server cannot satisfy the client character set request and falls
back to its defaults.
In this case, the client cannot use the character set that it wants because the server does not support it. The client must either be willing to use a different character set, or connect to a different server that supports the desired character set.
The same problem occurs when the client tells the server to use a character set that the server recognizes, but the default collation for that character set on the client side is not known on the server side.
Runtime Error Handling
Within an established connection, the client can request a
change of connection character set and collation with
SET NAMES
or
SET CHARACTER SET
.
Some character sets cannot be used as the client character set; see Impermissible Client Character Sets. If you specify a character set that is valid but not permitted as a client character set, the server returns an error:
mysql> SET NAMES 'ucs2';
ERROR 1231 (42000): Variable 'character_set_client' can't be set to
the value of 'ucs2'
If the server does not recognize the character set (or the collation), it produces an error:
mysql> SET NAMES 'bogus';
ERROR 1115 (42000): Unknown character set: 'bogus'
mysql> SET NAMES 'utf8mb4' COLLATE 'bogus';
ERROR 1273 (HY000): Unknown collation: 'bogus'
A client that wants to verify whether its requested character set was honored by the server can execute the following statement after connecting and checking that the result is the expected character set:
SELECT @@character_set_client;