Когда мы работаем с текстовыми данными в базе данных, часто кажется, что сравнение строк - это просто сравнение символов. На практике всё немного сложнее. За то, как база данных сравнивает и сортирует текст, отвечает механизм под названием Collation.

Collation - это набор правил, определяющий, как текстовые значения сравниваются и сортируются.

Например, collation может определять:

  • считать ли A и a одинаковыми;
  • учитывать ли диакритические знаки (é и e);
  • в каком порядке сортировать буквы;
  • как сравнивать символы разных языков.

Например, при case-insensitive collation:

SELECT 'Hello' = 'hello';

может вернуть true.

При case-sensitive collation результатом будет false.

Collation и Encoding - не одно и то же

Эти понятия часто путают.

Encoding определяет, как символы хранятся в байтах.

Например:

UTF-8
UTF-16

Collation определяет, как эти символы сравниваются и сортируются.

Упрощённо:

Encoding отвечает за хранение, Collation - за сравнение и сортировку.

Пример с сортировкой

Представим таблицу:

apple
Apple
banana
Banana

В зависимости от выбранного collation результат:

SELECT name FROM products ORDER BY name;

может отличаться.

В одной collation регистр будет иметь значение, в другой - нет.

Это особенно важно для поиска:

SELECT * FROM users WHERE username = 'John';

При case-insensitive collation такой запрос может также найти:

john
JOHN
joHN

Collation бывает на разных уровнях

В зависимости от СУБД collation может задаваться для:

  • всей базы данных;
  • таблицы;
  • конкретной колонки;
  • отдельного выражения или запроса.

Например, в MySQL можно встретить:

CREATE TABLE users (
    name VARCHAR(100) COLLATE utf8mb4_unicode_ci
);

Здесь ci означает case-insensitive - регистр не учитывается при сравнении.

Другой распространённый вариант: utf8mb4_bin

Он сравнивает значения более буквально, на уровне бинарного представления.

Почему это важно разработчику?

Проблемы с collation часто проявляются неожиданно:

  • поиск находит больше или меньше записей, чем ожидалось;
  • ORDER BY сортирует строки "неправильно";
  • уникальный индекс считает две строки одинаковыми;
  • сравнение строк работает по-разному в разных окружениях;
  • при JOIN между таблицами возникают ошибки из-за несовместимых collations.

Например, если колонка имеет case-insensitive collation, то значения:

User@example.com
user@example.com

могут считаться одинаковыми. Это особенно важно при создании UNIQUE индексов.

Collation - это не просто настройка сортировки. Она влияет на сравнение строк, поиск, сортировку и уникальность данных.

Source: Orkhan Alishov's notes