如何在MySQL的REGEXP_SUBSTR中用分组提取城市名称？

我明白你的困扰——你想从这段JSON字符串里精准提取城市名Minneapolis，但当前的正则查询返回了多余的前缀，别急，我给你两种解决方案，适配不同的MySQL版本：

首先先还原你的场景，目标JSON字符串是：

{"travelzoo_hotel_name":"Graduate Minneapolis","travelzoo_hotel_id":"223","city":"Minneapolis","country":"USA","sales_manager":"Stephen Conti"}

你之前用的查询语句返回了"city":"Minneapolis，这是因为正则没精准限定匹配范围，而且没利用好MySQL的分组提取能力。

方案一：MySQL 8.0+版本（推荐） #

MySQL 8.0及以上的REGEXP_SUBSTR支持指定提取正则分组的内容，我们可以调整正则表达式，精准捕获城市名：

SELECT REGEXP_SUBSTR(
    '{"travelzoo_hotel_name":"Graduate Minneapolis","travelzoo_hotel_id":"223","city":"Minneapolis","country":"USA","sales_manager":"Stephen Conti"}',
    '"city":"([[:alnum:]]+)"',
    1,
    1,
    'c',
    1
);

参数解释： #

• 第2个参数的正则"city":"([[:alnum:]]+)"：先精准匹配"city":"这个前缀，然后用([[:alnum:]]+)捕获由字母数字组成的城市名，最后用"匹配城市名的结束引号；
• 第6个参数1是关键：指定提取正则表达式里第1个分组的内容，也就是我们要的城市名；
• 其他参数：1表示从字符串开头匹配，1取第一次匹配结果，'c'表示区分大小写（可根据需求去掉）。

执行这个语句就能直接得到Minneapolis啦。

方案二：MySQL 8.0以下版本 #

如果你的MySQL版本不支持分组提取，我们可以用字符串截取函数组合来实现：

SELECT SUBSTRING_INDEX(
    SUBSTRING(
        '{"travelzoo_hotel_name":"Graduate Minneapolis","travelzoo_hotel_id":"223","city":"Minneapolis","country":"USA","sales_manager":"Stephen Conti"}',
        LOCATE('"city":"', '{"travelzoo_hotel_name":"Graduate Minneapolis","travelzoo_hotel_id":"223","city":"Minneapolis","country":"USA","sales_manager":"Stephen Conti"}') + 7
    ),
    '"',
    1
);

逻辑说明： #

1. 先用LOCATE找到"city":"在字符串中的位置，加上7（"city":"的字符长度）得到城市名的起始位置；
2. 用SUBSTRING截取从这个起始位置到字符串末尾的内容；
3. 最后用SUBSTRING_INDEX以"为分隔符，取第一部分内容，就是我们要的城市名。

内容的提问来源于stack exchange，提问作者Вовчик Николаенко