10.3.6 Character String Literal Character Set and Collation
Every character string literal has a character set and a collation.
A character string literal may have an optional character set introducer and
COLLATE clause, to designate it as a string that uses a particular character set and collation:
[_charset_name]'string' [COLLATE collation_name]
_ expression is formally called an introducer. It tells the parser, “the string that follows uses character set
charset_name.” An introducer does not change the string to the introducer character set like
CONVERT() would do. It does not change the string value, although padding may occur. The introducer is just a signal. See Section 10.3.8, “Character Set Introducers”.
SELECT 'abc'; SELECT _latin1'abc'; SELECT _binary'abc'; SELECT _utf8'abc' COLLATE utf8_danish_ci;
Character set introducers and the
COLLATE clause are implemented according to standard SQL specifications.
MySQL determines the character set and collation of a character string literal in the following manner:
COLLATEare specified, character set
collation_namemust be a permitted collation for
_charset_nameis specified but
COLLATEis not specified, character set
charset_nameand its default collation are used. To see the default collation for each character set, use the
SHOW CHARACTER SETstatement or query the
_charset_nameis not specified but
COLLATEis specified, the connection default character set given by the
character_set_connectionsystem variable and collation
collation_namemust be a permitted collation for the connection default character set.
COLLATEis specified), the connection default character set and collation given by the
collation_connectionsystem variables are used.
A nonbinary string with
latin1character set and
SELECT _latin1'Müller' COLLATE latin1_german1_ci;
A nonbinary string with
utf8character set and its default collation (that is,
A binary string with
binarycharacter set and its default collation (that is,
A nonbinary string with the connection default character set and
utf8_general_cicollation (fails if the connection character set is not
SELECT 'Müller' COLLATE utf8_general_ci;
A string with the connection default character set and collation:
An introducer indicates the character set for the following string, but does not change how the parser performs escape processing within the string. Escapes are always interpreted by the parser according to the character set given by
The following examples show that escape processing occurs using
character_set_connection even in the presence of an introducer. The examples use
SET NAMES (which changes
character_set_connection, as discussed in Section 10.4, “Connection Character Sets and Collations”), and display the resulting strings using the
HEX() function so that the exact string contents can be seen.
mysql> SET NAMES latin1; mysql> SELECT HEX('à\n'), HEX(_sjis'à\n'); +------------+-----------------+ | HEX('à\n') | HEX(_sjis'à\n') | +------------+-----------------+ | E00A | E00A | +------------+-----------------+
à (hexadecimal value
E0) is followed by
\n, the escape sequence for newline. The escape sequence is interpreted using the
character_set_connection value of
latin1 to produce a literal newline (hexadecimal value
0A). This happens even for the second string. That is, the
_sjis introducer does not affect the parser's escape processing.
mysql> SET NAMES sjis; mysql> SELECT HEX('à\n'), HEX(_latin1'à\n'); +------------+-------------------+ | HEX('à\n') | HEX(_latin1'à\n') | +------------+-------------------+ | E05C6E | E05C6E | +------------+-------------------+
sjis, a character set in which the sequence of
à followed by
\ (hexadecimal values
5C) is a valid multibyte character. Hence, the first two bytes of the string are interpreted as a single
sjis character, and the
\ is not interpreted as an escape character. The following
n (hexadecimal value
6E) is not interpreted as part of an escape sequence. This is true even for the second string; the
_latin1 introducer does not affect escape processing.