如何在MySQL中使用Regex匹配提取3字母加3-4位数字的变量模式

错误写法原因排查 #

• 你现有写法的核心错误是将REGEXP的返回值传入LOCATE函数：REGEXP仅会返回1（匹配成功）、0（匹配失败）、NULL（输入为空），并非匹配到的字符串位置，LOCATE实际在查找1/0在原字符串的位置，自然会返回错误偏移量，导致截取结果不符合预期。
• LIKE写法完全不适用你的需求：LIKE语法中_匹配任意单个字符，#仅匹配#本身，你写的%___###%实际匹配的是「任意3个字符+3个#号」的规则，和你要的「3个字母+3/4位数字」完全不匹配。

最快解决方案（MySQL 8.0+/MariaDB 10.0.5+ 适用） #

直接用数据库内置的REGEXP_SUBSTR函数实现正则匹配提取，代码最简、性能最高：

提取编码 #

SELECT 
  columnName,
  REGEXP_SUBSTR(columnName, '[a-zA-Z]{3}[0-9]{3,4}') AS extractedData
FROM tableName

仅筛选包含符合规则编码的记录 #

SELECT columnName 
FROM tableName 
WHERE columnName REGEXP '[a-zA-Z]{3}[0-9]{3,4}'

特殊场景：一条记录存在多个匹配项，仅取最后一个（你的示例适用） #

如果同一条URL里有多个符合规则的编码，需要取最后一个的话，调整正则加负向前瞻即可：

SELECT 
  columnName,
  REGEXP_SUBSTR(columnName, '[a-zA-Z]{3}[0-9]{3,4}(?!.*[a-zA-Z]{3}[0-9]{3,4})') AS extractedData
FROM tableName

低版本MySQL（无REGEXP_SUBSTR）兼容方案 #

如果用的是MySQL 5.x等不支持正则提取的版本，可以用手动截取的方式实现：

SELECT 
  columnName,
  SUBSTR(
    columnName,
    -- 计算匹配起始位置
    LENGTH(columnName) - 
    LENGTH(SUBSTRING_INDEX(columnName, REGEXP_SUBSTR(columnName, '[a-zA-Z]{3}[0-9]{3,4}'), -1)) - 
    IF(REGEXP_SUBSTR(columnName, '[a-zA-Z]{3}[0-9]{3,4}') IS NULL, 0, LENGTH(REGEXP_SUBSTR(columnName, '[a-zA-Z]{3}[0-9]{3,4}'))) + 1,
    -- 匹配长度
    IF(REGEXP_LIKE(columnName, '[a-zA-Z]{3}[0-9]{4}'), 7, 6)
  ) AS extractedData
FROM tableName

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