Решение: сравнение строк с учётом регистра в MySQL

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

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

SELECT *
FROM table_name
WHERE BINARY column_name = 'ExactCase';

В этом контексте 'ExactCase' буду иметь отличия от 'exactcase'.

Исследуем регистрозависимость в MySQL

Влияние коллации на сравнение строк

Стандартная коллация в MySQL (latin1_swedish_ci) не учитывает регистр. Однако, применяя оператор COLLATE с бинарной коллацией (например, latin1_bin или utf8mb4_bin), можно произвести регистрозависимое сравнение:

SELECT * FROM table_name WHERE column_name COLLATE utf8mb4_bin = 'ExactCase';

Индексы и регистрочувствительное сравнение

Применение функций CONVERT и COLLATE к полям, используемым в сравнении по регистру, может снизить эффективность индексов. Чтобы минимизировать этот риск, применяйте данные функции к значению, а не к столбцу:

EXPLAIN SELECT * FROM table_name WHERE column_name COLLATE utf8mb4_bin = 'ExactCase';

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

Установление регистрозависимости на уровне таблицы

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

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

Учтите, что данная операция повлияет на все строковые запросы к этой таблице.

Особенности использования binary

Применение LIKE BINARY или BINARY в WHERE для не бинарных столбцов может вызвать полное сканирование таблицы и привести к другим проблемам из-за несоответствия кодировок.

Ухищрения регистрозависимости в MySQL

Правильная сортировка данных

Для сортировки результатов регистрозависимого запроса используйте ORDER BY:

SELECT * FROM table_name
WHERE BINARY column_name = 'ExactCase'
ORDER BY valuable_column DESC;

Регистрочувствительное частичное сопоставление

С помощью операторов COLLATE и LIKE вы можете осуществить частичное совпадение текста с учетом регистра:

SELECT * FROM table_name
WHERE column_name LIKE 'Exa%' COLLATE utf8mb4_bin;

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

Сравнение строк с учетом и без учета регистра можно сравнить с работой двух вышибалов на входе в клуб:

Сравнение строк в SQL: вышибалы данных

Вышибала, который не учитывает регистр (CI): "Ваши 'JOHN', 'John' или 'joHn' для меня одинаковы. Заводи!"

Вышибала, который учитывает регистр (CS): "Называешься 'JOHN'? 'John' или 'joHn' – к сожалению, не проходи!"

Строгость проверки документов соответствует строгости оператора BINARY:

-- CS-вышибала заметит любую незначительную ошибку в документе
SELECT * FROM `users` WHERE BINARY `username` = 'JOHN';

В отличие от более лояльного стандартного сравнения:

-- Опечатка? Не проблема, добро пожаловать!
SELECT * FROM `users` WHERE `username` = 'JOHN';

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

Выбор коллации и наборов символов

Для широкой поддержки UTF-8 более предпочтительным является utf8mb4 нежели utf8mb3. Убедитесь, что коллация соответствует набору символов для корректного сравнения.

Когда вам не подходят коллации _ci

Коллации, оканчивающиеся на _ci, не учитывают регистр и диакритические знаки. Так что если вам необходимо различать их, используйте другие варианты.

Проблемы связанные с использованием binary

Важно избежать несоответствия кодировок при использовании binary. Правильное сочетание наборов символов и коллаций обеспечивает точность сравнения, в котором 'ä' не равно 'a':

-- Казалось бы, все 'a' одинаковы!
SELECT 'ä' LIKE BINARY 'a' AS Unexpected;

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

1. MySQL :: Руководство MySQL 8.0 :: B.3.4.1 Регистрозависимый поиск по строкам — официальная документация о регистрочувствительном поиске.
2. Как сделать регистрозависимое строковое сравнение в SQL для MySQL? – Stack Overflow — обсуждение и примеры кода для регистрозависимых сравнений SQL.
3. MySQL :: Руководство MySQL 8.0 :: 10.15 Настройка кодировок — настройка кодировок и коллаций в MySQL.
4. Сравнение строк в SQL с учетом регистра в MySQL – Database Journal — дополнительные примеры и рассуждения о регистрозависимых сравнениях в MySQL.