使用REGEX合并MySQL多查询处理老式零件数据匹配错误问题咨询

问题原因 #

你之前编写的SQL中两个正则匹配条件是独立校验的，只要Models字段包含任意目标序数词、chassisNo字段包含任意目标前缀就会触发匹配，没有绑定序数词和对应基数词的映射关系，因此会出现22ND匹配到23开头的底盘号这类跨组错误。

修正方案 #

因为你的运行场景是低频手动执行，不需要考虑性能，最易维护的方案是先定义所有序数词和对应基数词前缀的映射关系，再关联两张业务表做匹配，代码如下：

INSERT INTO parts_parsed (pageNo, baseGroup, partID, partNo, modelNo) 
SELECT 
    pl.PageNo AS pageNo, 
    pl.BaseGroup AS baseGroup, 
    pl.ID AS partID, 
    pl.PartNo AS partNo, 
    pm.chassisNo AS modelNo 
FROM parts_listing pl
-- 定义所有需要匹配的序数词-基数词前缀映射对
JOIN (
    SELECT '22ND' AS ord_suffix, '22' AS chassis_prefix UNION ALL
    SELECT '23RD' AS ord_suffix, '23' AS chassis_prefix UNION ALL
    SELECT '24TH' AS ord_suffix, '24' AS chassis_prefix UNION ALL
    SELECT '25TH' AS ord_suffix, '25' AS chassis_prefix UNION ALL
    SELECT '26TH' AS ord_suffix, '26' AS chassis_prefix UNION ALL
    SELECT '54TH' AS ord_suffix, '54' AS chassis_prefix
) AS match_map
-- 绑定对应关系：Models包含指定序数词
ON pl.Models LIKE CONCAT('%', match_map.ord_suffix, '%')
JOIN parts_modelno pm
-- 绑定对应关系：chassisNo以对应基数词开头
ON pm.chassisNo LIKE CONCAT(match_map.chassis_prefix, '%')
WHERE pl.BaseGroup NOT IN (SELECT GroupNo FROM parts_reftype WHERE BodyChassis = 1);

方案说明 #

• 后续如果需要新增其他匹配对，只需要在match_map的衍生表中新增对应UNION ALL行即可，不需要修改主逻辑
• 如果你偏好更简单的写法，也可以直接把所有对应组用OR拼接，效果完全一致，只是后续维护需要修改WHERE条件：

INSERT INTO parts_parsed (pageNo, baseGroup, partID, partNo, modelNo) 
SELECT PageNo AS pageNo, BaseGroup AS baseGroup, pl.ID AS partID, PartNo AS partNo, chassisNo AS modelNo 
FROM parts_listing pl, parts_modelno pm 
WHERE 
    (Models LIKE '%22ND%' AND chassisNo LIKE '22%')
    OR (Models LIKE '%23RD%' AND chassisNo LIKE '23%')
    OR (Models LIKE '%24TH%' AND chassisNo LIKE '24%')
    OR (Models LIKE '%25TH%' AND chassisNo LIKE '25%')
    OR (Models LIKE '%26TH%' AND chassisNo LIKE '26%')
    OR (Models LIKE '%54TH%' AND chassisNo LIKE '54%')
AND BaseGroup NOT IN (SELECT GroupNo FROM parts_reftype WHERE BodyChassis = 1);

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