pricing表重复值过滤、配对校验及修正存储过程实现问询

背景 #

我们通过插件从FTP服务器导入XML文件至数据库，处理后将数据插入pricing表（数据量万级以上）用于价格配置。导入过程中易产生重复值，系统默认仅取首个匹配项进行关联。表核心字段：

• ID：记录唯一标识
• SITE：站点标识
• LINKED：关联格式为PK!ID，用于关联同表内的其他记录

需求 #

1. 筛选两类异常记录：
   • 无有效配对目标的记录（即LINKED指向的ID不存在）
   • 同一站点下重复关联同一目标ID的记录
2. 创建存储过程，将LINKED字段修正为符合规则的关联ID

当前问题 #

已通过子查询定位异常记录，但无法实现正确的配对关联逻辑

1. 异常记录筛选SQL #

以下SQL可一次性筛选出两类异常记录：

-- 筛选无有效配对的记录
SELECT p.*
FROM pricing p
LEFT JOIN pricing p_target
  ON p.LINKED = CONCAT('PK!', p_target.ID)
WHERE p_target.ID IS NULL

UNION ALL

-- 筛选同一站点下重复关联同一目标ID的记录
SELECT p.*
FROM pricing p
INNER JOIN (
    SELECT 
        SUBSTRING(LINKED, 4) AS target_id,
        SITE,
        COUNT(*) AS关联次数
    FROM pricing
    WHERE LINKED LIKE 'PK!%'
    GROUP BY SUBSTRING(LINKED, 4), SITE
    HAVING COUNT(*) > 1
) dup_records
  ON SUBSTRING(p.LINKED, 4) = dup_records.target_id
  AND p.SITE = dup_records.SITE

2. 修正LINKED字段的存储过程 #

假设业务规则为：同一站点下，每个待关联记录需关联唯一且存在的目标ID；重复关联的记录重新分配可用的目标ID，无配对的记录匹配同站点下的有效目标。以下存储过程实现该逻辑：

DELIMITER //

CREATE PROCEDURE FixPricingLinkedAssociations()
BEGIN
    -- 临时标记异常记录
    ALTER TABLE pricing ADD COLUMN temp_is_abnormal TINYINT(1) DEFAULT 0;

    -- 标记无有效配对的记录
    UPDATE pricing p
    LEFT JOIN pricing p_target
      ON p.LINKED = CONCAT('PK!', p_target.ID)
    SET p.temp_is_abnormal = 1
    WHERE p_target.ID IS NULL;

    -- 标记重复关联的记录
    UPDATE pricing p
    INNER JOIN (
        SELECT 
            SUBSTRING(LINKED, 4) AS target_id,
            SITE,
            COUNT(*) AS关联次数
        FROM pricing
        WHERE LINKED LIKE 'PK!%'
        GROUP BY SUBSTRING(LINKED, 4), SITE
        HAVING COUNT(*) > 1
    ) dup_records
      ON SUBSTRING(p.LINKED, 4) = dup_records.target_id
      AND p.SITE = dup_records.SITE
    SET p.temp_is_abnormal = 1;

    -- 获取各站点可用的目标记录（假设LINKED为空或非PK!格式的为可关联目标）
    WITH AvailableTargets AS (
        SELECT 
            ID,
            SITE,
            ROW_NUMBER() OVER (PARTITION BY SITE ORDER BY ID) AS target_seq
        FROM pricing
        WHERE LINKED IS NULL OR LINKED NOT LIKE 'PK!%'
    ),
    AbnormalRecords AS (
        SELECT 
            p.ID,
            p.SITE,
            ROW_NUMBER() OVER (PARTITION BY p.SITE ORDER BY p.ID) AS abnormal_seq
        FROM pricing p
        WHERE p.temp_is_abnormal = 1
    )
    -- 为异常记录分配匹配的目标ID
    UPDATE pricing p
    INNER JOIN AbnormalRecords ar ON p.ID = ar.ID
    INNER JOIN AvailableTargets at 
      ON ar.SITE = at.SITE 
      AND ar.abnormal_seq = at.target_seq
    SET p.LINKED = CONCAT('PK!', at.ID), p.temp_is_abnormal = 0;

    -- 清理临时字段
    ALTER TABLE pricing DROP COLUMN temp_is_abnormal;
END //

DELIMITER ;

注意事项 #

• 执行前务必备份pricing表数据，避免数据丢失
• 需根据实际业务调整AvailableTargets中的目标记录筛选逻辑（比如哪些记录可作为关联目标）
• 针对万级数据，可考虑拆分存储过程为分批处理，减少锁表时间

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