10.8.5 The binary Collation Compared to _bin Collations — MySQL 5.7

10.8.5 Двоичное сравнение сортировок _bin

В этом разделе описывается, как сортировка binary для двоичных строк сравнивается с сортировками _bin для недвоичных строк.

Двоичные строки (хранящиеся с помощью типов данных BINARY, VARBINARY и BLOB) имеют кодировку и сортировку, названную binary. Двоичные строки представляют собой последовательности байтов, а числовые значения этих байтов определяют порядок сравнения и сортировки. См. Раздел 10.10.8, «Двоичный набор символов».

Недвоичные строки (хранящиеся с помощью типов данных CHAR, VARCHAR и TEXT) имеют набор символов и сортировку, отличную от binary. У данного набора недвоичных символов может быть несколько сортировок, каждая из которых определяет конкретный порядок сравнения и сортировки символов в наборе. Одна из них — двоичная сортировка, указываемая в имени сортировки суффиксом _bin. Например, двоичная сортировка для utf8 и latin1 называется соответственно utf8_bin и latin1_bin.

Сортировка binary отличается от сортировок _bin по нескольким аспектам, обсуждаемым в следующих разделах:

Единица сравнения и сортировки

Двоичные строки — это последовательности байтов. Для сортировки binary сравнение и сортировка основаны на числовых значениях байтов. Недвоичные строки — это последовательности символов, которые могут быть многобайтовыми. Сортировки для недвоичных строк определяют порядок сравнения и сортировки значений символов. Для сортировок _bin этот порядок основан на числовых значениях кода символа, что аналогично порядку для двоичных строк, за исключением того, что значения кода символа могут быть многобайтовыми.

Преобразование набора символов

Недвоичная строка имеет набор символов и в ряде случаев автоматически преобразуется в другой набор символов, даже если у строки сортировка _bin:

При присваивании значений столбцов другому столбцу с другим набором символов:
```
UPDATE t1 SET utf8_bin_column=latin1_column;
INSERT INTO t1 (latin1_column) SELECT utf8_bin_column FROM t2;
```
При присваивании значений столбцов для INSERT или UPDATE с использованием строковой литеральной:
```
SET NAMES latin1;
INSERT INTO t1 (utf8_bin_column) VALUES ('string-in-latin1');
```
При отправке результатов с сервера на клиент:
```
SET NAMES latin1;
SELECT utf8_bin_column FROM t2;
```

Для столбцов с двоичными строками преобразование не происходит. В аналогичных случаях значение строки копируется побайтово.

Преобразование регистра

Сортировки для недвоичных наборов символов содержат информацию о регистре символов, поэтому символы в недвоичной строке могут быть преобразованы из одного регистра в другой, даже для сортировок _bin, которые игнорируют регистр при упорядочивании:

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
mysql> SELECT LOWER('aA'), UPPER('zZ');
+-------------+-------------+
| LOWER('aA') | UPPER('zZ') |
+-------------+-------------+
| aa          | ZZ          |
+-------------+-------------+

Понятие регистра не применимо к байтам в двоичной строке. Для выполнения преобразования регистра строка должна быть сначала преобразована в недвоичную строку с помощью подходящего набора символов для данных, хранящихся в строке:

mysql> SET NAMES binary;
mysql> SELECT LOWER('aA'), LOWER(CONVERT('aA' USING utf8mb4));
+-------------+------------------------------------+
| LOWER('aA') | LOWER(CONVERT('aA' USING utf8mb4)) |
+-------------+------------------------------------+
| aA          | aa                                 |
+-------------+------------------------------------+

Обработка хвостовых пробелов при сравнении

Недвоичные строки имеют PAD SPACE поведение для всех сортировок, включая _bin. Хвостовые пробелы не учитываются при сравнении:

mysql> SET NAMES utf8 COLLATE utf8_bin;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          1 |
+------------+

Для двоичных строк все байты имеют значение при сравнении, включая хвостовые пробелы:

mysql> SET NAMES binary;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          0 |
+------------+

Обработка хвостовых пробелов при вставках и извлечениях

Столбцы CHAR(N) хранят недвоичные строки длиной N символов. При вставках значения, короче чем N символов, дополняются пробелами. При извлечении хвостовые пробелы удаляются.

Столбцы BINARY(N) хранят двоичные строки длиной N байтов. При вставках значения, короче чем N байтов, дополняются 0x00 байтами. При извлечении ничего не удаляется; всегда возвращается значение указанной длины.

mysql> CREATE TABLE t1 (
         a CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,
         b BINARY(10)
       );
mysql> INSERT INTO t1 VALUES ('x','x');
mysql> INSERT INTO t1 VALUES ('x ','x ');
mysql> SELECT a, b, HEX(a), HEX(b) FROM t1;
+------+------------+--------+----------------------+
| a    | b          | HEX(a) | HEX(b)               |
+------+------------+--------+----------------------+
| x    | x          | 78     | 78000000000000000000 |
| x    | x          | 78     | 78200000000000000000 |
+------+------------+--------+----------------------+