mysql - 为什么游标返回 null INTO 变量但查询返回行？

Question

我在 MySQL 中有这个存储过程，它应该可以工作，但没有。我放了一个名为 logs 的表来记录值，以查看那里发生了什么:

DELETE FROM LOGS;
CALL fixRegistrationsGroups;
SELECT * FROM LOGS;

这会在日志表中返回:

BEFORE LOOP registrationsIds NULL
registrationsIds NULL
nextRegistrationId NULL

正如我在这个问题的标题中所说，游标 registrationsWithSameGroupId 中的查询返回行，但当我从游标中获取时却没有，这是它返回的行的示例:

countRepeated   registrationsIds    groupId
2               11,1017             6
4               33,35,3463,4363     7
2               32,54               10
7               10,39,40,41,47,48,  51  

存储过程(有什么问题？):

DELIMITER $$

USE `database-name`$$

DROP PROCEDURE IF EXISTS `fixRegistrationsGroups`$$

CREATE DEFINER=`root`@`%` PROCEDURE `fixRegistrationsGroups`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE participantRegistration INT(11);
    DECLARE newAgreementSignatureId INT(11);
    DECLARE countRepeated INT;
    DECLARE registrationsIds VARCHAR(255);
    DECLARE currentGroupId INT;
    DECLARE nextRegistrationId INT(11);
    DECLARE strLen    INT DEFAULT 0;
    DECLARE SubStrLen INT DEFAULT 0;


    DECLARE registrationsWithSameGroupId CURSOR FOR 
        SELECT COUNT(groupId) AS countRepeated, 
                GROUP_CONCAT( registrations.id )  AS registrationsIds, 
                groupId 
            FROM registrations 
            GROUP BY groupId 
            HAVING countRepeated > 1;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN registrationsWithSameGroupId;
    READ_LOOP: LOOP
        FETCH NEXT FROM registrationsWithSameGroupId INTO countRepeated, registrationsIds, currentGroupId;      
        INSERT INTO LOGS(line) VALUES (CONCAT('BEFORE LOOP registrationsIds ',IF (registrationsIds IS NULL,'NULL',registrationsIds)) );
        createAndAssignGroups: LOOP             
            SET strLen = LENGTH(registrationsIds);      
            INSERT INTO LOGS(line) VALUES (CONCAT('registrationsIds ',IF (registrationsIds IS NULL,'NULL',registrationsIds)) );
            SET nextRegistrationId = SUBSTRING_INDEX(registrationsIds, ',', 1);
            INSERT INTO LOGS(line) VALUES (CONCAT('nextRegistrationId ',IF (nextRegistrationId IS NULL,'NULL',nextRegistrationId)) );

            INSERT INTO groups (NAME, administratorIdentificationNumber, numberOfParticipants, lastRegistrationDate, lastEventName, eventId)
             SELECT 
                groups.name, 
                administratorIdentificationNumber, 
                numberOfParticipants, 
                lastRegistrationDate, 
                (SELECT eventName FROM registrations WHERE id = nextRegistrationId) AS  lastEventName, 
                eventId 
                FROM groups WHERE id = currentGroupId;

            UPDATE registrations SET groupId = (SELECT MAX(id) FROM groups) WHERE id = nextRegistrationId;

            SET SubStrLen = LENGTH(SUBSTRING_INDEX(registrationsIds, ',', 1));
            SET registrationsIds = MID(registrationsIds, SubStrLen, strLen);            

            IF registrationsIds IS NULL THEN
                LEAVE createAndAssignGroups;
            END IF;         
        END LOOP;

        IF done THEN
            LEAVE READ_LOOP;
        END IF;
    END LOOP;

    CLOSE registrationsWithSameGroupId;

END$$

DELIMITER ;

Answer 1

您需要检查您的继续处理程序是否在 FETCH 之后立即触发。

FETCH NEXT FROM registrationsWithSameGroupId INTO countRepeated, registrationsIds, currentGroupId;       
IF done THEN
    LEAVE READ_LOOP;
END IF;
INSERT INTO LOGS(line) VALUES ...