Регулярное выражение 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()
возвращает подстроку строки, совпадающую с регулярным выражением;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
содержал литерал\
, то нужно его удвоить. (Если не включен режим SQLNO_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 8.0.17 результат, возвращаемый этой функцией, использовал набор символов UTF-16; в MySQL 8.0.17 и более поздних версиях используется набор символов (кодировка) и сопоставление выражения COLLATE
, которое ищется для совпадений. Другими словами, в новых версиях MySQL результат замены возвращается в той кодировке, в которой определена изначальная строка expr
.
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 | +----------------------------------------------+
Самое простое регулярное выражение - это выражение, в котором нет специальных символов. Например, регулярное выражение 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