MySQL中游标无结果返回与行结束的区分实现问询

要区分游标遍历结束和SELECT INTO无结果这两种NOT FOUND场景，核心是避免游标终止变量被SELECT INTO的无结果错误意外修改，可通过以下两种方案实现：

方案一：为SELECT INTO单独设置局部错误处理变量 #

新增局部变量专门捕获SELECT INTO的无结果错误，执行后判断该变量，不直接使用游标终止的FINISHED变量。修改后的完整存储过程代码如下：

DROP PROCEDURE IF EXISTS CURSOR_PLACEHOLDER;
DELIMITER $$
CREATE PROCEDURE CURSOR_PLACEHOLDER()
BEGIN
DECLARE FINISHED INTEGER DEFAULT 0;
DECLARE CURRENT_ROW_ID VARCHAR(256);
DECLARE SELECT_NO_DATA INTEGER DEFAULT 0; -- 新增变量，专门捕获SELECT INTO无结果

DEClARE CURRENT_ROW
    CURSOR FOR
    SELECT ID
    FROM A
    WHERE ID_P IN (SELECT ID_P
                      FROM A
                      GROUP BY ID_P
                      HAVING COUNT(ID_P) > 1);

-- 仅处理游标遍历结束的NOT FOUND
DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 1;

OPEN CURRENT_ROW;

GET_ACTION:
LOOP
    FETCH CURRENT_ROW INTO CURRENT_ROW_ID;

    IF FINISHED = 1 THEN
        LEAVE GET_ACTION;
    END IF;

    SET @CURRENT_P_ID := (SELECT ID_P FROM A WHERE ID = CURRENT_ROW_ID);

    -- 重置SELECT INTO的错误标记
    SET SELECT_NO_DATA = 0;
    -- 为当前SELECT INTO临时设置错误处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET SELECT_NO_DATA = 1;
    
    SELECT a.ID_U, a.ID_R, a.A, a.FROMDATE, a.TODATE 
    INTO @ID_U, @ID_R, @A, @FROM_DATE, @TO_DATE
    FROM ASSIG a
    WHERE a.ID_G = @CURRENT_P_ID;

    -- 恢复游标原有的错误处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = 1;

    -- 仅当SELECT有结果时执行插入
    IF SELECT_NO_DATA = 0 THEN
        SET @ASSIG_ID := GENERATE_ID();
        INSERT INTO ASSIG
        VALUES (@ASSIG_ID,
                @G_ID,
                @ID_U,
                @ID_R,
                @A,
                @FROM_DATE,
                @TO_DATE);
    END IF;

    UPDATE A
    SET ID_P = @G_ID
    WHERE ID = CURRENT_ROW_ID;

END LOOP GET_ACTION;
CLOSE CURRENT_ROW;

END
$$
DELIMITER ;

方案二：先检查数据存在性，再执行SELECT INTO #

在执行SELECT INTO前，先用COUNT判断是否有匹配数据，仅当数据存在时才执行赋值操作，从根源避免触发NOT FOUND错误。关键代码片段修改如下：

SET @CURRENT_P_ID := (SELECT ID_P FROM A WHERE ID = CURRENT_ROW_ID);

-- 先检查ASSIG表是否存在匹配数据
SET @ASSIG_COUNT = (SELECT COUNT(*) FROM ASSIG a WHERE a.ID_G = @CURRENT_P_ID);
IF @ASSIG_COUNT > 0 THEN
    SELECT a.ID_U, a.ID_R, a.A, a.FROMDATE, a.TODATE 
    INTO @ID_U, @ID_R, @A, @FROM_DATE, @TO_DATE
    FROM ASSIG a
    WHERE a.ID_G = @CURRENT_P_ID;

    SET @ASSIG_ID := GENERATE_ID();
    INSERT INTO ASSIG
    VALUES (@ASSIG_ID,
            @G_ID,
            @ID_U,
            @ID_R,
            @A,
            @FROM_DATE,
            @TO_DATE);
END IF;

方案说明 #

• 方案一通过局部错误变量+临时HANDLER隔离了游标与SELECT INTO的错误处理，适合需要直接获取查询结果的场景；
• 方案二更简洁，通过预检查避免触发错误，适合只需判断数据存在性再执行后续操作的场景；
• MySQL中HANDLER的作用域为当前BEGIN...END块，循环内声明的临时HANDLER仅作用于当前迭代，不会影响全局的游标HANDLER。

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