Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.1Mb
PDF (A4) - 35.2Mb
Man Pages (TGZ) - 256.4Kb
Man Pages (Zip) - 361.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

10.10.8 The Binary Character Set

Thebinary character set is the character set for binary strings, which are sequences of bytes. Thebinary character set has one collation, also namedbinary. Comparison and sorting are based on numeric byte values, rather than on numeric character code values (which for multibyte characters differ from numeric byte values). For information about the differences between thebinary collation of thebinary character set and the_bin collations of nonbinary character sets, seeSection 10.8.5, “The binary Collation Compared to _bin Collations”.

For thebinary character set, the concepts of lettercase and accent equivalence do not apply:

  • For single-byte characters stored as binary strings, character and byte boundaries are the same, so lettercase and accent differences are significant in comparisons. That is, thebinary collation is case-sensitive and accent-sensitive.

    mysql> SET NAMES 'binary';mysql> SELECT CHARSET('abc'), COLLATION('abc');+----------------+------------------+| CHARSET('abc') | COLLATION('abc') |+----------------+------------------+| binary         | binary           |+----------------+------------------+mysql> SELECT 'abc' = 'ABC', 'a' = 'ä';+---------------+------------+| 'abc' = 'ABC' | 'a' = 'ä'  |+---------------+------------+|             0 |          0 |+---------------+------------+

  • For multibyte characters stored as binary strings, character and byte boundaries differ. Character boundaries are lost, so comparisons that depend on them are not meaningful.

To perform lettercase conversion of a binary string, first convert it to a nonbinary string using a character set appropriate for the data stored in the string:

mysql> SET @str = BINARY 'New York';mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING utf8mb4));+-------------+------------------------------------+| LOWER(@str) | LOWER(CONVERT(@str USING utf8mb4)) |+-------------+------------------------------------+| New York    | new york                           |+-------------+------------------------------------+

To convert a string expression to a binary string, these constructs are equivalent:

BINARYexprCAST(expr AS BINARY)CONVERT(expr USING BINARY)

If a value is a character string literal, the_binary introducer may be used to designate it as a binary string. For example:

_binary 'a'

The_binary introducer is permitted for hexadecimal literals and bit-value literals as well, but unnecessary; such literals are binary strings by default.

For more information about introducers, seeSection 10.3.8, “Character Set Introducers”.

Note

Within themysql client, binary strings display using hexadecimal notation, depending on the value of the--binary-as-hex. For more information about that option, seeSection 4.5.1, “mysql — The MySQL Command-Line Client”.