MySQL：如何基于行序列及匹配条件关联另一表更新数据？

环境搭建代码 #

DROP TEMPORARY TABLE IF EXISTS temp_table1;
DROP TEMPORARY TABLE IF EXISTS temp_table2;

CREATE TEMPORARY TABLE temp_table1 (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `doc_no` varchar(25) NOT NULL,
  `other_table_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

CREATE TEMPORARY TABLE temp_table2 (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `doc_no` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

INSERT INTO temp_table1 (doc_no)
VALUES ('100/1/23-24'), ('100-1-2324');

INSERT INTO temp_table2 (id, doc_no)
VALUES (101, '10012324'), (102, '100/1/2324'), (103, '100/1-23/24');


SET SQL_SAFE_UPDATES = 0;

需求说明 #

• 两张表的doc_no值相似但含特殊字符，需先移除特殊字符后匹配
• 将temp_table2的id更新到temp_table1的other_table_id字段
• 匹配规则：按行序列对应匹配（temp_table1第1行对应temp_table2第1行，第2行对应第2行），仅在清理后doc_no匹配时执行更新
• temp_table2中多余的行（第3行）不参与更新

当前执行语句及问题 #

执行以下更新语句：

UPDATE temp_table1 T0
INNER JOIN temp_table2 T1
ON RemoveSpecialCharacters(T0.doc_no) = RemoveSpecialCharacters(T1.doc_no)
SET T0.other_table_id = T1.id;

得到错误结果：

id  doc_no      other_table_id
1   100/1/23-24 101
2   100-1-2324  101

问题：当清理后doc_no存在多对多匹配时，所有匹配行都被更新为同一个temp_table2.id，未按行序列对应匹配。

预期结果 #

id  doc_no      other_table_id
1   100/1/23-24 101
2   100-1-2324  102

核心思路是给两张表分别添加行序列编号，同时基于「清理后doc_no匹配」和「行序列编号相等」两个条件关联更新。

步骤1：定义移除特殊字符的函数（若未定义） #

如果MySQL中没有RemoveSpecialCharacters函数，先创建：

DELIMITER //
CREATE FUNCTION RemoveSpecialCharacters(str VARCHAR(255)) 
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
  RETURN REGEXP_REPLACE(str, '[^0-9a-zA-Z]', '');
END //
DELIMITER ;

步骤2：带行序列的关联更新语句 #

使用窗口函数ROW_NUMBER()生成行号，再执行更新：

UPDATE temp_table1 t0
JOIN (
    SELECT 
        id, 
        doc_no,
        ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM temp_table1
) tt0 ON t0.id = tt0.id
JOIN (
    SELECT 
        id, 
        doc_no,
        ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM temp_table2
) tt1 ON tt0.row_num = tt1.row_num
    AND RemoveSpecialCharacters(tt0.doc_no) = RemoveSpecialCharacters(tt1.doc_no)
SET t0.other_table_id = tt1.id;

原理说明 #

1. 子查询tt0和tt1分别为两张表按自身id顺序生成行号row_num，确保行序列对应
2. 关联条件同时满足行号相等、清理后doc_no匹配，保证只有对应行且内容匹配时才更新
3. temp_table2行号超过temp_table1的部分（第3行）无对应行，不会参与更新

执行后即可得到预期结果。

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