В чем разница LIKE и ILIKE в PostgreSQL
Что проверяют
Для тестировщика это вопрос не столько про синтаксис, сколько про умение писать проверочные запросы и видеть, где поиск в продукте может ошибаться. Следом обычно спрашивают, как найти строку с символом % или _ и какие тест-кейсы вы бы написали на поиск без учёта регистра.
Как отвечать
LIKE: сравнение с шаблоном с учётом регистра, стандартный SQL.'Anna' LIKE 'a%'даст false;ILIKE: то же самое без учёта регистра, расширение PostgreSQL.'Anna' ILIKE 'a%'даст true;- шаблоны:
%означает любое число символов, включая ноль,_ровно один символ. Шаблон должен совпасть со всей строкой, поэтому для поиска подстроки нужны%с двух сторон; - экранирование: по умолчанию экранирует обратная косая черта,
LIKE '50\%'ищет буквально «50%». Другой символ задаётся черезESCAPE; - операторы: в планах запросов встречаются
~~и~~*, это и естьLIKEиILIKE.
Про производительность стоит сказать честно: разница между операторами меньше, чем разница в использовании индекса. Обычный B-tree помогает только LIKE с шаблоном вида 'abc%', и то при подходящей сортировке или классе text_pattern_ops. Шаблон '%abc%' и почти любой ILIKE без специальных мер читают всю таблицу. Решения: индекс по выражению lower(name), тип citext или триграммный GIN-индекс из расширения pg_trgm, который поддерживает и LIKE, и ILIKE.
Для QA отсюда вытекают проверки поиска в продукте: разный регистр, кириллица и «ё», пробелы по краям, ввод % и _ пользователем, пустая строка. Если поиск построен на ILIKE без экранирования ввода, запрос «100%» найдёт лишнее.
Где спотыкаются
Путают, какой оператор чувствителен к регистру, или считают ILIKE стандартным SQL. В MySQL и SQL Server его нет, а LIKE там обычно и так нечувствителен к регистру из-за collation по умолчанию, поэтому перенесённый запрос ведёт себя иначе.
Забывают, что % и _ из пользовательского ввода работают как шаблон. Поиск по «user_1» найдёт и «userA1».
Уверены, что регистронезависимость одинаково работает для любого языка. Для кириллицы результат зависит от локали базы, и это стоит проверить на тестовом стенде, а не принимать на веру.