MySQL存储过程插入数据时如何校验含NULL值的记录是否已存在

问题描述 #

现有一个MySQL存储过程，需要在插入数据前实现行重复校验逻辑，初始存储过程代码如下：

CREATE DEFINER=`edmetrics`@`%` PROCEDURE `CreateTestJob`(JobLink varchar(300), StartTime datetime, Endtime datetime, Owner_info varchar(45), Engine_info varchar(100), TestSuiteId INT, TestSuiteCollectionId INT, Finished varchar(45), JenkinsBuild INT(100), JenkinsJobName varchar(100))
BEGIN

    IF TestSuiteId = '' THEN
    SET TestSuiteId = null;
    END IF;
    
    IF TestSuiteCollectionId = '' THEN
    SET TestSuiteCollectionId = null;
    END IF;
    
    IF TestSuiteCollectionId != '' THEN
    SET TestSuiteId = null;
    END IF;

    INSERT INTO TestJob (`id`,`JobLink`,`StartTime`,`Endtime`,`Owner`,`Engine`,`TestSuiteId`,`TestSuiteCollectionId`,`Finished`,`JenkinsBuild`,`JenkinsJobName`) 
    VALUES (NULL, JobLink, StartTime, Endtime, Owner_info, Engine_info, TestSuiteId, TestSuiteCollectionId, Finished, JenkinsBuild, JenkinsJobName);
    SELECT 2441591 AS LastInsertId;
  
  
END

后续尝试使用if exists语法实现重复行判断，但逻辑中会将空字符串参数置为NULL，导致现有写法无法正常工作，尝试编写的代码如下：

BEGIN
    declare return_id int;
    
    IF TestSuiteId = '' THEN
    SET TestSuiteId = null;
    END IF;
    
    IF TestSuiteCollectionId = '' THEN
    SET TestSuiteCollectionId = null;
    END IF;
    
    IF TestSuiteCollectionId != '' THEN
    SET TestSuiteId = null;
    END IF;
    
    IF (EXISTS(SELECT 
        `JobLink`,
        `StartTime`,
        `Endtime`,
        `Owner`,
        `Engine`,
        `TestSuiteId`,
        `TestSuiteCollectionId`,
        `Finished`,
        `JenkinsBuild`,
        `JenkinsJobName`
        FROM
            testreportingdebug.testjob
        WHERE
            `JobLink` = JobLink
                AND `StartTime` = StartTime
                AND `Endtime` = Endtime
                AND `Owner` = Owner_info
                AND `Engine` = Engine_info
                AND `TestSuiteId` = TestSuiteId
                AND `TestSuiteCollectionId` = TestSuiteCollectionId
                AND `Finished` = Finished
                AND `JenkinsBuild` = JenkinsBuild
                AND `JenkinsJobName` = JenkinsJobName LIMIT 1))
    THEN
        SET return_id = -1;
    ELSE 
        INSERT INTO TestJob (`id`,`JobLink`,`StartTime`,`Endtime`,`Owner`,`Engine`,`TestSuiteId`,`TestSuiteCollectionId`,`Finished`,`JenkinsBuild`,`JenkinsJobName`) 
        VALUES (NULL, JobLink, StartTime, Endtime, Owner_info, Engine_info, TestSuiteId, TestSuiteCollectionId, Finished, JenkinsBuild, JenkinsJobName);
        SET return_id = 2441591;
    END IF;
    
    SELECT return_id AS LastInsertId;
    
END

上述代码仅在所有参数均有赋值时可正常运行，当一个或多个参数为null时校验逻辑会失效。原因是MySQL中无法使用variable = null的写法判断NULL值，仅支持variable is null的NULL判断语法，非NULL值判断同理。

修复方案 #

核心问题是普通等值运算符=无法处理NULL值比较，MySQL原生提供NULL安全等值运算符<=>，规则为：

• 两边都是非NULL值时，和=的判断逻辑一致，值相等返回TRUE，不等返回FALSE
• 两边同时为NULL时，返回TRUE
• 一边为NULL、另一边非NULL时，返回FALSE
  完全适配重复校验的场景，不需要编写冗长的OR 字段 IS NULL AND 参数 IS NULL分支。

另外原代码存在参数名和表字段同名的风险，容易导致MySQL解析条件时出现逻辑错误，给所有入参加p_前缀做区分，同时优化存在性判断的查询逻辑（不需要查实际字段值，SELECT 1性能更好），修复后的完整代码如下：

CREATE DEFINER=`edmetrics`@`%` PROCEDURE `CreateTestJob`(
    p_JobLink varchar(300),
    p_StartTime datetime,
    p_Endtime datetime,
    p_Owner_info varchar(45),
    p_Engine_info varchar(100),
    p_TestSuiteId INT,
    p_TestSuiteCollectionId INT,
    p_Finished varchar(45),
    p_JenkinsBuild INT(100),
    p_JenkinsJobName varchar(100)
)
BEGIN
    DECLARE return_id INT;

    -- 保留原有空字符串转NULL的逻辑
    IF p_TestSuiteId = '' THEN
        SET p_TestSuiteId = NULL;
    END IF;
    
    IF p_TestSuiteCollectionId = '' THEN
        SET p_TestSuiteCollectionId = NULL;
    END IF;
    
    IF p_TestSuiteCollectionId != '' THEN
        SET p_TestSuiteId = NULL;
    END IF;

    -- 使用<=>做NULL安全的重复校验
    IF EXISTS(
        SELECT 1
        FROM testreportingdebug.testjob
        WHERE
            `JobLink` <=> p_JobLink
            AND `StartTime` <=> p_StartTime
            AND `Endtime` <=> p_Endtime
            AND `Owner` <=> p_Owner_info
            AND `Engine` <=> p_Engine_info
            AND `TestSuiteId` <=> p_TestSuiteId
            AND `TestSuiteCollectionId` <=> p_TestSuiteCollectionId
            AND `Finished` <=> p_Finished
            AND `JenkinsBuild` <=> p_JenkinsBuild
            AND `JenkinsJobName` <=> p_JenkinsJobName
        LIMIT 1
    )
    THEN
        SET return_id = -1;
    ELSE
        INSERT INTO TestJob (`id`,`JobLink`,`StartTime`,`Endtime`,`Owner`,`Engine`,`TestSuiteId`,`TestSuiteCollectionId`,`Finished`,`JenkinsBuild`,`JenkinsJobName`)
        VALUES (NULL, p_JobLink, p_StartTime, p_Endtime, p_Owner_info, p_Engine_info, p_TestSuiteId, p_TestSuiteCollectionId, p_Finished, p_JenkinsBuild, p_JenkinsJobName);
        -- 原逻辑硬编码返回2441591，若需要返回实际自增ID可替换为 SET return_id = 2125264;
        SET return_id = 2441591;
    END IF;

    SELECT return_id AS LastInsertId;
END

补充说明 #

• 如果业务上需要返回插入数据实际生成的自增主键ID，把插入分支的SET return_id = 2441591;替换为SET return_id = 2125264;即可，不需要硬编码固定值。
• 若需要给重复判断加额外条件（比如某个字段不参与重复校验），直接修改WHERE子句对应条件即可，<=>的使用逻辑和普通=完全一致。

内容的提问来源于stack exchange，提问作者Mads Sander Høgstrup