MySQL: Поиск по регулярному выражению.

Регулярное выражение MySQL - это мощный способ указания шаблона для сложного поиска. В этом разделе рассматриваются функции и операторы, доступные для сопоставления регулярных выражений, и иллюстрируются примерами некоторые специальные символы и конструкции, которые можно использовать для операций с регулярными выражениями.

Смотрите также материал "Поиск по шаблону строки в запросах к БД MySQL".

MySQL реализует поддержку регулярных выражений с использованием международных компонентов для Unicode (ICU), которые обеспечивают полную поддержку Unicode и многобайтовую безопасность.

ВНИМАНИЕ! До MySQL 8.0.4 в MySQL использовалась реализация регулярных выражений Генри Спенсера, которая работает в побайтовом режиме и не является многобайтовой. Кириллица является многобайтной кодировкой, это означает, что до MySQL 8.0.4 поиск по регулярному выражению с использованием русского языка будет выдавать не корректные результаты.

До версии MySQL 8.0.22 с этими функциями можно было использовать бинарные строковые аргументы, но они приводили к противоречивым результатам. В MySQL 8.0.22 и более поздних версиях использование двоичной строки с любой из функций регулярных выражений MySQL отклоняется с помощью ER_CHARACTER_SET_MISMATCH.

Содержание:

Функции и операторы регулярных выражений;
- WHERE expr REGEXP pattern соответствует регулярному выражению;
- WHERE expr RLIKE pattern соответствует регулярному выражению;
- WHERE expr NOT REGEXP pattern НЕ соответствует регулярному выражению;
- WHERE expr NOT RLIKE pattern НЕ соответствует регулярному выражению;
- REGEXP_INSTR() возвращает индекс строки, соответствующей регулярному выражению;
- REGEXP_LIKE() возвращает соответствие регулярному выражению;
- REGEXP_REPLACE() осуществляет замену по регулярному выражению;
- REGEXP_SUBSTR() возвращает подстроку строки, совпадающую с регулярным выражением;
Синтаксис регулярных выражений БД MySQL;

Функции и операторы регулярных выражений.

`expr REGEXP pattern`,
`expr RLIKE pattern`:

Выражение expr REGEXP pattern возвращает 1, если строка expr соответствует регулярному выражению, заданному шаблоном pattern, и 0 в противном случае. Если expr или pat имеют значение NULL, то возвращаемое значение равно NULL.

Обычно выражение expr REGEXP pattern в MySQL используют в сочетании с инструкцией WHERE для поиска совпадающих записей в колонках таблицы expr с шаблоном pattern.

-- отберет все записи из `str_col`, которые соответствуют `regexp_pattern`
SELECT col1, col2, col3 FROM table WHERE str_col REGEXP "regexp_pattern";
-- что эквивалентно
SELECT col1, col2, col3 FROM table WHERE str_col RLIKE "regexp_pattern";

Инструкции REGEXP и RLIKE являются синонимами функции REGEXP_LIKE().

По умолчанию сравнение с регулярным выражением производится без учета регистра символов:

-- поиск без учета регистра
mysql> SELECT 'CamelCase' RLIKE 'CAMELCASE';
                     -> 1
mysql>  SELECT 'CamelCase' REGEXP 'CAMELCASE';
                     -> 1

Что бы регулярное выражение учитывало регистр символов, необходимо перед паттерном поместить инструкцию BINARY (сопоставление будет осуществляться в бинарном режиме):

-- поиск с учетом регистра, используя инструкцию `BINARY`
mysql> SELECT 'CamelCase' REGEXP BINARY 'СAMELCASE';
                     -> 0
mysql>  SELECT 'CamelCase' REGEXP BINARY 'CamelCase';
                     -> 1

или использовать правило сортировки COLLATE, которые учитывает регистр символов:

-- регистрозависимый поиск с использованием сопоставления `COLLATE`
mysql> SELECT 'CamelCase' REGEXP 'СAMELCASE' COLLATE utf8mb4_bin;
                     -> 0
mysql>  SELECT 'CamelCase' REGEXP 'CamelCase' COLLATE utf8mb4_bin;
                     -> 1

Дополнительные сведения о том, как происходит сопоставление, смотрите в описании функции REGEXP_LIKE(). Также смотрите раздел "Синтаксис регулярных выражений БД MySQL".

`expr NOT REGEXP pattern`,
`expr NOT RLIKE pattern`:

Выражение expr NOT REGEXP pattern это то же самое, что NOT (expr REGEXP pattern). Другими словами, выражение expr NOT REGEXP pattern возвращает 1, если строка expr НЕ соответствует регулярному выражению, заданному шаблоном pattern, и 0 в противном случае.

-- отберет все записи из `str_col`, которые НЕ соответствуют `regexp_pattern`
SELECT col1, col2, col3 FROM table WHERE str_col NOT REGEXP "regexp_pattern";
-- что эквивалентно
SELECT col1, col2, col3 FROM table WHERE str_col NOT RLIKE "regexp_pattern";

`REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])`:

MySQL функция REGEXP_INSTR() возвращает начальный индекс подстроки строки expr, соответствующей регулярному выражению, заданному шаблоном pat, и возвращает 0, если совпадений нет. Если expr или pat имеют значение NULL, возвращаемое значение равно NULL. Индексы символов начинаются с 1.

Функция REGEXP_INSTR() принимает следующие необязательные аргументы:

pos: Позиция в выражении, с которой следует начать поиск. Если опущено, значение по умолчанию равно 1.
occurrence: какое вхождение совпадения искать. Если опущено, значение по умолчанию равно 1.
return_option: Тип позиции для возврата. Если значение равно 0, то функция REGEXP_INSTR() возвращает позицию первого символа совпадающей подстроки. Если это значение равно 1, то функция REGEXP_INSTR() возвращает позицию, следующую за совпадающей подстрокой. Если опущено, то значение по умолчанию равно 0.
match_type: строка, указывающая, как выполнять сопоставление. Значение такое же, как описано для REGEXP_LIKE().

mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog');
+------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog') |
+------------------------------------+
|                                  1 |
+------------------------------------+
mysql> SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);
+---------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog', 2) |
+---------------------------------------+
|                                     9 |
+---------------------------------------+
mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{2}') |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
mysql> SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}');
+-------------------------------------+
| REGEXP_INSTR('aa aaa aaaa', 'a{4}') |
+-------------------------------------+
|                                   8 |
+-------------------------------------+

`REGEXP_LIKE(expr, pat[, match_type])`:

MySQL функция REGEXP_LIKE() возвращает 1, если строка expr соответствует регулярному выражению, указанному шаблоном pat, в противном случае 0. Если expr или pat равно NULL, то возвращаемое значение равно NULL.

Шаблон может быть расширенным регулярным выражением, синтаксис которого обсуждается в разделе "Синтаксис регулярных выражений БД MySQL". Шаблон pat не обязательно должен быть буквальной строкой. Например, он может быть указан в виде строкового выражения или столбца таблицы.

Необязательный аргумент `match_type`:

Это строка, которая может содержать любой или все следующие символы, указывающие, как выполнить сопоставление:

c: Сопоставление с учетом регистра.
i: Сопоставление без учета регистра.
m: Многострочный режим. Распознавать символ новой строки строк внутри строки. Поведение по умолчанию заключается в сопоставлении окончаний строк только в начале и конце строкового выражения.
n: символ '.' соответствует разделителям строк. По умолчанию для '.' соответствие остановке в конце строки.
u: Окончания строк только для Unix. Только символ новой строки распознается как окончание строки операторами соответствия ., ^ и $.

Если символы, указанные в match_type, определят противоречивые параметры, то самый правый из них имеет приоритет.

Общий пример использования с инструкцией WHERE:

-- отберет все записи из `str_col`, которые соответствуют `regexp_pattern`
SELECT col1, col2, col3 FROM table WHERE REGEXP_LIKE(str_col, "regexp_pattern", "c");
-- отберет все записи из `str_col`, которые НЕ соответствуют `regexp_pattern`
SELECT col1, col2, col3 FROM table WHERE NOT REGEXP_LIKE(str_col, "regexp_pattern", "c");

По умолчанию, при определении типа символа и выполнении сравнения, операции регулярных выражений используют набор символов (кодировку) и сопоставление COLLATE аргументов expr и pat. Если аргументы имеют разные кодировки или COLLATE, то применяются неявные правила приведения. Чтобы изменить поведение при сравнении, аргументы функции могут быть указаны с явными сопоставлением COLLATE.

mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE');
+---------------------------------------+
| REGEXP_LIKE('CamelCase', 'CAMELCASE') |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
mysql> SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs);
+------------------------------------------------------------------+
| REGEXP_LIKE('CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs) |
+------------------------------------------------------------------+
|                                                                0 |
+------------------------------------------------------------------+

Аргумент match_type может быть указан с символами c или i, чтобы переопределить чувствительность к регистру по умолчанию. Исключение: если любой из аргументов является двоичной строкой, то аргументы обрабатываются с учетом регистра как двоичные строки, даже если match_type содержит символ i.

Примечание. MySQL использует escape-синтаксис языка C в строках (например, для представления символа новой строки). Если необходимо, чтобы аргумент expr или pat содержал литерал \, то нужно его удвоить. (Если не включен режим SQL NO_BACKSLASH_ESCAPES, в этом случае управляющий символ не используется.)

mysql> SELECT REGEXP_LIKE('Michael!', '.*');
+-------------------------------+
| REGEXP_LIKE('Michael!', '.*') |
+-------------------------------+
|                             1 |
+-------------------------------+
mysql> SELECT REGEXP_LIKE('new*\n*line', 'new\\*.\\*line');
+----------------------------------------------+
| REGEXP_LIKE('new*\n*line', 'new\\*.\\*line') |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
mysql> SELECT REGEXP_LIKE('a', '^[a-d]');
+----------------------------+
| REGEXP_LIKE('a', '^[a-d]') |
+----------------------------+
|                          1 |
+----------------------------+

`REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])`:

MySQL функция REGEXP_REPLACE() заменяет вхождения в строке expr, которые соответствуют регулярному выражению, заданному шаблоном pat, строкой замены repl, и возвращает результирующую строку. Если expr, pat или repl имеют значение NULL, то возвращаемое значение равно NULL.

Функция REGEXP_REPLACE() принимает следующие необязательные аргументы:

pos: Позиция в выражении, с которой следует начать поиск. Если опущено, значение по умолчанию равно 1.
occurrence: какое вхождение совпадения заменить. Если этот параметр опущен, по умолчанию используется значение 0 (что означает "заменить все вхождения").
match_type: строка, указывающая, как выполнять сопоставление. Значение такое же, как описано для REGEXP_LIKE().

До MySQL 8.0.17 результат, возвращаемый этой функцией, использовал набор символов UTF-16; в MySQL 8.0.17 и более поздних версиях используется набор символов (кодировка) и сопоставление выражения COLLATE, которое ищется для совпадений. Другими словами, в новых версиях MySQL результат замены возвращается в той кодировке, в которой определена изначальная строка expr.

mysql> SELECT REGEXP_REPLACE('a b c', 'b', 'X');
+-----------------------------------+
| REGEXP_REPLACE('a b c', 'b', 'X') |
+-----------------------------------+
| a X c                             |
+-----------------------------------+
mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
+----------------------------------------------------+
| REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3) |
+----------------------------------------------------+
| abc def X                                          |
+----------------------------------------------------+

`REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])`:

MySQL функция REGEXP_SUBSTR() возвращает подстроку строки expr, совпадающую с регулярным выражением, указанным в шаблоне pat, NULL, если совпадений нет. Если expr или pat имеют значение NULL, то возвращаемое значение равно NULL.

Функция REGEXP_SUBSTR() принимает следующие необязательные аргументы:

pos: Позиция в выражении, с которой следует начать поиск. Если опущено, значение по умолчанию равно 1.
occurrence: какое вхождение совпадения заменить. Если этот параметр опущен, по умолчанию используется значение 1.
match_type: строка, указывающая, как выполнять сопоставление. Значение такое же, как описано для REGEXP_LIKE().

mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+');
+----------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+') |
+----------------------------------------+
| abc                                    |
+----------------------------------------+
mysql> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3);
+----------------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) |
+----------------------------------------------+
| ghi                                          |
+----------------------------------------------+

Синтаксис регулярных выражений БД MySQL.

Самое простое регулярное выражение - это выражение, в котором нет специальных символов. Например, регулярное выражение hello соответствует hello и ничему другому.

Нетривиальные регулярные выражения используют определенные специальные конструкции, поэтому они могут соответствовать более чем одной строке. Например, регулярное выражение hello|world содержит символ | (оператор чередования) и соответствует hello или world.

В качестве более сложного примера регулярное выражение B[an]*s соответствует любой из строк Bananas, Baaaaas, Bs и любой другой строке, начинающейся с B, заканчивающейся на s и содержащей между ними любое количество символов a или n.

До версии 8.0.4, для поддержки операций с регулярными выражениями MySQL использовала библиотеку регулярных выражений Генри Спенсера, а не международные компоненты для Unicode (ICU). В следующем списке перечислены основные специальные символы и конструкции, которые одинаково интерпретируются библиотекой Спенсера и ICU.

В MySQL старше 8.0.4, синтаксис регулярных выражений MySQL схож с синтаксисом регулярных выражений Python.

`'^'`:

Совпадение с началом строки.

mysql> SELECT REGEXP_LIKE('fo\nfo', '^fo$');
                   -> 0
mysql> SELECT REGEXP_LIKE('fofo', '^fo');
                   -> 1

`'$'`:

Совпадение с концом строки.

mysql> SELECT REGEXP_LIKE('fo\no', '^fo\no$');
                 -> 1
mysql> SELECT REGEXP_LIKE('fo\no', '^fo$');
                 -> 0

`'.'`:

Совпадение с любым символом (включая возврат каретки \r и новую строку \n, хотя для их соответствия в середине строки необходимо указать m (многострочный) символ управления совпадением или модификатор (?m) внутри шаблона).

mysql> SELECT REGEXP_LIKE('fofo', '^f.*$');
                 -> 1
mysql> SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$');
                 -> 0
mysql> SELECT REGEXP_LIKE('fo\r\nfo', '^f.*$', 'm');
                 -> 1
mysql> SELECT REGEXP_LIKE('fo\r\nfo', '(?m)^f.*$');
                 -> 1

`''`*:

Совпадение с нулем и более символов, стоящим перед *.

mysql> SELECT REGEXP_LIKE('Ban', '^Ba*n');
                 -> 1
mysql> SELECT REGEXP_LIKE('Baaan', '^Ba*n');
                 -> 1
mysql> SELECT REGEXP_LIKE('Bn', '^Ba*n');
                 -> 1

`'+'`:

Совпадение одним или несколькими символами, стоящим перед +.

mysql> SELECT REGEXP_LIKE('Ban', '^Ba+n');
                 -> 1
mysql> SELECT REGEXP_LIKE('Bn', '^Ba+n');
                 -> 0

`'?'`:

Совпадение с нулем или одним символом, стоящим перед ?.

mysql> SELECT REGEXP_LIKE('Bn', '^Ba?n');
                 -> 1
mysql> SELECT REGEXP_LIKE('Ban', '^Ba?n');
                 -> 1
mysql> SELECT REGEXP_LIKE('Baan', '^Ba?n');
                 -> 0

`'|'`:

Чередование (или). Соответствует любой из последовательностей, стоящей перед или после |.

mysql> SELECT REGEXP_LIKE('pi', 'pi|apa');
                 -> 1
mysql> SELECT REGEXP_LIKE('axe', 'pi|apa'); 
                 -> 0
mysql> SELECT REGEXP_LIKE('apa', 'pi|apa');
                 -> 1
mysql> SELECT REGEXP_LIKE('apa', '^(pi|apa)$');
                 -> 1
mysql> SELECT REGEXP_LIKE('pi', '^(pi|apa)$');
                 -> 1
mysql> SELECT REGEXP_LIKE('pix', '^(pi|apa)$');
                 -> 0

`'(abc)'`*:

Сопоставляет ноль или более экземпляров последовательности abc.

mysql> SELECT REGEXP_LIKE('pi', '^(pi)*$');
                 -> 1
mysql> SELECT REGEXP_LIKE('pip', '^(pi)*$');
                 -> 0
mysql> SELECT REGEXP_LIKE('pipi', '^(pi)*$');
                 -> 1

`'{n}'`,
`'{m,n}'`:

Обозначения {n} и {m,n} обеспечивают более общий способ написания регулярных выражений, которые соответствуют множеству/количеству вхождений предыдущего символа (или "части") шаблона. m и n - целые числа.

Если заданы как m, так и n, то m должно быть меньше или равно n.

а*: Может быть записано как a{0,}.
а+: Может быть записано как a{1,}.
а?: Может быть записано как a{0,1}.

Если быть более точным:

a{n} соответствует ровно n экземплярам символа a.
a{n,} соответствует n или более экземплярам a.
a{m,n} соответствует от m до n экземпляров a включительно.

mysql> SELECT REGEXP_LIKE('abcde', 'a[bcd]{2}e');
                 -> 0
mysql> SELECT REGEXP_LIKE('abcde', 'a[bcd]{3}e');
                 -> 1
mysql> SELECT REGEXP_LIKE('abcde', 'a[bcd]{1,10}e');
                 -> 1

`'[a-dX]'`,
`'[^a-dX]'`:

mysql> SELECT REGEXP_LIKE('aXbc', '[a-dXYZ]');
                 -> 1
mysql> SELECT REGEXP_LIKE('aXbc', '^[a-dXYZ]$');
                 -> 0
mysql> SELECT REGEXP_LIKE('aXbc', '^[a-dXYZ]+$');
                 -> 1
mysql> SELECT REGEXP_LIKE('aXbc', '^[^a-dXYZ]+$');
                 -> 0
mysql> SELECT REGEXP_LIKE('gheis', '^[^a-dXYZ]+$');
                 -> 1
mysql> SELECT REGEXP_LIKE('gheisa', '^[^a-dXYZ]+$');
                 -> 0

Чтобы использовать буквальный экземпляр специального символа в регулярном выражении, перед ним необходимо поставить два символа обратной косой черты (\). Синтаксический анализатор MySQL интерпретирует одну обратную косую черту, а библиотека регулярных выражений интерпретирует другую. Например, для сопоставления строки 1+2, содержащей специальный символ +, правильным является только последнее из следующих регулярных выражений:

mysql> SELECT REGEXP_LIKE('1+2', '1+2');
                 -> 0
mysql> SELECT REGEXP_LIKE('1+2', '1\+2');
                 -> 0
mysql> SELECT REGEXP_LIKE('1+2', '1\\+2');
                 -> 1

MySQL: Поиск по регулярному выражению.

Содержание:

Функции и операторы регулярных выражений.

expr REGEXP pattern,expr RLIKE pattern:

expr NOT REGEXP pattern,expr NOT RLIKE pattern:

REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]]):

REGEXP_LIKE(expr, pat[, match_type]):

Необязательный аргумент match_type:

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]]):

REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]]):

Синтаксис регулярных выражений БД MySQL.

'^':

'$':

'.':

'*':

'+':

'?':

'|':

'(abc)*':

'{n}','{m,n}':

'[a-dX]','[^a-dX]':