MySQL如何使用正则匹配实现两个表casenum字段的关联查询

核心思路是从对方系统表X的casenum字段中提取出中间的纯数字部分，再和我方系统表Y的casenum字段做等值匹配，共有两种常用实现方案：

方案1：字符串函数截取（推荐，性能更高） #

如果对方casenum格式固定为「首字母C+数字段+横杠后缀」（如C10972-02），优先用原生字符串截取函数，执行效率远高于正则匹配，适合大数据量场景：

-- 关联查询获取匹配的Y表id
SELECT 
  Y.id,
  X.casenum AS 对方系统casenum,
  Y.casenum AS 我方系统casenum
FROM X
INNER JOIN Y 
  ON Y.casenum = SUBSTRING(SUBSTRING_INDEX(X.casenum, '-', 1), 2)

函数逻辑说明：

• SUBSTRING_INDEX(X.casenum, '-', 1)：提取casenum中第一个横杠前的内容，示例输入C10972-02会得到C10972
• SUBSTRING(..., 2)：从第2位开始截取字符串，去掉首字母C，得到纯数字段10972

方案2：正则提取（兼容性强，适合格式不固定场景） #

如果对方casenum前缀不固定、或者有其他非数字前缀干扰，可以用正则提取第一个连续数字串匹配，MySQL 8.0及以上版本支持：

SELECT 
  Y.id,
  X.casenum AS 对方系统casenum,
  Y.casenum AS 我方系统casenum
FROM X
INNER JOIN Y 
  ON Y.casenum = REGEXP_SUBSTR(X.casenum, '[0-9]+')

注意事项 #

• 大数据量查询时，提前给Y表的casenum字段创建索引，可大幅提升关联查询效率
• 正式执行前可以先测试提取逻辑是否正确，避免匹配错误，测试语句如下：

  -- 测试X表casenum提取结果
  SELECT casenum, SUBSTRING(SUBSTRING_INDEX(casenum, '-', 1), 2) AS 提取的数字段 FROM X LIMIT 100;
  

• MySQL 5.x版本不支持REGEXP_SUBSTR函数，优先使用方案1即可。

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