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

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

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

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

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

Различие LIKE и REGEXP

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

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

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

Символ % — означает любое количество любых символов в строке;
Символ _ — означает 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’е. Все-таки тема очень важная, поскольку регулярные выражения — это очень мощный инструмент, позволяющий решать широкий круг задач. И, вместе с тем, тема достаточно непростая и её всестороннее рассмотрения кажется оправданным.