Как извлечь число после ключа в строке с помощью REGEXP_SUBSTR в MySQL

При работе с текстовыми данными в MySQL часто возникает задача извлечения определённых значений, например, числа, следующего за ключом "10":. Использование функции REGEXP_SUBSTR с регулярными выражениями может приводить к неожиданным результатам, особенно если не учитывать особенности нумерации групп. Рассмотрим типичную проблему и её решение.

Почему REGEXP_SUBSTR возвращает NULL или не ту часть?

В исходном запросе SELECT REGEXP_SUBSTR('{"10":19656,"11":19658}', '"10":(\\d+)', 1, 2) указан параметр occurrence (вхождение) равный 2. Это означает, что функция пытается найти второе совпадение всего шаблона, а не первую группу. В строке есть только одно совпадение для "10":\d+, поэтому возвращается NULL.

Правильный способ извлечения числа после ключа

Чтобы получить число 19656, нужно извлечь первое вхождение шаблона и указать номер группы. В MySQL REGEXP_SUBSTR возвращает совпадение целиком, если не задан параметр группы. Для извлечения подмаски (группы) используйте REGEXP_SUBSTR с указанием группы в четвёртом аргументе (начиная с MySQL 8.0).

SELECT REGEXP_SUBSTR('{"10":19656,"11":19658}', '"10":(\\d+)', 1, 1, NULL, 1);

Здесь:
1 - начальная позиция,
1 - номер вхождения (первое),
NULL - модификатор (не используется),
1 - номер группы (первая скобочная группа). Результат: 19656.

Альтернативные подходы без регулярных выражений

Если строка имеет строгий формат JSON, проще использовать встроенные функции работы с JSON:

• JSON_EXTRACT - извлекает значение по пути: SELECT JSON_EXTRACT('{"10":19656}', '$.10') вернёт 19656.
• JSON_UNQUOTE - удаляет кавычки, если нужно строковое значение.
• ->> оператор - сокращённая запись: SELECT '{"10":19656}' ->> '$.10'.

Эти методы более надёжны и производительны для JSON-данных.

Частые ошибки при использовании REGEXP_SUBSTR

Ошибка 1: путаница между номером вхождения и номером группы.
Ошибка 2: экранирование обратной косой черты. В строке SQL нужно удваивать \, чтобы получить один символ \ в регулярном выражении.
Ошибка 3: использование REGEXP_SUBSTR в старых версиях MySQL (до 8.0), где функция отсутствует.

Заключение

Для извлечения числа после ключа "10": в MySQL используйте REGEXP_SUBSTR с правильным указанием группы или, ещё лучше, функции для работы с JSON. Это избавит от путаницы с вхождениями и повысит читаемость кода.