re:re:actable
Урок 053 минуты чтения

Как работают 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.

Документация

PostgreSQL: Pattern Matching, PostgreSQL: pg_trgm.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам