Сообщить об ошибке.

MySQL: Поиск по шаблону, LIKE в запросах SELECT

MySQL: Использование LIKE и функции STRCMP()

В материале рассматривается синтаксис и особенности использования инструкции LIKE в запросах к БД MySQL, а также функция 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 содержала литерал \, то нужно его удвоить. (Если конечно не включен режим SQL NO_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() возвращает:

  • 0, если строки совпадают,
  • -1, если первый аргумент меньше второго в соответствии с текущим порядком сортировки,
  • NULL, если любой из аргументов равен NULL,
  • 1 - во всех остальных случаях возвращается .
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 |
+---------------------------------------------+