12.8.5 The binary Collation Compared to _bin Collations — MySQL 9.2

12.8.5 Сравнение двоичного порядка сопоставления с _bin

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

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

Недвоичные строки (хранящиеся с использованием типов данных CHAR, VARCHAR и TEXT) имеют кодовую страницу и порядок сопоставления, отличный от binary. Определённая недвоичная кодовая страница может иметь несколько порядков сопоставления, каждый из которых определяет конкретный порядок сравнения и сортировки символов в наборе. Для большинства кодовых страниц один из этих порядков сопоставления — это двоичный порядок сопоставления, обозначенный суффиксом _bin в имени порядка сопоставления. Например, двоичные порядки сопоставления для latin1 и big5 называются latin1_bin и big5_bin соответственно. utf8mb4 является исключением, которое имеет два двоичных порядка сопоставления, utf8mb4_bin и utf8mb4_0900_bin; см. Раздел 12.10.1, «Наборы символов Юникод».

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

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

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

Преобразование кодовых страниц

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

При присваивании значений столбцов другому столбцу, имеющему другую кодовую страницу:
```
UPDATE t1 SET utf8mb4_bin_column=latin1_column;
INSERT INTO t1 (latin1_column) SELECT utf8mb4_bin_column FROM t2;
```
При присваивании значений столбцов для INSERT или UPDATE с использованием строковой литеральной константы:
```
SET NAMES latin1;
INSERT INTO t1 (utf8mb4_bin_column) VALUES ('string-in-latin1');
```
При отправке результатов с сервера клиенту:
```
SET NAMES latin1;
SELECT utf8mb4_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                                 |
+-------------+------------------------------------+

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

Порядки сопоставления MySQL имеют атрибут pad, значение которого равно PAD SPACE или NO PAD:

Большинство порядков сопоставления MySQL имеют атрибут pad, значение которого равно PAD SPACE.
Порядки сопоставления Юникода, основанные на UCA 9.0.0 и выше, имеют атрибут pad, значение которого равно NO PAD; см. Раздел 12.10.1, «Наборы символов Юникод».

Для недвоичных строк (значения CHAR, VARCHAR и TEXT) атрибут pad порядка сопоставления строки определяет обработку в сравнениях хвостовых пробелов в конце строк:

Для порядков сопоставления PAD SPACE хвостовые пробелы не учитываются в сравнениях; строки сравниваются без учёта хвостовых пробелов.
Порядки сопоставления NO PAD учитывают хвостовые пробелы в сравнениях, как и любой другой символ.

Разные методы можно продемонстрировать, используя два utf8mb4 двоичных порядка сопоставления, один из которых PAD SPACE, а другой — NO PAD. Пример также показывает, как использовать таблицу INFORMATION_SCHEMA COLLATIONS для определения атрибута pad порядков сопоставления.

mysql> SELECT COLLATION_NAME, PAD_ATTRIBUTE
       FROM INFORMATION_SCHEMA.COLLATIONS
       WHERE COLLATION_NAME LIKE 'utf8mb4%bin';
+------------------+---------------+
| COLLATION_NAME   | PAD_ATTRIBUTE |
+------------------+---------------+
| utf8mb4_bin      | PAD SPACE     |
| utf8mb4_0900_bin | NO PAD        |
+------------------+---------------+
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          1 |
+------------+
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_0900_bin;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          0 |
+------------+

Примечание

“Сравнение” в данном контексте не включает оператор сопоставления по шаблону LIKE, для которого хвостовые пробелы значимы независимо от порядка сопоставления.

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

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 utf8mb4 COLLATE utf8mb4_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    | 0x78000000000000000000 | 78     | 78000000000000000000 |
| x    | 0x78200000000000000000 | 78     | 78200000000000000000 |
+------+------------------------+--------+----------------------+