MySQL触发器调用存储过程后searchAddressId返回NULL的问题排查

在MySQL 8.0数据库中，我创建了一个BEFORE INSERT触发器，用于检查插入数据的status字段：当值为fresh时，调用存储过程计算searchAddressId的值并赋值给新记录的对应字段。存储过程调用无报错，但插入后searchAddressId字段值为NULL，而非预期的1。

触发器代码 #

CREATE DEFINER=`user`@`` TRIGGER `data_BEFORE_INSERT` BEFORE INSERT ON `data` FOR EACH ROW BEGIN
DECLARE newSearchAddressId INTEGER;

IF NEW.status = 'fresh' THEN
    CALL CALC_FRESH_SEARCH_ADDRESS_ID(NEW.lead, @newSearchAddressId);
    SET NEW.searchAddressId = @newSearchAddressId;
END IF;
END

存储过程代码 #

CREATE DEFINER=`user`@`` PROCEDURE `CALC_FRESH_SEARCH_ADDRESS_ID`(
    IN leadId INT,
    OUT newSearchAddressId INT
)
proc_label: BEGIN
    DECLARE lastSearchAddressID INT;
    DECLARE lastStatus VARCHAR(100);
    
    SELECT
        `searchAddressId`,
        `status`
    INTO
        lastSearchAddressID,
        lastStatus
    FROM `leads`
    WHERE `id` = leadId;
    
    -- 无历史记录则为首次录入
    IF lastStatus IS NULL THEN
        SET newSearchAddressId = 1;
        LEAVE proc_label;
    END IF;
END

样本数据与结果 #

样本数据表创建与插入语句 #

DROP TABLE IF EXISTS `data`;
CREATE TABLE `data` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `lead` INT NOT NULL,
    `status` VARCHAR(100) NOT NULL,
    `searchAddressId` INT NOT NULL
) ENGINE=INNODB;
INSERT INTO `data` (`lead`, `status`, `searchAddressId`)
VALUES(1, 'fresh',     1),
(2, 'suspended', 1),
(3, 'stale',     1),
(4, 'fresh',     1),
(5, 'cancelled', 1);

预期查询结果 #

实际查询结果 #

问题根源 #

1. 存储过程逻辑漏洞：
   • 当leads表中存在leadId对应的记录时，lastStatus不为NULL，存储过程未执行任何赋值逻辑，导致OUT参数newSearchAddressId保持默认NULL值。
   • 当leads表中不存在leadId对应的记录时，SELECT INTO语句会抛出“无数据”错误，存储过程直接终止，无法执行后续的IF判断，同样导致OUT参数未被赋值。
2. 触发器变量混淆：
   触发器中声明了局部变量newSearchAddressId，但调用存储过程时使用的是会话变量@newSearchAddressId，二者完全独立，虽不是本次NULL问题的直接原因，但会导致代码逻辑混乱。

修复方案 #

方案1：完善存储过程逻辑，处理所有分支并捕获异常 #

修改存储过程，增加无数据异常处理，并确保所有分支都给OUT参数赋值：

CREATE DEFINER=`user`@`` PROCEDURE `CALC_FRESH_SEARCH_ADDRESS_ID`(
    IN leadId INT,
    OUT newSearchAddressId INT
)
proc_label: BEGIN
    DECLARE lastSearchAddressID INT;
    DECLARE lastStatus VARCHAR(100);
    -- 捕获无数据异常，将lastStatus设为NULL
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET lastStatus = NULL;
    
    SELECT
        `searchAddressId`,
        `status`
    INTO
        lastSearchAddressID,
        lastStatus
    FROM `leads`
    WHERE `id` = leadId;
    
    -- 无历史记录或查询无数据时设为1，否则沿用历史值
    IF lastStatus IS NULL THEN
        SET newSearchAddressId = 1;
    ELSE
        SET newSearchAddressId = lastSearchAddressID;
    END IF;
END

方案2：修正触发器变量使用（优化可读性） #

将触发器中的会话变量改为局部变量，避免混淆：

CREATE DEFINER=`user`@`` TRIGGER `data_BEFORE_INSERT` BEFORE INSERT ON `data` FOR EACH ROW BEGIN
DECLARE newSearchAddressId INTEGER;

IF NEW.status = 'fresh' THEN
    CALL CALC_FRESH_SEARCH_ADDRESS_ID(NEW.lead, newSearchAddressId);
    SET NEW.searchAddressId = newSearchAddressId;
END IF;
END

验证 #

执行插入语句：

INSERT INTO `data` (`lead`, `status`) VALUES(6, 'fresh');

查询data表，即可看到searchAddressId字段值为预期的1。

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