Оператор LIKE в SQL¶

LIKE — это оператор сравнения в языке структурированных запросов SQL, который используется в предложении WHERE для поиска строк по шаблону с использованием подстановочных символов. В отличие от оператора равенства (=), который требует точного совпадения, LIKE позволяет находить частичные совпадения текстовых значений, что делает его ключевым инструментом для реализации нечёткого поиска в реляционных базах данных.

¶Синтаксис и основные символы

Базовый синтаксис оператора выглядит следующим образом:

``sql SELECT * FROM table_name WHERE column_name LIKE 'pattern'; ``

Шаблон (pattern) представляет собой строку, которая может содержать два основных подстановочных символа, определённых стандартом SQL:

• % (процент) — заменяет любую последовательность символов любой длины, включая нулевую. Например, шаблон 'И%' найдёт все значения, начинающиеся с буквы «И», а '%ов' — все значения, заканчивающиеся на «ов».
• _ (нижнее подчёркивание) — заменяет ровно один произвольный символ. Например, шаблон '_аша' найдёт строки «Маша», «Даша», «Саша», но не найдёт «Наташа», так как перед «аша» должно быть ровно одно подчёркивание.

Если шаблон не содержит подстановочных символов, оператор LIKE ведёт себя аналогично оператору равенства, однако в некоторых СУБД это может приводить к различиям в производительности из-за невозможности использования индексов.

¶Регистрозависимость и национальные особенности

Поведение оператора LIKE в отношении регистра символов зависит от конкретной системы управления базами данных и настроек сортировки (collation). В Microsoft SQL Server по умолчанию сравнение регистронезависимо (зависит от collation базы данных), в то время как в PostgreSQL и MySQL поведение определяется типом используемой сортировки. В MySQL, например, сравнение по умолчанию регистронезависимо для типов данных CHAR и VARCHAR, но регистрозависимо для типов BINARY.

Для явного управления регистром в разных СУБД предусмотрены дополнительные функции, такие как LOWER() или UPPER(), которые приводят оба операнда к единому регистру перед сравнением. В PostgreSQL также доступен оператор ILIKE, который выполняет регистронезависимое сравнение без дополнительных преобразований.

¶Экранирование специальных символов

Поскольку символы % и _ имеют специальное значение в шаблоне, возникает необходимость поиска строк, содержащих эти символы буквально. Для этого используется предложение ESCAPE, которое определяет символ экранирования:

``sql SELECT * FROM products WHERE description LIKE '50!%%' ESCAPE '!'; ``

В данном примере символ ! указан как экранирующий, поэтому !% интерпретируется как литеральный символ процента, а завершающий % — как подстановочный символ. Стандарт SQL также допускает использование ключевого слова ESCAPE без явного указания символа, что подразумевает отсутствие экранирования.

¶Альтернативные операторы и функции

Помимо LIKE, в стандарте SQL определён оператор SIMILAR TO, который поддерживает более сложные шаблоны на основе регулярных выражений. Однако он реализован не во всех СУБД и менее распространён на практике.

Во многих системах управления базами данных существуют дополнительные средства для поиска по шаблону:

• В PostgreSQL оператор ~ позволяет использовать полноценные регулярные выражения.
• В MySQL функция REGEXP_LIKE() (или оператор REGEXP) также предоставляет возможности регулярных выражений.
• В Microsoft SQL Server функция PATINDEX() возвращает позицию первого вхождения шаблона в строку.

Оператор LIKE также тесно связан с полнотекстовым поиском, который реализован в большинстве современных СУБД (например, полнотекстовые индексы в MySQL и SQL Server, tsvector/tsquery в PostgreSQL). Полнотекстовый поиск обеспечивает более высокую производительность и лингвистическую обработку, но требует специальной настройки индексов и не заменяет LIKE для простых шаблонных запросов.

¶Производительность и использование индексов

Одним из ключевых ограничений оператора LIKE является невозможность эффективного использования стандартных индексов (B-tree) при поиске с подстановочным символом в начале шаблона. Запрос вида WHERE column LIKE '%текст' вынуждает СУБД выполнять полное сканирование таблицы, так как индекс строится по префиксам значений.

Однако если подстановочный символ отсутствует в начале шаблона (например, 'текст%'), многие СУБД могут использовать индекс для ускорения запроса. Для поддержки поиска по произвольным подстрокам применяются специальные типы индексов, такие как триграммные индексы (pg_trgm в PostgreSQL) или полнотекстовые индексы.

¶Особенности реализации в различных СУБД

Несмотря на стандартизацию, реализации оператора LIKE имеют отличия в разных системах:

• PostgreSQL поддерживает стандартный синтаксис, а также оператор ILIKE для регистронезависимого поиска. Существует возможность использования индексов с классом операторов varchar_pattern_ops для ускорения префиксного поиска.
• MySQL по умолчанию использует регистронезависимое сравнение для небинарных строк. В MySQL также доступен оператор LIKE BINARY для регистрозависимого поиска.
• Microsoft SQL Server позволяет использовать LIKE с символьными и юникодными типами данных. Для оптимизации префиксного поиска можно создавать индексы с включением столбца.
• Oracle требует особого внимания к значениям NULL, так как сравнение с NULL всегда возвращает неизвестное значение (NULL), которое трактуется как ложное в условиях WHERE.
• SQLite реализует LIKE с регистронезависимым поведением только для ASCII-символов по умолчанию, что может вызывать неожиданные результаты при работе с кириллицей.

¶Практические примеры использования

Оператор LIKE широко применяется для решения различных задач поиска:

1. Поиск по частичному совпадению имени: WHERE name LIKE 'Ан%' найдёт «Анна», «Андрей», «Антон».
2. Поиск по маске с одиночным символом: WHERE code LIKE 'A_1' найдёт «AB1», «AC1», но не «ABC1».
3. Поиск значений, содержащих цифры в определённой позиции: WHERE phone LIKE '+7___%'.
4. Фильтрация по датам, преобразованным в строковый формат: WHERE order_date LIKE '2024-%'.
5. Поиск по нескольким возможным вариантам с использованием нескольких условий LIKE, объединённых оператором OR.

¶Ограничения и рекомендации

При использовании оператора LIKE следует учитывать несколько важных ограничений. Во-первых, оператор неэффективен для поиска по большим объёмам данных без соответствующих индексов, особенно при наличии ведущего подстановочного символа. Во-вторых, поведение с NULL-значениями требует явной обработки, так как LIKE никогда не возвращает TRUE при сравнении с NULL.

Для повышения производительности рекомендуется избегать шаблонов с % в начале строки, использовать полнотекстовый поиск для сложных лингвистических запросов, а также комбинировать LIKE с другими условиями для сужения выборки. При работе с пользовательским вводом необходимо экранировать специальные символы для предотвращения непреднамеренного расширения шаблона.

¶Источники

1. Стандарт ISO/IEC 9075:2016 Information technology — Database languages — SQL.
2. Документация PostgreSQL: операторы сравнения и сопоставление с образцом.
3. Документация MySQL 8.0: операторы сравнения строк.
4. Документация Microsoft SQL Server: LIKE (Transact-SQL).
5. Документация Oracle Database: условия LIKE.