Поиск LIKE: буквальный текст, шаблоны и индексы

Пользователь, ищущий код с подчёркиванием, обычно ожидает буквальное подчёркивание, а не любой символ. Параметризация не позволяет входу превратиться в SQL-синтаксис, но не отменяет шаблоны LIKE. Безопасная передача и правильное совпадение являются разными задачами.

Текст или пользовательский шаблон

Для буквального префикса сначала экранируйте выбранный escape-символ, затем процент, подчёркивание и открывающую квадратную скобку. Только после этого добавьте завершающий шаблонный символ приложения. Если обработать escape последним, изменятся и символы, добавленные предыдущими заменами.

CREATE TABLE #Names(Name nvarchar(100) NOT NULL);
INSERT #Names VALUES(N'A_% report'),(N'ABC report'),(N'A! report');
DECLARE @input nvarchar(100)=N'A_%';
DECLARE @pattern nvarchar(201);
SET @pattern=REPLACE(@input,N'!',N'!!');
SET @pattern=REPLACE(@pattern,N'%',N'!%');
SET @pattern=REPLACE(@pattern,N'_',N'!_');
SET @pattern=REPLACE(@pattern,N'[',N'![')+N'%';
SELECT Name FROM #Names
WHERE Name LIKE @pattern ESCAPE N'!'
ORDER BY Name;

Пример ищет имена, действительно начинающиеся с A_%. Строка A_% report должна совпасть, ABC report не должна. Для экранирования выбрано восклицание. Восклицание самого пользователя сначала удваивается и остаётся буквальным текстом.

Входная переменная ограничена, а переменная шаблона рассчитана на расширение. Экранирование может удвоить длину, плюс требуется место для суффикса. Аналогично задайте размер клиентского параметра. Усечение способно удалить escape или последний шаблонный символ, изменив результат. Тип параметра должен соответствовать столбцу без ненужного преобразования всех значений.

Правила поиска определяют доступ

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

Регистр и акценты определяются collation. Заранее решите, равны ли cafe и café. Применение UPPER к каждому индексированному значению может осложнить доступ. Единый договор о сравнении проще поддерживать, чем разные преобразования в каждом запросе.

Пробелы также требуют правила. Фиксированный char может добавлять заполнение, а автоматическое удаление пробелов меняет намеренный код. Проверяйте настоящие типы параметров и рабочую collation, а не только литерал, вставленный в SSMS.

Предсказуемый поисковый интерфейс

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

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

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

Сохраняйте параметризацию и после экранирования: обработка метасимволов решает интерпретацию LIKE, а не SQL-инъекции. Проверяйте длину на границе API, чтобы приложение и база не усекали текст по-разному. Добавьте строку только из специальных символов и строку с escape в конце: они выявляют неправильный порядок замен. Отдельно проверьте длинную строку, у которой каждый символ требует экранирования, поскольку именно она создаёт максимальное расширение. Надёжный интерфейс одновременно сохраняет вход как данные и выполняет обещанные пользователю правила поиска.

Если пользователям разрешены настоящие шаблоны, выделите отдельный явно обозначенный режим. Не смешивайте его с обычным буквальным полем. Опишите поддерживаемые метасимволы и способ поиска самого процента. Для публичной формы дополнительно ограничьте число результатов и частоту запросов, чтобы короткий широкий шаблон не создавал неограниченную нагрузку на базу.

Техническая документация: Microsoft Learn: LIKE · Microsoft Learn: Collation.