MySQL WHILE循环插入存储过程出现更新失效、主键重复报错问题

问题根源 #

你遇到的所有异常都来自同一个核心问题：存储过程的输入参数名和test_table的字段名完全重名，MySQL在解析SQL时错误地将字段名匹配为输入参数，导致逻辑完全偏离预期。

对应现象解析 #

1. 第一次调用能正常运行：
   此时test_table是空表，哪怕NOT EXISTS的判断逻辑已经出错，空表的SELECT查询永远返回空，所以NOT EXISTS判定为true，成功插入test1。UPDATE时刚好匹配到刚插入的记录，赋值刚好命中参数值，所以返回结果符合预期。

2. 第二次调用val1未更新、无返回：
   你的UPDATE test_table SET val1 = val1 WHERE token = tokenUsed语句中，val1同时是输入参数名和表字段名，MySQL优先匹配表字段，相当于执行了SET val1 = 自身原值，所以更新完全不生效，val1保留NULL。
   同时此时NOT EXISTS逻辑已经出错，但刚好tmpTok对应的条件判断返回空，所以插入了test2。

3. 第三次调用报主键重复错误：
   你的存在性判断语句IF NOT EXISTS(SELECT 1 FROM test_table WHERE token = tmpTok)中，token同时是第一个输入参数名和表字段名，MySQL优先解析为输入参数token（也就是你每次调用传入的第一个值test1），因此存在性判断的逻辑变成了判断是否存在 入参token=tmpTok 的记录，而不是判断表中是否存在token等于tmpTok的记录。
   第三次调用时trial走到1，tmpTok为test2，此时判断条件变成入参test1 = tmpTok test2，结果为false，所以NOT EXISTS永远返回true，哪怕表中已经存在test2，还是会执行插入，自然报主键重复错误。

修复方案 #

最简单的修复方式是给所有输入参数添加统一前缀，避免和表字段重名，同时查询时给表加别名明确限定字段来源，彻底消除歧义。参考修改后的存储过程：

DELIMITER $$
CREATE PROCEDURE sp_test(
    IN p_token VARCHAR(16),
    IN p_token1 VARCHAR(16),
    IN p_token2 VARCHAR(16),
    IN p_token3 VARCHAR(16),
    IN p_token4 VARCHAR(16),
    IN p_val1 VARCHAR(256)
)
BEGIN
    DECLARE tokenUsed VARCHAR(16);
    DECLARE trial INT DEFAULT 0;
    DECLARE tmpTok VARCHAR(16);
    
    WHILE (tokenUsed IS NULL AND trial < 5)
    DO
        SET tmpTok = CASE trial 
            WHEN 0 THEN p_token 
            WHEN 1 THEN p_token1 
            WHEN 2 THEN p_token2 
            WHEN 3 THEN p_token3 
            WHEN 4 THEN p_token4 
        END;
        
        IF NOT EXISTS(SELECT 1 FROM test_table t WHERE t.token = tmpTok)
        THEN
            SET tokenUsed = tmpTok;
            INSERT INTO test_table (token) VALUES(tmpTok);
        END IF;
        
        SET trial = trial + 1;
    END WHILE;
    
    IF tokenUsed IS NOT NULL
    THEN
        UPDATE test_table t SET t.val1 = p_val1 WHERE t.token = tokenUsed;
    END IF;
    
    SELECT * FROM test_table t WHERE t.token = tokenUsed;
END$$
DELIMITER ;

如果要应对高并发调用场景，还可以把INSERT语句改成INSERT IGNORE INTO test_table (token) VALUES(tmpTok)，结合插入影响行数判断插入结果，避免并发判断导致的主键重复问题。

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