MySQL按匹配条件更新每组首行及扩展多行匹配更新需求

需要基于多列关联条件，用另一张表的主键更新目标表数据。当前问题是：当目标表（docs1）多行匹配源表（docs2）一行时，所有匹配行都会被赋予相同的主键值。希望仅更新每组匹配中的首行（按id升序判定），避免同一主键被多次赋值。

额外扩展需求：当源表多行匹配目标表多行时，按id升序将源表的前N行主键对应更新目标表的前N行，避免同一主键重复赋值（例如目标表4行匹配源表3行，则仅更新前3行）。

要求方案高效（20秒内处理各20万条数据的表），尽量避免复杂的ROW_NUMBER()/PARTITION语法，若单语句无法实现可接受存储过程方案。

测试用例代码 #

DROP TEMPORARY TABLE IF EXISTS docs1;
DROP TEMPORARY TABLE IF EXISTS docs2;
CREATE TEMPORARY TABLE docs1 (
    id int PRIMARY KEY,
    other_table_id int,
    doc_no VARCHAR(40)
);

CREATE TEMPORARY TABLE docs2 (
    id int PRIMARY KEY,
    doc_no VARCHAR(40)
);

INSERT INTO docs1 (id, doc_no) VALUES (150, '001/2324'), (157, '01-2324'), (165, 'I/101'), (123, 'I-101');

INSERT INTO docs2 (id, doc_no) VALUES (11, '1/2324'), (37, 'I/101');

CREATE FUNCTION `RemoveSpecialCharacters` (doc_no VARCHAR(50))
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    RETURN TRIM(LEADING '0' FROM REPLACE(REPLACE(doc_no, '/', ''), '-', ''));
END

SET SQL_SAFE_UPDATES = 0;

-- 当前有问题的更新语句：会把所有匹配行都赋值为同一主键
UPDATE docs1 T0 
    INNER JOIN docs2 T1 
    ON RemoveSpecialCharacters(T0.doc_no) = RemoveSpecialCharacters(T1.doc_no)
    -- 实际场景包含更多关联列及WHERE子句
    SET T0.other_table_id = T1.id;

期望输出 #

id, other_table_id, doc_no
150, 11, 001/2324
157, NULL, 01-2324
165, NULL, I/101
123, 37, I-101

解决方案 #

基础需求：仅更新目标表每组匹配的首行 #

方案1：单语句高效更新（利用关联子查询锁定首行）

核心思路：通过子查询找到每个匹配组中目标表的最小id（即首行），仅更新这些行。这种方式避免窗口函数，性能更优，适合大数据量。

UPDATE docs1 T0
JOIN docs2 T1 
    ON RemoveSpecialCharacters(T0.doc_no) = RemoveSpecialCharacters(T1.doc_no)
    -- 替换为实际的5-6列关联条件及WHERE子句
WHERE T0.id = (
    SELECT MIN(id) 
    FROM docs1 
    WHERE RemoveSpecialCharacters(doc_no) = RemoveSpecialCharacters(T1.doc_no)
    -- 同步T0和T1的关联条件
)
SET T0.other_table_id = T1.id;

性能优化建议：

• 为docs1和docs2的关联列（处理后的doc_no相关）创建函数索引（MySQL 8.0.13+支持）：

  CREATE INDEX idx_docs1_clean_docno ON docs1 ((RemoveSpecialCharacters(doc_no)));
  CREATE INDEX idx_docs2_clean_docno ON docs2 ((RemoveSpecialCharacters(doc_no)));
  

• 若不支持函数索引，可提前计算并存储处理后的doc_no到新列，再为新列创建普通索引，避免每次查询重复执行函数。

方案2：存储过程循环（适合复杂场景）

如果单语句无法满足复杂关联逻辑，可通过存储过程循环处理每个匹配组：

DELIMITER //
CREATE PROCEDURE UpdateOnlyFirstMatch()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE t1_id INT;
    DECLARE clean_doc VARCHAR(50);
    -- 游标遍历docs2的所有匹配项
    DECLARE cur CURSOR FOR 
        SELECT id, RemoveSpecialCharacters(doc_no) 
        FROM docs2;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO t1_id, clean_doc;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 更新docs1中该组的最小id行
        UPDATE docs1
        SET other_table_id = t1_id
        WHERE id = (
            SELECT MIN(id) 
            FROM docs1 
            WHERE RemoveSpecialCharacters(doc_no) = clean_doc
            -- 加入实际的WHERE子句
        ) AND other_table_id IS NULL; -- 避免重复更新
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- 调用存储过程
CALL UpdateOnlyFirstMatch();

扩展需求：双向匹配时一对一更新前N行 #

当源表和目标表都有多行匹配时，需要按id升序一对一匹配更新，避免主键重复赋值。可利用用户变量实现无窗口函数的高效匹配：

-- 先为匹配对生成序号，再仅更新序号相同的行
UPDATE docs1 T0
JOIN (
    SELECT 
        t0.id AS t0_id,
        t1.id AS t1_id,
        @row_num := IF(@current_group = clean_doc, @row_num + 1, 1) AS row_num,
        @current_group := clean_doc
    FROM (
        SELECT id, RemoveSpecialCharacters(doc_no) AS clean_doc
        FROM docs1
        -- 加入实际WHERE子句
        ORDER BY id ASC
    ) T0
    JOIN (
        SELECT id, RemoveSpecialCharacters(doc_no) AS clean_doc
        FROM docs2
        -- 加入实际WHERE子句
        ORDER BY id ASC
    ) T1 ON T0.clean_doc = T1.clean_doc
    CROSS JOIN (SELECT @row_num := 0, @current_group := '') AS vars
) AS matched ON T0.id = matched.t0_id
JOIN docs2 T1 ON T1.id = matched.t1_id
SET T0.other_table_id = T1.id;

说明：子查询会为每个匹配组内的行按id升序生成连续序号，只有序号相同的行才会被匹配更新，实现一对一对应关系。同样建议为处理后的doc_no创建索引提升性能。

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