正则关联的多表查询，MYSQL函数Replace、Regexp的用法

最新推荐文章于 2024-08-23 15:26:45 发布

Tryingwa

最新推荐文章于 2024-08-23 15:26:45 发布

阅读量533

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Tryingwa/article/details/131808423

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

在物资管理系统的多表查询中，遇到一个特殊场景，关联条件是通过物资表的主键id匹配申请表中material_ids字段（包含多个物资id的逗号分隔串）。为了解决这个问题，采用了子查询结合MySQL的REPLACE和REGEXP函数，首先通过REPLACE将逗号分隔的ids转换为管道分隔，然后在子查询中与物资表进行INNERJOIN，利用REGEXP进行精确匹配，避免了模糊匹配带来的额外数据。这种方式有效地解决了物资表和申请表之间的关联查询问题。

摘要由CSDN通过智能技术生成

一般的多表查询：多张表通过外连接、内连接，关联条件是几张表的公共字段相等。

sql举例如下：

SELECT a.name,b.age FROM a LEFT JOIN b ON a.id=b.id

只要是公共字段的关联查询，基本上就是这样，复杂点就是ON后面接的条件可能是多个字段

SELECT a.name,b.age FROM a LEFT JOIN b ON** a.id=b.id AND a.code=b.code

要么就是ON后面会接一些定性条件

SELECT a.name,b.age FROM a LEFT JOIN b ON **a.id=b.id AND a.type=1

但是在最近的开发需求中，我遇到了这样一个场景，导致多表查询的关联条件迟迟不能确定。

问题场景：

物资管理模块的工作流【物资的调动：调拨、领用、退库、入库】操作涉及到5张表：

物资信息表：material_info
物资调动对应的四种申请表：

调拨申请表：material_allocation_apply

报废申请表：material_scrap_apply

入库申请表：material_store_in

退库申请表：material_store_out
关联字段：物资信息表和这四张申请表之间都有关联，关联的条件是material_info的主键id对应四张申请表中的字段：material_ids（内容形式：多个物资id逗号隔开）

申请表：

物资表：

问题重点：

物资表和申请表的关联条件是申请表的material_ids字段正则匹配能够对应上物资表的主键id

解决方式：

子查询+MYSQL函数REPLACE：字段替换+MYSQL函数REGEXP ：正则匹配
1. 第一步：查询匹配集数据，用MYSQL函数REPLACE替换掉被匹配字段
【replcae（被匹配字段,‘分隔符’,‘|’）取别名】
先查询申请表的所有信息，并且将其中的material_ids字段使用MYSQL函数REPLACE转换成replace_id。
2.第二步：将匹配集结果作为子查询表和待匹配关联
将上面的查询结果作为子查询和物资表进行关联；
3.第三步：使用MYSQL函数REGEXP进行正则匹配，默认是模糊匹配，这里采用精确匹配
物资表的主键id使用MYSQL函数REGEXP匹配转换后的replace_id，因为这里是对物资id进行精确匹配，所以要在replace_id使用concat函数进行一些修饰。

SELECT
  mi.id 物资ID,
  mi.mc_id 类别ID,
  mi.store_id 仓库ID,
  mi.material_status 物资状态,
  yan.id 物资出库申请ID,
  yan.material_ids 申请物资ID,
  yan.receive_type 领取类型,
  yan.receive_id 领取人 
FROM
  ( 
  SELECT 
  REPLACE ( mso.material_ids, ',', '|' ) replace_id, 
  mso.* 
  FROM 
  material_store_out mso 
  ) yan
  INNER JOIN material_info mi 
  ON 
  mi.id REGEXP (concat( '^(', yan.replace_id, ')$' ))## '^('和')$'保证能够精确匹配

在这里插入图片描述
如果不用concat和限制符修饰，那么就会默认模糊匹配。例如我想查询物资id为10的物资信息和对应退库记录，那么实际的查询结果是物资id包含10的物资信息和对应退库记录，这样会筛选出很多不必要的数据。
在这里插入图片描述

可能有人不太懂模糊匹配和精确匹配在这里的区别，举例：
匹配集：{‘1,2,3’;‘12,123’,‘3’}
待匹配数据：1
如果将1和匹配集用MYSQL函数REGEXP进行匹配：
默认情况会返回{‘1,2,3’,‘12,123’}，因为前2组数据里面都包含1这个数字；
但是如果用精确匹配会返回{‘1,2,3’}，因为只有第1组数据里面有1这个独立数字，第二组只是包含1但没有1这个独立元素