Регулярные выражения в MySQL

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

Регулярные выражения в MySQL

Статья поделена на несколько логических блоков:

  1. Различие LIKE и REGEXP
  2. Шаблоны и практические примеры использования оператора Like
  3. Шаблоны и практические примеры использования Regexp

В качестве основы для примеров взята таблица битрикса b_module_to_module.

Различие LIKE и REGEXP

В чем различие между LIKE и REGEXP? Ответ прост: LIKE — это оператор, использующий в своей основе простейшие регулярные выражения, который, в основном, используется в конструкции с …WHERE… и возвращает бинарный ответ. REGEXP же — это полноценный оператор регулярных выражений в MySQL, который производит сравнение строки на соответствие шаблону регулярного выражения. Очевидно, что в плане скорости REGEXP проигрывает LIKE, но позволяет решать более нетривиальные задачи.

Шаблоны и практические примеры использования оператора Like

Оператор LIKE имеет только два символа, которые можно использовать в построении шаблонов:

  1. Символ % — означает любое количество любых символов в строке;
  2. Символ _ — означает 1 любой символ в строке.

Примеры использования оператора LIKE:

Поиск выражения, содержащего ‘file’ в  TO_METHOD

SELECT
*
FROM
b_module_to_module
WHERE
TO_METHOD LIKE '%File%';

Обратный пример: Выбор всех строк, которые НЕ содержат ‘file’ в  TO_METHOD

SELECT
*
FROM
b_module_to_module
WHERE
TO_METHOD NOT LIKE '%File%';

Выбрать все строки, содержащие ‘»block» и неизвестную первую букву:

SELECT
*
FROM
b_module_to_module
WHERE
FROM_MODULE_ID LIKE '_block';

В целом, это всё, что может оператор LIKE, для простой выборки строк этого достаточно, но не более. Теперь переходим к рассмотрению более мощного оператора REGEXP.

Шаблоны и практические примеры использования Regexp

Как было сказано выше, оператор REGEXP позволяет производить поиск значений по шаблонам. К слову, у него есть синоним — RLIKE.

REGEXP пользуется следующим синтаксисом:

{m,n}Предыдущий символ повторяется от m до n раз{m,n}Предыдущий символ повторяется от m до n раз

Метасимволы шаблонов REGEXP

Символ Значение
^ Начало строки
$ Конец строки
, Любой отдельный символ
[…] Любой символ, перечисленный в квадратных скобках ([abc], [а-я], [а-я0-9] и т.д.)
[^ …] Любой символ, что не указан в квадратных скобках ([^0-9])
p1 | p2 Или р1 или р2 (символ | означает «или»)
* Предыдущий символ повторяется 0 или более раз (.* — любой символ повторяется)
+ Предыдущий символ повторяется 1 или более раз
{n} Предыдущий символ повторяется n раз
{m,n} Предыдущий символ повторяется от m до n раз
[[:<:]] Начало слова
[[:>:]] Конец слова
‘\t’ Табуляция
‘\f’ Конец файла
‘\n’ Перевод строки
‘\r’ Возврат строки
‘\\’ Обратный слеш

Также, помимо перечисленных выше символов, можно использовать символьные классы POSIX:

  • [:alnum:] — Алфавит и цифры
  • [:alpha:] — Только символы алфавита
  • [:blank:] — Пробел и табуляция
  • [:cntrl:] — Управляющие символы
  • [:digit:] — Десятичные цифры
  • [:graph:] — Графические символы
  • [:lower:] — Алфавит в нижнем регистре
  • [:print:] — Невидимые символы
  • [:punct:] — Знаки препинания
  • [:space:] — Пробел, табуляция, новая строка, возврат каретки
  • [:upper:] — Алфавит в верхнем регистре
  • [:xdigit:] — Шестнадцатеричные цифры

Примеры использования REGEXP в MySQL

Выбрать те строки, где b_module_to_module или iblock или main:

SELECT
*
FROM
b_module_to_module
WHERE
FROM_MODULE_ID REGEXP 'iblock|main';

Выбрать те строки, где Add в конце строки:

SELECT
*
FROM
b_module_to_module
WHERE
MESSAGE_ID REGEXP 'Add$';

Выбрать те строки, где название модуля 5 символов:

SELECT
*
FROM
b_module_to_module
WHERE
FROM_MODULE_ID REGEXP '^.{5}$';

Выбрать те строки, где слово Search стоит особняком, а не в составе названия классов (ToSearch и т.д.):

SELECT
*
FROM
b_module_to_module
WHERE
TO_CLASS REGEXP '[[:<:]]search[[:>:]]';

Пример использования POSIX. Выбрать все строки, в которых нет символов алфавита в верхнем регистре:

SELECT
*
FROM
b_module_to_module
WHERE
TO_MODULE_ID REGEXP '[^[:upper:]]';

В данной публикации было рассмотрено использование регулярных выражений в MySQL. Хочется заметить, что это не последняя статья из цикла, посвященного регуляркам: На очереди еще аналогичные статьи, посвященные регуляркам в PHP и Python’е. Все-таки тема очень важная, поскольку регулярные выражения — это очень мощный инструмент, позволяющий решать широкий круг задач. И, вместе с тем, тема достаточно непростая и её всестороннее рассмотрения кажется оправданным.