MySQL 5.7中如何通过正则提取链接中的id参数？

由于MySQL 5.7没有像PHP里preg_match_all那样直接的正则提取函数（REGEXP_SUBSTR要到MySQL 8.0才支持），我们可以通过字符串定位+截取的组合方式来实现，同时兼容链接中&和转义后的&两种参数分隔符。

方法：字符串函数组合实现 #

这是完全兼容MySQL 5.7的方案，能处理id参数在任意位置（开头、中间、末尾）的情况：

SELECT 
  link,
  CASE 
    -- 先判断链接中是否存在id参数
    WHEN LOCATE('id=', REPLACE(link, '&', '&')) > 0 THEN
      TRIM(
        SUBSTRING(
          -- 先把转义的&替换成&，统一处理分隔符
          REPLACE(link, '&', '&'),
          -- 定位到id=的位置，+3跳过'id='这三个字符
          LOCATE('id=', REPLACE(link, '&', '&')) + 3,
          -- 计算截取长度：如果id后面有&，就截取到&之前；如果是最后一个参数，截取到末尾
          IF(
            LOCATE('&', REPLACE(link, '&', '&'), LOCATE('id=', REPLACE(link, '&', '&'))) > 0,
            LOCATE('&', REPLACE(link, '&', '&'), LOCATE('id=', REPLACE(link, '&', '&'))) - LOCATE('id=', REPLACE(link, '&', '&')) - 3,
            LENGTH(REPLACE(link, '&', '&')) - LOCATE('id=', REPLACE(link, '&', '&')) - 2
          )
        )
      )
    -- 没有id参数时返回NULL
    ELSE NULL
  END AS extracted_id
FROM your_table_name;

关键函数解释 #

• REPLACE(link, '&', '&')：统一把HTML转义的&替换成普通&，避免分隔符不一致导致的提取错误
• LOCATE('id=', ...)：找到id=在处理后的链接中的起始位置
• SUBSTRING(...)：根据计算好的起始位置和长度，截取id参数的值
• TRIM()：去除可能的空格（如果参数值前后有空格的话）
• CASE语句：处理没有id参数的情况，避免返回无效内容

举个例子，对于链接https://item.taobao.com/item.htm?id=550585067709或者https://item.taobao.com/item.htm?name=test&id=550585067709&type=1，这个SQL都能正确提取出550585067709。

内容的提问来源于stack exchange，提问作者Evgeny Gil