Чувствительный к регистру поиск в MySQL/MariaDB

Оглавление

1. MySQL/MariaDB ищут строки без учёта регистра

2. Как в MySQL/MariaDB сделать поиск чувствительным к регистру

3. Почему MySQL/MariaDB ищут строки без учёта регистра

4. Почему добавление «BINARY» делает поиск строки чувствительным к регистру

5. Правильно ставить «BINARY» перед искомой строкой или перед именем колонки

6. Оптимизация чувствительного к регистру поиска (бинарного поиска) в MySQL/MariaDB

---

MySQL/MariaDB ищут строки без учёта регистра

Поиск по строкам в MySQL может дать неожиданные результаты. К примеру, попробуем найти все строки, в которых встречается слово «test»:

SELECT * FROM `TestTABLE` WHERE `test_column` LIKE "%test%";

Получены следующие строки:

+----+----------------------------------------------------+
| id | test_column                                        |
+----+----------------------------------------------------+
|  6 | This is a test string                              |
|  7 | AND THIS IS A TEST LINE WRITTEN IN CAPITAL LETTERS |
|  8 | Test String Written In Camel Style                 |
|  9 | iNVERTED tEST sTRING wRITTEN iN cAMEL sTYLE        |
+----+----------------------------------------------------+

То есть были найдены значения, в которых присутствуют строки «test», «TEST», «Test» и «tEST».

Как в MySQL/MariaDB сделать поиск чувствительным к регистру

Чтобы поиск стал чувствительным к регистру букв, добавьте слово «BINARY»:

SELECT * FROM `TestTABLE` WHERE `test_column` LIKE BINARY "%test%";

Теперь получен результат именно такой, какой мы ожидаем от поиска с учётом регистра:

В предыдущем примере мы добавили оператор «BINARY» перед искомой строкой. Вместо этого оператор можно поместить перед именем колонки, в которой будет проводиться поиск:

SELECT * FROM `TestTABLE` WHERE BINARY `test_column` LIKE "%test%";

Второй вариант, когда «BINARY» идёт перед именем колонки, чуть хуже с точки зрения производительности.

Почему MySQL/MariaDB ищут строки без учёта регистра

Если ознакомиться с документацией (https://dev.mysql.com/doc/refman/8.3/en/case-sensitivity.html), то становится понятно, что «это не баг, это фича».

В типах данных которые называются «nonbinary strings» (недвоичные строки), а именно в CHAR, VARCHAR, TEXT, поиск использует «collation» сравниваемых данных, то есть сопоставление, не основанное на точном совпадении. Что такое «collation» — это тема для отдельной заметки. Но проще всего это понимать как правила сравнения/сортировки/поиска. И вот в соответствии с этими правилами регистр букв не учитывается.

С практической точки зрения это означает, что поиск строк выполняется без учёта регистра и, при сочетании некоторых условий, иногда даже без учёта знака акцента. То есть возможны ситуации, когда 'é' = 'e'.

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

Отсюда можно сделать первый важный вывод: если вы хотите, чтобы поиск по таблице всегда был чувствительным к регистру, то для строк вы можете использовать типы данных BINARY, VARBINARY, BLOB.

Как для TEXT имеются типы данных разной длины (TINYTEXT, TEXT, MEDIUMTEXT, и LONGTEXT), так и для BLOB имеются аналогичные типы данных (TINYBLOB, BLOB, MEDIUMBLOB и LONGBLOB).

Почему добавление «BINARY» делает поиск строки чувствительным к регистру

Ответ на этот вопрос даёт официальная документация, в ней сказано: сравнение между небинарной строкой и бинарной строкой трактуется как сравнение бинарных строк.

То есть, при добавлении «BINARY» в рассмотренном выше запросе:

SELECT * FROM `TestTABLE` WHERE `test_column` LIKE BINARY "%test%";

Искомая строка «test» начинается трактоваться как бинарная строка. В результате получается сравнение бинарной строки с небинарными данными, а в этом случае ситуация трактуется как сравнение бинарных строк. Короче говоря, поиск становится чувствительным к регистру.

Правильно ставить «BINARY» перед искомой строкой или перед именем колонки

Как можно догадаться, если мы ставим слово «BINARY» перед именем колонки, то данные в колонке начинают трактоваться как бинарные данные, а если ставим «BINARY» перед строкой, то строка начинает трактоваться как бинарная.

Имеется мнение, что если бинарной является строка, то это лучше для производительности. Хотя, если вдуматься, в любом случае все данные начинают считаться бинарными и разницы быть не должно.

Тем не менее, в моих тестах SQL запрос с «BINARY» перед искомой строкой всегда оказывался чуть быстрее, чем SQL запрос с «BINARY» перед именем колонки.

Оптимизация чувствительного к регистру поиска (бинарного поиска) в MySQL/MariaDB

Имеется мнение, что поиск с учётом регистра является более ресурсоёмкий, чем обычный поиск. Мои наблюдения при работе с таблицами на десятки тысяч записей размером до нескольких Гигабайт этого не подтверждают, но, возможно, это действительно так при работе с очень большими массивами данных.

Если в ваших условиях бинарный поиск действительно намного более ресурсоёмкий, то вы можете использовать следующую конструкцию для SQL запроса:

SELECT
	*
FROM
	(SELECT * FROM `table` WHERE `column` = 'value') as firstresult
WHERE
	BINARY `column` = 'value'

В результате выполнения, на первой стадии поиска НЕ чувствительного к регистру будет собран промежуточный результат. Затем будет выполнен бинарный поиск (чувствительный к регистру), но уже не по всему объёму данных, а по выборке, полученной на первом этапе.