Как работают LIKE, ILIKE и поиск по шаблону
На этой странице
LIKE сравнивает строку с шаблоном: % — любая последовательность символов (в том числе пустая), _ — ровно один символ. В PostgreSQL LIKE чувствителен к регистру, ILIKE — нет (это расширение PostgreSQL); есть и регулярные выражения (~, ~*, SIMILAR TO). Обычный B-tree индекс помогает только префиксному поиску ('abc%', и то при подходящей сортировке/operator class), а поиск подстроки ('%abc%') читает всю таблицу — для него нужен trigram-индекс (pg_trgm) или полнотекстовый поиск. Пользовательский ввод экранируют: % и _ в нём — спецсимволы.
Вопрос
Как работают LIKE, ILIKE и регулярные выражения в SQL и как сделать поиск быстрым?
Шаблоны
| Шаблон | Найдёт |
|---|---|
'Ann%' | Начинается с «Ann» |
'%son' | Заканчивается на «son» |
'%dev%' | Содержит «dev» |
'A_a' | «A», любой символ, «a» |
'100\%' | Буквально «100%» (экранирование) |
SELECT * FROM users WHERE email LIKE '%@example.com';
SELECT * FROM users WHERE name ILIKE 'анна%'; -- без учёта регистра
SELECT * FROM users WHERE email NOT LIKE '%@test.%';Регулярные выражения (PostgreSQL)
SELECT * FROM users WHERE phone ~ '^\+7\d{10}$'; -- с учётом регистра
SELECT * FROM products WHERE title ~* 'macbook\s+pro'; -- без учёта регистра
SELECT regexp_replace(phone, '\D', '', 'g') FROM users; -- оставить только цифрыЭкранирование пользовательского ввода
const escaped = query.replace(/[\\%_]/g, (c) => `\\${c}`);
await db.query("SELECT * FROM products WHERE title ILIKE $1", [`%${escaped}%`]);Без экранирования запрос «100%» найдёт всё, что начинается с «100». Параметризация защищает от инъекций, но не от спецсимволов шаблона.
Производительность
| Запрос | Обычный B-tree | Что поможет |
|---|---|---|
LIKE 'abc%' | Да (при C-collation или индексе с text_pattern_ops) | B-tree |
LIKE '%abc' | Нет | Индекс по reverse(col) или trigram |
LIKE '%abc%', ILIKE | Нет | Trigram-индекс pg_trgm |
| Поиск по словам, морфология | Нет | Full-text search |
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_title_trgm_idx ON products USING gin (title gin_trgm_ops);
SELECT * FROM products WHERE title ILIKE '%keychron%'; -- теперь через индекс
SELECT * FROM products WHERE title % 'keyhron'; -- похожие строки (опечатки)Подробнее о текстовом поиске — в уроке как искать текст в SQL.
Частые ошибки
LIKE '%text%'по миллионам строк без индекса.- Не экранировать
%и_в пользовательском поиске. - Ожидать, что
LIKEв PostgreSQL игнорирует регистр (в MySQL это зависит от collation). - Использовать
LIKEдля поиска по словам с учётом морфологии.
Что важно помнить
%— любая строка,_— один символ.ILIKEи регулярные выражения — возможности PostgreSQL.- Поиск подстроки ускоряет
pg_trgm, поиск по словам — full-text search.
Мини-шпаргалка
LIKE,NOT LIKE,ILIKE.~,~*,regexp_replace.- Экранировать
%,_,\. - Префикс — B-tree, подстрока —
gin_trgm_ops.