如何区分逗号分隔存储的多类型ID对应的客户与物料实体？

一、优化数据结构（推荐方案） #

这是最彻底的解决办法——用逗号存储多值本身就违反数据库设计范式，后续维护、查询都会踩坑。直接拆分结构：

• 给类型ID表新增关联表type_mapping，字段包含主表ID、类型标识（比如customer/material）、类型ID
• 给具体值表新增关联表value_mapping，字段包含主表ID、类型标识、具体值ID

比如原来主表一条记录存的类型ID是"3,5"，拆分后type_mapping里是两条记录：

具体值表对应记录的"40,1"拆分后value_mapping：

查询时直接通过类型标识关联两张映射表，完全不会混淆对应关系。

二、无法修改结构时的临时处理 #

如果暂时动不了表结构，只能基于现有字符串处理——前提是两种类型的ID顺序固定（比如第一个永远是客户，第二个永远是物料）：

MySQL 实现 #

• 提取类型ID表的客户/物料ID：

  SELECT 
      SUBSTRING_INDEX(type_ids, ',', 1) AS customer_type_id,
      SUBSTRING_INDEX(type_ids, ',', -1) AS material_type_id
  FROM type_table;
  

• 提取具体值表的客户/物料具体ID：

  SELECT 
      SUBSTRING_INDEX(value_ids, ',', 1) AS customer_value_id,
      SUBSTRING_INDEX(value_ids, ',', -1) AS material_value_id
  FROM value_table;
  

• 关联查询：

  SELECT 
      t.customer_type_id,
      v.customer_value_id,
      t.material_type_id,
      v.material_value_id
  FROM type_table t
  JOIN value_table v ON t.关联主键 = v.关联主键;
  

SQL Server 实现 #

用STRING_SPLIT结合序号提取：

-- 先拆分类型ID并标记序号
SELECT 
    主表ID,
    value AS type_id,
    ROW_NUMBER() OVER (PARTITION BY 主表ID ORDER BY (SELECT NULL)) AS seq
INTO #type_split
FROM type_table
CROSS APPLY STRING_SPLIT(type_ids, ',');

-- 拆分具体值并标记序号
SELECT 
    主表ID,
    value AS value_id,
    ROW_NUMBER() OVER (PARTITION BY 主表ID ORDER BY (SELECT NULL)) AS seq
INTO #value_split
FROM value_table
CROSS APPLY STRING_SPLIT(value_ids, ',');

-- 关联查询对应类型的ID
SELECT 
    t1.type_id AS customer_type_id,
    v1.value_id AS customer_value_id,
    t2.type_id AS material_type_id,
    v2.value_id AS material_value_id
FROM #type_split t1
JOIN #value_split v1 ON t1.主表ID = v1.主表ID AND t1.seq = 1
JOIN #type_split t2 ON t1.主表ID = t2.主表ID AND t2.seq = 2
JOIN #value_split v2 ON t1.主表ID = v2.主表ID AND v2.seq = 2;

DROP TABLE #type_split;
DROP TABLE #value_split;

注意：这种临时方法完全依赖ID顺序固定，如果后续新增类型或者调整顺序，直接失效，所以能改结构就优先改结构。

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