Принуждение MySQL к чувствительности к регистру с LIKE

Быстрый ответ

Если вы хотите, чтобы поиск с помощью оператора LIKE в MySQL был зависим от регистра символов, используйте ключевое слово BINARY:

SELECT * FROM table WHERE BINARY column LIKE 'CaseSensitive%';

Замените table, column и 'CaseSensitive%' на соответствующие значения для вашей конкретной задачи. Этот подход позволяет проводить сравнение строк в бинарном формате, где каждый символ рассматривается отдельно, с учетом его регистра.

Подробнее о BINARY

В MySQL оператор BINARY обеспечивает сравнение с учетом регистра, различая заглавные и строчные буквы. Он особенно полезен когда требуется строгое совпадение регистра, например, при проверке паролей или обработке уникальных кодов.

SELECT 'abc' LIKE BINARY 'ABC'; -- Возвращает 0 (нет), то есть: '"abc", сегодня тебе не повезло'

Пример демонстрирует, что LIKE BINARY требует точного совпадения регистра символов.

Как использовать COLLATE на вашу пользу

В MySQL коллации определяют правила сравнения символов. Коллация utf8_bin отлично подходит для регистрозависимых сравнений: она рассматривает символы в бинарном представлении, четко различая 'A' и 'a'.

SELECT * FROM table WHERE column COLLATE utf8_bin LIKE 'value%'; -- Путь к точному поиску

Ухищрение с использованием COLLATE позволяет раздельно найти 'a%' и 'A%' — обеспечивает максимальную точность для ваших данных.

SELECT * FROM table WHERE column COLLATE utf8_general_ci LIKE 'a%' AND column COLLATE utf8_bin LIKE 'A%';

Визуализация

Представьте оператор MySQL LIKE в образе библиотекаря с очками, тщательно искомого книги по названиям:

Запрос LIKE: "Мне нужны книги, названия которых ПОХОЖИ на 'Harry Potter', регистр значения не имеет."

Библиотекарь принесет следующие книги:

- "harry potter"
- "Harry Potter"
- "HARRY POTTER"

Теперь библиотекарь надевает специальные очки, способные различать регистр:

👓🔍 (режим учета регистра: ВКЛЮЧЕН)

И вы отдаете следующую команду:

"Принеси мне книги, которые похожи на 'Harry Potter', но с учетом смотрящих через твои специальные очки"

И в итоге вы получаете:

- "Harry Potter"

Выбор режима с особыми очками говорит о том, что РЕГИСТР ИМЕЕТ ЗНАЧЕНИЕ! ✨

Плюсы и минусы поиска с регистрозависимостью

Использование BINARY и COLLATE дает вам больший контроль, однако стоит быть готовым к возможным сложностям:

• Производительность: Бинарные сравнения или использование специальных коллаций могут замедлять запросы.
• Согласованность: Данные должны быть в предсказуемом формате, ведь бинарное сравнение может выявить неожиданные расхождения.
• Точность: Важно точно определить шаблоны, так как поиск с масками может дать разные результаты, когда учитывается регистр.

Дополнительно: Углубленное изучение регистрозависимости

Сравнение с шаблонами через 'LIKE BINARY'

Оператор BINARY применим не только для сравнения целых строк — его можно использовать и с шаблонами:

SELECT * FROM users WHERE BINARY username LIKE 'john%'; -- Будут найдены 'john', 'john123', но не 'John' или 'JOHNdoe'

Подробнее об этом расскажет наш спикер на видео

Создание регистрочувствительных представлений

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

CREATE VIEW case_sensitive_users AS
SELECT * FROM users WHERE column COLLATE utf8_bin LIKE 'pattern%'; -- Упрощение работы с регистрозависимыми операциями

Полнотекстовый поиск с учетом регистра

Стандартные полнотекстовые индексы не различают регистр, однако применение BINARY позволит исправить это:

SELECT * FROM posts WHERE MATCH(title, body) AGAINST('keyword' IN NATURAL LANGUAGE MODE) 
AND title LIKE BINARY '%keyword%'; -- Приспосабливаем полнотекстовый поиск к регистрозависимости

Полезные материалы

1. MySQL :: Руководство по MySQL 8.0 :: B.3.4.1 Чувствительность к регистру при поиске в строках
2. Как использовать чувствительность к регистру в функции instr() в MySQL? – Stack Overflow
3. Оператор SQL LIKE
4. MySQL: Функция BINARY
5. LIKE – База знаний MariaDB