MySQL自定义comparePrice函数异常：@start变量截断致signal_列错误

现有一张包含id、price、signal_三列的MySQL表，需求如下：

• 以第1行的price作为初始参考价，按id顺序逐行遍历price
• 当前price较参考价上涨≥1时，signal_设为1，并更新参考价为当前price
• 当前price较参考价下跌≥1时，signal_设为-1，并更新参考价为当前price
• 变动幅度<1时，signal_设为0，不更新参考价（signal_初始值均为0）

编写了如下函数及执行语句：

DELIMITER //

CREATE FUNCTION comparePrice(p DECIMAL)
RETURNS INT DETERMINISTIC
BEGIN
DECLARE signalnum INT;
# @start is the first value in row 1 when we start
# if p > @start
IF  p - @start >= 1  THEN SET signalnum := 1, @start := p;
# if @start - p >= 1 p is smaller than @start by 1 or more
ELSEIF @start - p >= 1 THEN SET signalnum := -1, @start := p;
# if the change is less than 1, signalnum = 0
ELSE SET signalnum := 0, @start = p;
END IF;
RETURN signalnum;
END; //

DELIMITER ;

初始化变量并执行更新：

# initialize @start
SELECT @start := price FROM prices_up_down WHERE id = 1;
UPDATE prices_up_down SET signal_ = comparePrice(price);

执行后结果不符合预期，排查发现用户变量@start被异常覆盖（表现为"截断"），导致signal_列生成错误值。

1. UPDATE执行顺序非确定性：MySQL默认不会按照id顺序逐行处理UPDATE语句，而是根据存储引擎的索引或数据存储顺序随机处理行。这会导致@start被不同行的price无序覆盖，完全打乱了"逐行更新参考价"的逻辑，看起来像是变量被截断。
2. 函数逻辑错误：原函数的ELSE分支错误地将@start设为当前price，而需求中只有当涨跌幅度≥1时才需要更新参考价，变动小于1时应保留原参考价，这也会导致参考价被不必要地修改，引发错误。

1. 修正函数逻辑 #

移除ELSE分支中对@start的赋值，仅在触发涨跌条件时更新参考价：

DELIMITER //

CREATE FUNCTION comparePrice(p DECIMAL)
RETURNS INT DETERMINISTIC
BEGIN
DECLARE signalnum INT;
IF p - @start >= 1 THEN 
    SET signalnum := 1;
    SET @start := p;
ELSEIF @start - p >= 1 THEN 
    SET signalnum := -1;
    SET @start := p;
ELSE 
    SET signalnum := 0;
    # 不更新@start，保留原参考价
END IF;
RETURN signalnum;
END; //

DELIMITER ;

2. 强制UPDATE按顺序执行 #

在UPDATE语句中添加ORDER BY id，确保MySQL按照id从小到大的顺序逐行处理，保证参考价的更新逻辑符合预期：

# 初始化参考价
SELECT @start := price FROM prices_up_down WHERE id = 1;
# 按id顺序更新
UPDATE prices_up_down SET signal_ = comparePrice(price) ORDER BY id;

备选方案：使用游标处理（适合复杂顺序依赖场景） #

如果表数据量极大或ORDER BY UPDATE存在性能问题，可以用游标逐行遍历并更新，确保严格的顺序执行：

DELIMITER //

CREATE PROCEDURE updateSignal()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE current_id INT;
    DECLARE current_price DECIMAL;
    DECLARE cur CURSOR FOR SELECT id, price FROM prices_up_down ORDER BY id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    # 初始化参考价
    SELECT price INTO @start FROM prices_up_down WHERE id = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO current_id, current_price;
        IF done THEN
            LEAVE read_loop;
        END IF;

        DECLARE signal_val INT;
        IF current_price - @start >= 1 THEN
            SET signal_val := 1;
            SET @start := current_price;
        ELSEIF @start - current_price >= 1 THEN
            SET signal_val := -1;
            SET @start := current_price;
        ELSE
            SET signal_val := 0;
        END IF;

        UPDATE prices_up_down SET signal_ = signal_val WHERE id = current_id;
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;

# 调用存储过程
CALL updateSignal();

注意：游标逐行更新对于百万级数据来说性能会比ORDER BY UPDATE差，优先使用ORDER BY UPDATE方案。

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