正则关联的多表查询

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

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. 先查询申请表的所有信息，并且将其中的material_ids字段使用MYSQL函数REPLACE转换成replace_id。
  2. 将上面的查询结果作为子查询和物资表进行关联；
  3. 关联条件：物资表的主键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的物资信息和对应退库记录，这样会筛选出很多不必要的数据。