MySQL填充reference列：处理空值与重复值的格式化需求

刚好做过类似的需求，给你整理了MySQL和PostgreSQL两种常用数据库的实现方案，直接就能套用：

核心逻辑梳理 #

先明确我们要处理的三种场景：

• 空值：按「分类名称首字母（大写）+id_category-id_product」生成，比如示例的A26-548
• 重复值：在原reference后追加「-id_product」，比如示例的420GF-2045
• 非重复非空值：保持原reference不变

MySQL 实现 #

1. 查询验证（先看结果再更新） #

用窗口函数快速判断重复值，同时处理空值：

SELECT
    id_category,
    id_product,
    name_category,
    name,
    CASE
        -- 优先处理空值
        WHEN reference IS NULL THEN CONCAT(UPPER(SUBSTRING(name_category, 1, 1)), id_category, '-', id_product)
        -- 判断当前reference是否重复（出现次数>1）
        WHEN COUNT(*) OVER(PARTITION BY reference) > 1 THEN CONCAT(reference, '-', id_product)
        -- 非重复非空值直接保留
        ELSE reference
    END AS updated_reference
FROM your_table_name;

2. 直接更新原表 #

如果确认结果没问题，要把修改同步到原表，可以用下面的UPDATE语句（因为MySQL的UPDATE里不能直接用窗口函数，所以用子查询统计重复次数）：

UPDATE your_table_name
SET reference = CASE
    WHEN reference IS NULL THEN CONCAT(UPPER(SUBSTRING(name_category, 1, 1)), id_category, '-', id_product)
    WHEN (SELECT COUNT(*) FROM your_table_name t2 WHERE t2.reference = your_table_name.reference) > 1 THEN CONCAT(reference, '-', id_product)
    ELSE reference
END;

注意：如果数据量很大，子查询可能效率偏低，建议先把重复的reference存入临时表，再关联更新。

PostgreSQL 实现 #

PostgreSQL的逻辑和MySQL一致，只是字符串截取的语法略有不同，而且支持用CTE更高效地更新：

1. 查询验证 #

SELECT
    id_category,
    id_product,
    name_category,
    name,
    CASE
        WHEN reference IS NULL THEN CONCAT(UPPER(SUBSTRING(name_category FROM 1 FOR 1)), id_category, '-', id_product)
        WHEN COUNT(*) OVER(PARTITION BY reference) > 1 THEN CONCAT(reference, '-', id_product)
        ELSE reference
    END AS updated_reference
FROM your_table_name;

2. 直接更新原表 #

用CTE先计算好所有行的新reference，再关联更新，比子查询更高效：

WITH updated_data AS (
    SELECT
        id_category,
        id_product,
        CASE
            WHEN reference IS NULL THEN CONCAT(UPPER(SUBSTRING(name_category FROM 1 FOR 1)), id_category, '-', id_product)
            WHEN COUNT(*) OVER(PARTITION BY reference) > 1 THEN CONCAT(reference, '-', id_product)
            ELSE reference
        END AS new_reference
    FROM your_table_name
)
UPDATE your_table_name t
SET reference = ud.new_reference
FROM updated_data ud
WHERE t.id_category = ud.id_category AND t.id_product = ud.id_product;

额外注意事项 #

• 确保id_category + id_product是表的唯一键，否则更新时可能出现数据覆盖问题
• 如果存在name_category为空的情况，需要额外处理（比如用id_category的首字母替代），可以在CASE里加一层判断
• 测试时建议先跑查询语句确认结果，没问题再执行更新操作

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