LIKE и ILIKE в SQL: разница, шаблоны % и _, регистр и индексы

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

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

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

Отдельно стоит помнить про 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 это молча склеит. Число получится правильным, а проблема с трекингом останется невидимой. Полезнее сначала посмотреть список уникальных значений с частотами, а уже потом решать, чинить ли трекинг или писать регистронезависимый фильтр.

Ещё одна ловушка — регистр в кириллице. По документации PostgreSQL ILIKE сводит регистр по правилам действующей локали, поэтому для не-ASCII результат зависит от кодировки и локали базы. В PostgreSQL 14 с кодировкой UTF-8 и локалью en_US.UTF-8 'Оплата' ILIKE '%оплата%' возвращает true, а то же сравнение по правилам локали C (COLLATE "C") — false: в ней регистр есть только у букв ASCII. В DuckDB — true. Если у вас смешанные русско-английские названия, проверьте это на своих данных, а не на предположении.

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

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

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

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

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

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

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

Блок ниже выполнен в PostgreSQL 14 на временной копии events учебной базы: после каждого индекса снят план, всё внутри транзакции с ROLLBACK.

BEGIN;

-- учебная вставка: временная копия событий, общую таблицу не трогаем
CREATE TEMP TABLE demo_events ON COMMIT DROP AS
SELECT * FROM events;
ANALYZE demo_events;

-- до индексов: читается вся таблица
EXPLAIN (COSTS OFF)
SELECT count(*) FROM demo_events WHERE event_name LIKE 'report%';
-- Seq Scan on demo_events

-- префиксный LIKE: text_pattern_ops нужен, если сортировка базы не C
CREATE INDEX demo_events_name_prefix_idx
  ON demo_events (event_name text_pattern_ops);

EXPLAIN (COSTS OFF)
SELECT count(*) FROM demo_events WHERE event_name LIKE 'report%';
-- Bitmap Index Scan on demo_events_name_prefix_idx

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

EXPLAIN (COSTS OFF)
SELECT count(*) FROM demo_events WHERE lower(event_name) LIKE 'report%';
-- Bitmap Index Scan on demo_events_name_lower_idx

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

EXPLAIN (COSTS OFF)
SELECT count(*) FROM demo_events WHERE event_name ILIKE '%export%';
-- Bitmap Index Scan on demo_events_name_trgm_idx

-- шаблон из двух символов: триграмм нет, снова вся таблица
EXPLAIN (COSTS OFF)
SELECT count(*) FROM demo_events WHERE event_name ILIKE '%ex%';
-- Seq Scan on demo_events

ROLLBACK;  -- копия, индексы и расширение исчезают

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

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

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

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

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

-- учебная вставка: пять артикулов и строка, которую ввёл пользователь
WITH products (sku) AS (
  VALUES ('A_100'), ('A1100'), ('AX100'), ('A_1000'), ('B_100')
),
search (user_input) AS (
  VALUES ('A_100')
)
SELECT p.sku,
       p.sku ILIKE '%' || s.user_input || '%' AS naive_match,
       -- ! выбран как escape-символ: сначала удваиваем его, потом экранируем % и _
       -- (в приложении эти замены делают до подстановки параметра)
       p.sku ILIKE '%' || replace(replace(replace(s.user_input, '!', '!!'), '%', '!%'), '_', '!_') || '%'
             ESCAPE '!' AS escaped_match
FROM products p
CROSS JOIN search s
ORDER BY p.sku;

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

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

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

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

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

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

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

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

В учебной базе чекаута нет, поэтому шаблон — %created%, а платформа — users.device. За июль под него попали workspace_created (951 событие) и report_created (612), оба в нижнем регистре и с двух устройств.

SELECT
  e.event_name,
  lower(e.event_name)                     AS normalized_name,
  count(*)                                AS events,
  count(DISTINCT e.user_id)               AS users,
  count(DISTINCT u.device)                AS devices,
  CAST(min(e.event_time) AS date)         AS first_seen
FROM events e
JOIN users u ON u.user_id = e.user_id
WHERE e.event_time >= TIMESTAMP '2026-07-01'
  AND e.event_time <  TIMESTAMP '2026-08-01'
  AND e.event_name ILIKE '%created%'
GROUP BY e.event_name, lower(e.event_name)
ORDER BY events DESC, e.event_name;

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

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

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

Нормализация до группировки: 42 написания канала сворачиваются в 16

В запросе аудита рядом с event_name уже стоит lower(event_name) — это минимальная нормализация, и она же подсказывает общий приём. Проблема из предыдущего раздела не уникальна для имён событий: то же самое происходит с каналами, артикулами, доменами и любым текстовым полем, которое заполняет не один клиент.

Возьмём условный пример: канал в таблице сессий. Прямой group by raw_channel даёт 42 группы. После снятия крайних пробелов остаётся 31 — значит у одиннадцати написаний отличался только пробел. После приведения регистра остаётся 18. И только последние две группы приходится разбирать руками: это опечатка в настройке рекламного кабинета и канал, который переименовали в середине месяца. Итог — 16 реальных значений вместо 42. Разница здесь не косметическая: пока написания не сведены, «organic» в дашборде меньше себя настоящего, а сравнение каналов между месяцами показывает изменение разметки, а не поведение пользователей.

Запросы ниже проходят те же шаги на учебной базе, испортив users.channel вставкой: 17 написаний, 9 после trim, 5 после lower; до четырёх настоящих каналов остаётся ручное правило для paid search, в запросах его нет. «Organic» до нормализации — 1 223 пользователя вместо 1 747.

Границы у трёх рабочих функций разные, и знать их стоит точно. trim в PostgreSQL снимает по краям только обычные пробелы: неразрывный U+00A0 из выгрузки, табуляция и перевод строки останутся на месте, а значение будет выглядеть чистым. В DuckDB trim убирает и неразрывный пробел, но табуляцию и перевод строки тоже оставляет. Для них есть trim(both набор from value) с явным набором символов и regexp_replace(value, '\s+', ' ', 'g') для пробелов внутри строки. lower сводит регистр, но не разные способы записать одно и то же: «Organic» и «organic» станут одной группой, «Органик» и «organic» — нет, потому что это разные алфавиты. А split_part(url, '?', 1) для адреса без вопросительного знака вернёт всю строку, зато split_part(url, '?', 2) вернёт не NULL, а пустую строку: count(landing_query) посчитает её заполненной, и доля пропусков в отчёте о качестве окажется заниженной. Лечится обёрткой nullif(split_part(...), '').

Делать нормализацию стоит один раз в витрине и не затирая исходное значение. Колонки raw_channel, channel и название правила рядом друг с другом отвечают на вопрос, который возникает при первом расхождении: это пользователи так себя ведут или кто-то поменял разметку. Полезная метрика такой витрины — доля строк, где нормализованное значение отличается от исходного: пока она стабильна, правило работает, а резкий рост означает новый источник с другим написанием. Отдельно стоит договориться, где нормализация заканчивается: привести Organic к organic — решение аналитика, а считать два адреса с разным регистром одним пользователем — решение про идентичность, и принимает его владелец данных.

Готовая нормализованная колонка заодно снимает половину поводов писать ILIKE. Фильтр в дашборде становится channel = 'organic' — точным, проверяемым и устойчивым к новому написанию, которое появится завтра. Это тот самый недостающий измеритель из раздела про неправильный инструмент, только для канала, а не для события.

-- учебная вставка: у трёх пользователей из десяти канал испорчен пробелом
-- или регистром, ещё у каждого десятого подчёркивание в paid_search заменено пробелом
with raw_signups as (
  select user_id,
         case user_id % 10
           when 0 then ' ' || channel
           when 1 then channel || ' '
           when 2 then upper(channel)
           when 3 then replace(channel, '_', ' ')
           else channel
         end as raw_channel
  from users
)
select
  raw_channel,
  nullif(lower(trim(regexp_replace(raw_channel, '\s+', ' ', 'g'))), '') as channel,
  case
    when raw_channel is null or trim(raw_channel) = '' then 'empty'
    when raw_channel <> trim(raw_channel)              then 'trim'
    when raw_channel <> lower(raw_channel)             then 'lower'
    else 'as_is'
  end                                                  as rule,
  count(*)                                             as users
from raw_signups
group by 1, 2, 3
order by users desc, raw_channel;

-- учебная вставка: та же, что в запросе выше
with raw_signups as (
  select user_id,
         case user_id % 10
           when 0 then ' ' || channel
           when 1 then channel || ' '
           when 2 then upper(channel)
           when 3 then replace(channel, '_', ' ')
           else channel
         end as raw_channel
  from users
)
select
  count(distinct raw_channel)                                     as raw_groups,   -- 17
  count(distinct trim(raw_channel))                               as after_trim,   -- 9
  count(distinct lower(trim(raw_channel)))                        as after_lower,  -- 5
  count(*)                                                        as rows_total,   -- 4613
  count(*) filter (where raw_channel <> lower(trim(raw_channel))) as rows_changed  -- 1385
from raw_signups;

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

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

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