Rechercher dans le manuel MySQL
10.8.5 The binary Collation Compared to _bin Collations
This section describes how the binary
collation for binary strings compares to _bin
collations for nonbinary strings.
Binary strings (as stored using the
BINARY
,
VARBINARY
, and
BLOB
data types) have a character
set and collation named binary
. Binary
strings are sequences of bytes and the numeric values of those
bytes determine comparison and sort order.
Nonbinary strings (as stored using the
CHAR
,
VARCHAR
, and
TEXT
data types) have a character
set and collation other than binary
. A given
nonbinary character set can have several collations, each of
which defines a particular comparison and sort order for the
characters in the set. One of these is the binary collation for
the character set, indicated by a _bin
suffix
in the collation name. For example, the binary collations for
utf8
and latin1
are named
utf8_bin
and latin1_bin
,
respectively. (utf8mb4
is an exception that
has two binary collations; see
Section 10.10.1, “Unicode Character Sets”.)
The binary
collation differs from
_bin
collations in several respects,
discussed in the following sections:
The Unit for Comparison and Sorting
Binary strings are sequences of bytes. For the
binary
collation, comparison and sorting
are based on numeric byte values. Nonbinary strings are
sequences of characters, which might be multibyte. Collations
for nonbinary strings define an ordering of the character
values for comparison and sorting. For _bin
collations, this ordering is based on numeric character code
values, which is similar to ordering for binary strings except
that character code values might be multibyte.
A nonbinary string has a character set and is automatically
converted to another character set in many cases, even when
the string has a _bin
collation:
For binary string columns, no conversion occurs. For cases similar to those preceding, the string value is copied byte-wise.
Collations for nonbinary character sets provide information
about lettercase of characters, so characters in a nonbinary
string can be converted from one lettercase to another, even
for _bin
collations that ignore lettercase
for ordering:
- +-------------+-------------+
- +-------------+-------------+
- | aa | ZZ |
- +-------------+-------------+
The concept of lettercase does not apply to bytes in a binary string. To perform lettercase conversion, the string must be converted to a nonbinary string:
- +-------------+------------------------------------+
- +-------------+------------------------------------+
- | aA | aa |
- +-------------+------------------------------------+
Many MySQL collations have a pad attribute of PAD
SPACE
. The Unicode collations based on UCA 9.0.0 and
higher have a pad attribute of NO PAD
; see
Section 10.10.1, “Unicode Character Sets”.
The pad attribute determines how trailing spaces are treated
for comparison of nonbinary strings (CHAR
,
VARCHAR
, and TEXT
values):
For
PAD SPACE
collations, trailing spaces are insignificant in comparisons; strings are compared without regard to any trailing spaces.NO PAD
collations treat spaces at the end of strings like any other character.
The differing behaviors can be demonstrated using the two
utf8mb4
binary collations, one of which is
PAD SPACE
, the other of which is
NO PAD
. The example also shows how to use
the INFORMATION_SCHEMA
COLLATIONS
table to determine the
pad attribute for collations.
- FROM INFORMATION_SCHEMA.COLLATIONS
- +------------------+---------------+
- | COLLATION_NAME | PAD_ATTRIBUTE |
- +------------------+---------------+
- +------------------+---------------+
- +------------+
- | 'a ' = 'a' |
- +------------+
- | 1 |
- +------------+
- +------------+
- | 'a ' = 'a' |
- +------------+
- | 0 |
- +------------+
For binary strings, all characters are significant in comparisons, including trailing spaces:
CHAR(
columns
store nonbinary strings. For inserts, values shorter than
N
)N
characters are extended with
spaces. For retrievals, trailing spaces are removed.
BINARY(
columns store binary strings. For inserts, values shorter than
N
)N
bytes are extended with
0x00
bytes. For retrievals, nothing is
removed; a value of the declared length is always returned.
- );
- +--------+----------------------+
- +--------+----------------------+
- | 61 | 61000000000000000000 |
- +--------+----------------------+
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-charset-binary-collations.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.