В материале рассматривается синтаксис и особенности использования инструкции LIKE
в запросах к БД MySQL, а также функция STRCMP()
, которая используется для сравнения строк.
str_col LIKE pattern
- выбирает все, что соответствует строке шаблона;str_col NOT LIKE pattern
- выбирает все, что НЕ соответствие строке шаблона;STRCMP()
- сравнение строк с учетом порядка сортировки.expr LIKE pattern [ESCAPE 'escape_char']
:Сопоставление с шаблоном с использованием шаблона SQL. Возвращает 1 (TRUE
) или 0 (FALSE
). Если expr
или pattern
имеют значение NULL
, то результат равен NULL
. Другими словами, при использовании в инструкции WHERE col_str LIKE pattern
будет выбирать строки из колонки col
, которые СООТВЕТСТВУЮТ паттерну pattern
.
Шаблон pattern
не обязательно должен быть буквальной строкой. Например, его можно указать как строковое выражение или столбец таблицы. В последнем случае столбец должен быть определен как один из строковых типов MySQL.
Общий пример использования:
SELECT col1, col2, col3 FROM table WHERE col_str LIKE 'привет%';
Согласно стандарту SQL, инструкция LIKE
выполняет сопоставление для каждого символа, поэтому он может давать результаты, отличные от оператора сравнения =
:
С инструкцией LIKE
в шаблоне pattern
можно использовать два подстановочных знака:
%
- соответствует любому количеству символов, даже нулю символов._
- соответствует ровно одному символу.mysql> SELECT 'David!' LIKE 'David_'; -> 1 mysql> SELECT 'David!' LIKE '%D%v%'; -> 1
По умолчанию, сравнение записей с шаблоном pattern
осуществляется БЕЗ учета регистра символов! Например:
-- поиск без учета регистра mysql> SELECT 'привет' LIKE 'Привет'; -> 1
Что бы инструкция LIKE
учитывала регистр символов, необходимо перед паттерном поместить инструкцию BINARY
(сопоставление будет осуществляться в бинарном режиме, которое производится с учетом регистра символов):
-- поиск с учетом регистра, используя инструкцию `BINARY` mysql> SELECT 'привет' LIKE BINARY 'Привет'; -> 0 mysql> SELECT 'Привет' LIKE BINARY 'Привет'; -> 1
или использовать/указать кодировку в инструкции COLLATE
, которая при сортировки учитывает регистр символов, при этом кодировка паттерна должна соответствовать кодировки, указанной в COLLATE
.
mysql> SELECT 'привет' LIKE 'Привет' COLLATE utf8_bin; -- Ошибка несоответствия кодировки -- ERROR 1253 (42000): COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'
Кодировку паттерна можно указать принудительно, использовав интродьюсер (имя кодировки с подчеркиванием), например _utf8mb4 "pattern"
.
-- поиск с учетом регистра символов при использовании -- сопоставления `COLLATE` mysql> SELECT 'привет' LIKE _utf8 'Привет' COLLATE utf8_bin; -> 0 mysql> SELECT 'Привет' LIKE _utf8 'Привет' COLLATE utf8_bin; -> 1
Как расширение стандартного SQL, MySQL разрешает использование инструкции LIKE
для числовых выражений. Например:
mysql> SELECT 150 LIKE '1_0'; -> 1 mysql> SELECT 1000 LIKE '1_0'; -> 0
Если необходимо, чтобы паттерн имел буквальные символы подстановочных знаков %
и/или _
, то перед ними следует поставить escape-символ (символ "экранирования").
Например:
\%
- соответствует одному символу %
.\_
- соответствует одному символу _
.mysql> SELECT 'David!' LIKE 'David\_'; -> 0 mysql> SELECT 'David_' LIKE 'David\_'; -> 1
Если в SQL-запросе необходимо использовать другой символ "экранирования" (вместо \
), то необходимо использовать инструкцию ESCAPE
:
mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|'; -> 1
Escape-последовательность должна состоять из одного символа или быть пустой, если escape-символ не используется. Выражение должно оцениваться как константа во время выполнения. Если включен SQL-режим NO_BACKSLASH_ESCAPES
, то последовательность не может быть пустой.
Примечание. MySQL использует escape-синтаксис языка C в строках (например,
\n
для представления символа новой строки). Если необходимо, чтобы строкаLIKE
содержала литерал\
, то нужно его удвоить. (Если конечно не включен режим SQLNO_BACKSLASH_ESCAPES
, в этом случае escape-символ не используется.) Например, для поиска\n
укажите его как\\n
. Для поиска\
укажите его как\\\\
; это связано с тем, что обратная косая черта удаляется синтаксическим анализатором один раз и снова при сопоставлении с шаблоном, оставляя одну обратную косую черту для сопоставления.
Исключение: В конце строки шаблона обратная косая черта может быть указана как \\
. В конце строки обратная косая черта обозначает саму себя, потому что после нее ничего нет, что можно было бы экранировать. Предположим, что таблица содержит следующие значения:
mysql> SELECT filename FROM t1; -- +--------------+ -- | C: | -- | C:\ | -- | C:\Programs | -- | C:\Programs\ | -- +--------------+
Чтобы проверить наличие значений, заканчивающихся обратной косой чертой, можно сопоставить значения, используя любой из следующих шаблонов:
mysql> SELECT filename, filename LIKE '%\\' FROM t1; -- +--------------+---------------------+ -- | C: | 0 | -- | C:\ | 1 | -- | C:\Programs | 0 | -- | C:\Programs\ | 1 | -- +--------------+---------------------+ mysql> SELECT filename, filename LIKE '%\\\\' FROM t1; -- +--------------+-----------------------+ -- | C: | 0 | -- | C:\ | 1 | -- | C:\Programs | 0 | -- | C:\Programs\ | 1 | -- +--------------+-----------------------+
expr NOT LIKE pattern [ESCAPE 'escape_char']
:Выражение expr NOT LIKE pattern
соответствует выражению NOT (expr LIKE pattern)
, разобранному выше. Другими словами, при использовании в инструкции WHERE col NOT LIKE pattern
будет выбирать строки из колонки col
, которые НЕ соответствуют паттерну pattern
.
Примечание. Совокупные запросы, включающие сравнения
NOT LIKE
со столбцами, содержащимиNULL
, могут привести к неожиданным результатам.
Например, рассмотрим следующую таблицу и данные:
CREATE TABLE foo (bar VARCHAR(10)); INSERT INTO foo VALUES (NULL), (NULL);
Запрос SELECT COUNT(*) FROM foo WHERE bar LIKE '%baz%';
возвращает 0. Можно предположить, что SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%';
вернет 2. Однако это не так: второй запрос возвращает 0. Это связано с тем, что NULL NOT LIKE expr
, всегда возвращает NULL
, независимо от значения expr
. То же самое верно для агрегированных запросов (GROUP BY
), включающих NULL
и сравнения с использованием NOT RLIKE
или NOT REGEXP
. В таких случаях необходимо явно проверить наличие NOT NULL
, используя оператор OR
(а не AND
), как показано здесь:
SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%' OR bar IS NULL;
STRCMP(expr1,expr2)
:MySQL функция STRCMP()
возвращает:
NULL
, если любой из аргументов равен NULL
, mysql> SELECT STRCMP('text', 'text2'); -> -1 mysql> SELECT STRCMP('text2', 'text'); -> 1 mysql> SELECT STRCMP('text', 'text'); -> 0
Функция STRCMP()
выполняет сравнение, используя сопоставление COLLATE
аргументов (COLLATE
по другому правила сортировки).
mysql> SET @s1 = _utf8mb4 'x' COLLATE utf8mb4_0900_ai_ci; mysql> SET @s2 = _utf8mb4 'X' COLLATE utf8mb4_0900_ai_ci; mysql> SET @s3 = _utf8mb4 'x' COLLATE utf8mb4_0900_as_cs; mysql> SET @s4 = _utf8mb4 'X' COLLATE utf8mb4_0900_as_cs; --> mysql> SELECT STRCMP(@s1, @s2), STRCMP(@s3, @s4); +------------------+------------------+ | STRCMP(@s1, @s2) | STRCMP(@s3, @s4) | +------------------+------------------+ | 0 | -1 | +------------------+------------------+
Если параметры сопоставления/сортировки COLLATE
несовместимы, один из аргументов должен быть преобразован для обеспечения совместимости с другим.
mysql> SET @s1 = _utf8mb4 'x' COLLATE utf8mb4_0900_ai_ci; mysql> SET @s2 = _utf8mb4 'X' COLLATE utf8mb4_0900_ai_ci; mysql> SET @s3 = _utf8mb4 'x' COLLATE utf8mb4_0900_as_cs; mysql> SET @s4 = _utf8mb4 'X' COLLATE utf8mb4_0900_as_cs; --> mysql> SELECT STRCMP(@s1, @s3); -- ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) -- and (utf8mb4_0900_as_cs,IMPLICIT) for operation 'strcmp' mysql> SELECT STRCMP(@s1, @s3 COLLATE utf8mb4_0900_ai_ci); +---------------------------------------------+ | STRCMP(@s1, @s3 COLLATE utf8mb4_0900_ai_ci) | +---------------------------------------------+ | 0 | +---------------------------------------------+