Pre-General Availability Draft: 2017-12-16
A character string literal, hexadecimal literal, or bit-value
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] literal [COLLATE collation_name]
Character set introducers and the
clause are implemented according to standard SQL
SELECT 'abc'; SELECT _latin1'abc'; SELECT _binary'abc'; SELECT _utf8'abc' COLLATE utf8_danish_ci; SELECT _latin1 X'4D7953514C'; SELECT _utf8 0x4D7953514C COLLATE utf8_danish_ci; SELECT _latin1 b'1000001'; SELECT _utf8 0b1000001 COLLATE utf8_danish_ci;
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
CONVERT() would do. It
does not change the string value, although padding may occur.
The introducer is just a signal.
For character string literals, space between the introducer and the string is permitted but optional.
Character string literals can be designated as binary strings
by using the
Hexadecimal literals and bit-value literals are binary strings
by default, so
_binary is permitted, but
_binary may be useful
to preserve a hexadecimal or bit literal as a binary string in
contexts for which the literal is otherwise treated as a
number. For example, bit operations permit numeric or binary
string arguments in MySQL 8.0 and higher, but
treat hexadecimal and bit literals as numbers by default. To
explicitly specify binary string context for such literals,
_binary introducer for at least one
of the arguments:
mysql> SET @v1 = X'000D' | X'0BC0'; mysql> SET @v2 = _binary X'000D' | X'0BC0'; mysql> SELECT HEX(@v1), HEX(@v2); +----------+----------+ | HEX(@v1) | HEX(@v2) | +----------+----------+ | BCD | 0BCD | +----------+----------+
The displayed result appears similar for both bit operations,
but the result without
_binary is a
BIGINT value, whereas the result with
_binary is a binary string. Due to the
difference in result types, the displayed values differ:
High-order 0 digits are not displayed for the numeric result.
MySQL determines the character set and collation of a character string literal, hexadecimal literal, or bit-value 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.
_charset_nameis not specified but
For a character string literal, 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.
For a hexadecimal literal or bit-value literal, the only permitted collation is
binarybecause these types of literals are binary strings by default.
Nonbinary strings with
latin1character set and
SELECT _latin1'Müller' COLLATE latin1_german1_ci; SELECT _latin1 X'0A0D' COLLATE latin1_german1_ci; SELECT _latin1 b'0110' COLLATE latin1_german1_ci;
Nonbinary strings with
utf8character set and its default collation (that is,
SELECT _utf8'Müller'; SELECT _utf8 X'0A0D'; SELECT _utf8 b'0110';
Binary strings with
binarycharacter set and its default collation (that is,
SELECT _binary'Müller'; SELECT X'0A0D'; SELECT b'0110';
The hexadecimal literal and bit-value literal need no introducer because they are binary strings by default.
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;
This construction (
COLLATEonly) does not work for hexadecimal literals or bit literals because their character set is
binaryno matter the connection character set, and
binaryis not compatible with the
utf8_general_cicollation. The only permitted
COLLATEclause in the absence of an introducer is
A string with the connection default character set and collation:
For character set literals, 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
additional discussion and examples, see
Section 10.1.3.6, “Character String Literal Character Set and Collation”.