MariaDB中Join查询Select与Update结果不一致问题求助

可能的原因及排查步骤 #

1. 关联查询存在一对多匹配 #

当temp_20240323_01_tallypl的一行记录在v_price_list_complete中匹配到多行时，MariaDB会随机选择其中一行（通常是最后匹配到的）进行更新，导致最终值不符合预期。

验证方法：执行以下查询，查看零件572052匹配到的所有v表记录：

SELECT 
    t.pm_id, 
    t.tc_pricelevels, 
    v.pricelist, 
    v.store_id, 
    v.price_level_name
FROM temp_20240323_01_tallypl t
INNER JOIN v_price_list_complete v 
    ON t.oms_aplid = v.price_list_id 
    AND t.tc_pricelevels = v.price_level_name 
    AND t.pm_id = v.part_id
WHERE 
    v.store_id = p_store_id
    AND t.pm_id = '572052'
    AND t.oms_aplid IS NOT NULL;

如果结果中存在pricelist=17777的记录，说明UPDATE时匹配到了该行而非你预期的1975/2794的行。

2. 隐式类型转换导致意外匹配 #

检查关联字段的类型是否一致：

• t.oms_aplid和v.price_list_id
• t.tc_pricelevels和v.price_level_name
• t.pm_id和v.part_id

若类型不匹配（比如一个是字符串、一个是数字），MariaDB会进行隐式转换，可能导致非预期的匹配（例如'0572052'和572052被判定为相等）。

3. 变量p_store_id值不一致 #

确认执行SELECT和UPDATE时，会话变量p_store_id的值是否完全相同。如果变量在两次执行之间被修改，会导致匹配到不同门店的价格数据。

解决方法 #

方法1：严格过滤匹配条件 #

在UPDATE的关联或WHERE子句中添加更精确的过滤，确保只匹配到预期的价格等级记录：

UPDATE temp_20240323_01_tallypl t
INNER JOIN v_price_list_complete v 
    ON t.oms_aplid = v.price_list_id 
    AND t.tc_pricelevels = v.price_level_name 
    AND t.pm_id = v.part_id
SET 
    t.discount_var = v.discount_var,
    t.oms_pricelist = v.pricelist,
    t.o_pricelist = 2000,
    t.oms_discount = v.discount_value,
    t.oms_rate = (v.pricelist * ((100 - v.discount_value)/ 100))
WHERE 
    v.store_id = p_store_id
    AND t.pm_id = '572052'
    AND t.oms_aplid IS NOT NULL
    AND t.tc_pricelevels IN ('Cost Price', 'Overseas'); -- 明确指定目标价格等级

方法2：使用子查询确保单一匹配 #

通过子查询预先筛选出目标记录，避免一对多匹配的问题：

UPDATE temp_20240323_01_tallypl t
SET 
    t.discount_var = (
        SELECT v.discount_var 
        FROM v_price_list_complete v 
        WHERE t.oms_aplid = v.price_list_id 
          AND t.tc_pricelevels = v.price_level_name 
          AND t.pm_id = v.part_id 
          AND v.store_id = p_store_id
          AND v.price_level_name IN ('Cost Price', 'Overseas')
        LIMIT 1
    ),
    t.oms_pricelist = (
        SELECT v.pricelist 
        FROM v_price_list_complete v 
        WHERE t.oms_aplid = v.price_list_id 
          AND t.tc_pricelevels = v.price_level_name 
          AND t.pm_id = v.part_id 
          AND v.store_id = p_store_id
          AND v.price_level_name IN ('Cost Price', 'Overseas')
        LIMIT 1
    ),
    t.o_pricelist = 2000,
    t.oms_discount = (
        SELECT v.discount_value 
        FROM v_price_list_complete v 
        WHERE t.oms_aplid = v.price_list_id 
          AND t.tc_pricelevels = v.price_level_name 
          AND t.pm_id = v.part_id 
          AND v.store_id = p_store_id
          AND v.price_level_name IN ('Cost Price', 'Overseas')
        LIMIT 1
    ),
    t.oms_rate = (
        SELECT (v.pricelist * ((100 - v.discount_value)/ 100)) 
        FROM v_price_list_complete v 
        WHERE t.oms_aplid = v.price_list_id 
          AND t.tc_pricelevels = v.price_level_name 
          AND t.pm_id = v.part_id 
          AND v.store_id = p_store_id
          AND v.price_level_name IN ('Cost Price', 'Overseas')
        LIMIT 1
    )
WHERE 
    t.pm_id = '572052'
    AND t.oms_aplid IS NOT NULL;

注意事项 #

执行UPDATE前，务必先用对应的SELECT语句验证匹配的记录是否正确，避免误更新大量数据。

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