SQL多地点库存DB paint_inventory分隔字段存储方案咨询

已知需要保留非范式的逗号分隔存储结构，不需要重构为标准关联表，直接按以下逻辑实现转换即可：核心流程为「拆分原始颜色字符串→关联基础表映射油漆ID→按门店分组重新拼接为目标ID字符串」，主流数据库的可直接落地代码如下：

前置准备 #

以下为和业务规则对齐的示例表定义与测试数据，可直接参考：

-- 门店位置表
CREATE TABLE store_location (
  Location_ID INT PRIMARY KEY,
  address VARCHAR(255),
  paint_inventory TEXT
);

-- 油漆基础映射表
CREATE TABLE paint_base (
  paint_ID INT PRIMARY KEY,
  color_name VARCHAR(50) UNIQUE NOT NULL
);

-- 灌入示例原始数据
INSERT INTO store_location VALUES
(1001, '门店地址1', 'red,blue,black'),
(1002, '门店地址2', 'blue,orange');

INSERT INTO paint_base VALUES
(1,'red'),(2,'blue'),(3,'purple'),(4,'black'),(5,'orange');

不同数据库的转换更新语句 #

执行对应语句后，store_location表的paint_inventory字段会直接更新为目标格式：Location_ID=1001对应1,2,4，Location_ID=1002对应2,5。

MySQL 8.0+ #

WITH RECURSIVE color_split AS (
    SELECT
        Location_ID,
        SUBSTRING_INDEX(paint_inventory, ',', 1) AS color,
        SUBSTRING(paint_inventory, LENGTH(SUBSTRING_INDEX(paint_inventory, ',', 1)) + 2) AS rest
    FROM store_location
    UNION ALL
    SELECT
        Location_ID,
        SUBSTRING_INDEX(rest, ',', 1) AS color,
        SUBSTRING(rest, LENGTH(SUBSTRING_INDEX(rest, ',', 1)) + 2) AS rest
    FROM color_split
    WHERE rest <> ''
)
UPDATE store_location s
JOIN (
    SELECT
        cs.Location_ID,
        GROUP_CONCAT(pb.paint_ID ORDER BY pb.paint_ID SEPARATOR ',') AS id_inventory
    FROM color_split cs
    INNER JOIN paint_base pb ON cs.color = pb.color_name
    GROUP BY cs.Location_ID
) map ON s.Location_ID = map.Location_ID
SET s.paint_inventory = map.id_inventory;

如果使用MySQL 5.x版本，不支持递归CTE，可以提前建一张存储1~100连续整数的辅助序号表，通过序号定位逗号位置完成字符串拆分，核心映射和聚合逻辑和上述语句一致。

PostgreSQL #

UPDATE store_location s
SET paint_inventory = map.id_inventory
FROM (
    SELECT
        sl.Location_ID,
        STRING_AGG(pb.paint_ID::TEXT, ',' ORDER BY pb.paint_ID) AS id_inventory
    FROM store_location sl,
    LATERAL string_to_table(sl.paint_inventory, ',') AS c(color)
    INNER JOIN paint_base pb ON c.color = pb.color_name
    GROUP BY sl.Location_ID
) map
WHERE s.Location_ID = map.Location_ID;

SQL Server #

WITH color_split AS (
    SELECT
        Location_ID,
        value AS color
    FROM store_location
    CROSS APPLY STRING_SPLIT(paint_inventory, ',')
)
UPDATE s
SET paint_inventory = map.id_inventory
FROM store_location s
JOIN (
    SELECT
        cs.Location_ID,
        STRING_AGG(pb.paint_ID, ',') WITHIN GROUP (ORDER BY pb.paint_ID) AS id_inventory
    FROM color_split cs
    INNER JOIN paint_base pb ON cs.color = pb.color_name
    GROUP BY cs.Location_ID
) map ON s.Location_ID = map.Location_ID;

注意事项 #

• 执行更新操作前务必备份原始paint_inventory字段数据，避免转换异常导致数据丢失
• 如果原始库存字符串中存在paint_base表未收录的颜色值，上述语句会自动过滤无效颜色；如果需要保留异常值标记，可以将INNER JOIN改为LEFT JOIN，对NULL的paint_ID做自定义处理
• 后续业务写入新的库存数据时，建议在应用层提前完成颜色名到paint_ID的映射转换，直接写入ID拼接的字符串，减少数据库侧的拆分计算开销

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