LIKE и ILIKE в SQL: поиск по строкам, регистр и индексы

Что делает шаблон LIKE

LIKE сравнивает строку с шаблоном по двум подстановочным символам. % заменяет любое количество символов, включая ноль. _ заменяет ровно один символ — не «ноль или один», а именно один. Всё остальное в шаблоне сравнивается буквально.

Из этого следует неочевидное: checkout_% и checkout% — разные условия. Первое требует хотя бы один символ после подчёркивания, причём подчёркивание здесь тоже шаблон, а не буквальный знак. Оно совпадёт и с checkouts_ok, потому что _ подходит под любую букву. Если нужно именно подчёркивание, его экранируют: checkout\_%.

Отдельно стоит помнить про NULL. Сравнение event_name LIKE 'checkout%' для строки с NULL даёт NULL, а не false, поэтому такая строка не попадёт ни в выборку, ни в её «отрицание» через NOT LIKE. Если события с пустым именем существуют, их нужно обрабатывать явно.

ILIKE и регистр: почему это не «просто галочка»

ILIKE — расширение PostgreSQL, которое сравнивает без учёта регистра. В стандартном SQL его нет, поэтому в других базах пишут lower(event_name) LIKE lower(:pattern) или используют регистронезависимую коллацию столбца.

Регистр в аналитике событий — почти всегда симптом, а не задача. Если в одной таблице лежат checkout_start и Checkout_Start, значит два клиента шлют разные имена событий, и ILIKE это молча склеит. Число получится правильным, а проблема с трекингом останется невидимой. Полезнее сначала посмотреть список уникальных значений с частотами, а уже потом решать, чинить ли трекинг или писать регистронезависимый фильтр.

Ещё одна ловушка — регистр в кириллице зависит от коллации базы. В типичной UTF-8 инсталляции с ICU или glibc-локалью ILIKE '%оплата%' найдёт «Оплата», но в базе с локалью C регистронезависимость для не-ASCII работать не будет. Если у вас смешанные русско-английские названия, проверьте это на своих данных, а не на предположении.

Три разные задачи, которые путают

Под «поиском по тексту» скрываются три задачи с разной ценой ошибки. Аналитический фильтр в отчёте должен давать воспроизводимое число: здесь нужен либо точный список значений через IN, либо явный префикс, зафиксированный в контракте событий. Исследовательский поиск нужен один раз, чтобы понять, что вообще лежит в таблице: здесь ILIKE с широким шаблоном уместен. Пользовательский поиск в интерфейсе — вообще отдельный продукт со своими требованиями к опечаткам, морфологии и скорости.

Смешивать их дорого. %checkout% в постоянном дашборде значит, что завтра команда добавит событие checkout_banner_view, и метрика вырастет без единого изменения в продукте. Ни один читатель дашборда об этом не узнает.

Почему поиск подстроки кладёт запрос

Обычный B-tree индекс хранит значения отсортированными. Префиксный шаблон checkout% — это по сути диапазон, и его теоретически можно взять из индекса. Шаблон с ведущим % диапазоном не является: подстрока может начинаться в любом месте строки, поэтому базе остаётся прочитать все строки и проверить каждую.

В PostgreSQL есть нюанс, на котором спотыкаются даже опытные разработчики: даже префиксный LIKE не использует обычный B-tree индекс, если база работает не в локали C. Нужен индекс с классом операторов text_pattern_ops — тогда сравнение идёт посимвольно и префиксный поиск снова становится диапазонным.

Для шаблонов с ведущим % и для ILIKE есть отдельный механизм — расширение pg_trgm. Оно разбивает строку на триграммы и строит по ним GIN-индекс, который умеет ускорять и LIKE '%checkout%', и ILIKE. Цена — размер индекса и стоимость записи, а также то, что на шаблонах короче трёх символов выигрыша не будет.

-- префиксный LIKE в базе с обычной локалью
CREATE INDEX events_name_prefix_idx
  ON events (event_name text_pattern_ops);

-- регистронезависимый префикс: индексируем результат функции
CREATE INDEX events_name_lower_idx
  ON events (lower(event_name) text_pattern_ops);

-- поиск подстроки в любом месте, включая ILIKE
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX events_name_trgm_idx
  ON events USING gin (event_name gin_trgm_ops);

-- проверяем, что план действительно изменился
EXPLAIN ANALYZE
SELECT count(*) FROM events
WHERE event_name ILIKE '%checkout%';

Экранирование: пользовательский ввод — это тоже шаблон

Если строка поиска приходит от пользователя или из фильтра дашборда, символы % и _ внутри неё сохраняют смысл шаблона. Запрос по артикулу A_100 найдёт и A1100, и AX100. А поиск по одному символу % вернёт вообще всё, что есть в таблице.

Лечится это экранированием: перед подстановкой заменить %, _ и сам символ экранирования на экранированные версии. В PostgreSQL по умолчанию роль экранирующего символа играет обратный слеш, но лучше задавать его явно через ESCAPE — так поведение не зависит от настроек и диалекта.

Отдельно напомню очевидное, которое всё равно регулярно ломают: значение подставляется параметром запроса, а не склейкой строк. Экранирование шаблона и защита от SQL-инъекции — это две разные вещи, и нужны обе.

-- 1. в приложении: экранируем спецсимволы шаблона
--    'A_100' -> 'A!_100'   (! выбран как escape-символ)

-- 2. в запросе: параметр + явный ESCAPE
SELECT sku, title
FROM products
WHERE sku ILIKE :pattern ESCAPE '!'
ORDER BY sku
LIMIT 50;

-- :pattern = '%' || экранированный_ввод || '%'

Когда LIKE — неправильный инструмент

LIKE работает с подстроками, а не со словами. Он не знает про морфологию: %оплат% найдёт «оплата» и «оплатить», но заодно «неоплаченный», а %купить% не найдёт «куплю». Он не переживает опечатки: чекаут и чекуат для него разные строки. И он не умеет ранжировать: все совпадения одинаково хороши.

Если задача про слова и релевантность, нужен полнотекстовый поиск: to_tsvector приводит текст к нормальным формам, tsquery описывает запрос, а ts_rank даёт ранжирование. Если задача про опечатки и похожесть — тот же pg_trgm умеет считать similarity и сортировать по ней. Если задача про категории и семейства сущностей — правильный ответ обычно не в поиске вообще, а в справочнике или в отдельной колонке.

Последний случай встречается в аналитике чаще всех. Когда в запросе появляется третий LIKE подряд, это сигнал: в данных не хватает измерения. Событию нужна колонка event_group, товару — category_id, каналу — нормализованный справочник. Один раз добавить поле дешевле, чем поддерживать растущую цепочку шаблонов в десяти дашбордах.

• Нужны слова и релевантность — полнотекстовый поиск, а не LIKE.
• Нужна устойчивость к опечаткам — similarity из pg_trgm.
• Нужны сложные правила — регулярные выражения, но с оглядкой на стоимость.
• Нужны устойчивые группы для отчётов — колонка или справочник, а не шаблон.

Рабочий пример: аудит имён событий

Разберём типичную задачу целиком. Прежде чем считать метрику чекаута, нужно понять, какие имена событий вообще существуют, как они распределены по частоте и не разъехались ли они по регистру между платформами.

Запрос ниже делает три вещи сразу: собирает список совпадений с частотами, показывает регистр в исходном виде и считает, сколько разных платформ шлёт каждое имя. Именно последняя колонка обычно и вскрывает проблему — когда одно и то же действие называется по-разному на web и в приложении.

SELECT
  event_name,
  lower(event_name)                       AS normalized_name,
  count(*)                                AS events,
  count(DISTINCT user_id)                 AS users,
  count(DISTINCT platform)                AS platforms,
  min(occurred_at)::date                  AS first_seen
FROM events
WHERE occurred_at >= date '2026-07-01'
  AND occurred_at <  date '2026-08-01'
  AND event_name ILIKE '%checkout%'
GROUP BY event_name, lower(event_name)
ORDER BY events DESC;

Как прочитать результат

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

Дальше решение принимается не в SQL, а в разговоре с командой. Checkout_Start нужно склеить с основным событием и починить трекинг на iOS. web_checkout_click — это не чекаут, а клик по кнопке, и в метрику он попадать не должен. После этого фильтр в дашборде становится явным списком значений, а не шаблоном.

Чеклист перед тем, как отдать число

Текстовый фильтр — это гипотеза о данных, а не факт о них. Короткая проверка перед публикацией отчёта закрывает большую часть ошибок.

• Вы видели полный список значений, попавших под шаблон, а не только итоговое число?
• В шаблоне нет случайного _, который на самом деле означает «любой символ»?
• Строки с NULL обработаны явно, а не потерялись вместе с NOT LIKE?
• Пользовательский ввод экранирован и подставлен параметром?
• Если запрос попадёт в дашборд — фильтр зафиксирован списком значений, а не остался широким %слово%?
• На реальном объёме проверен план выполнения, а не только результат на выборке за день?