mysql - 不更新表行的过程

Question

我有这个程序(不要太费心去弄清楚它的作用，目标是名为“修改 1,2,3,4”的评论)

/* PROCEDURE 1 : Post notification */
DROP PROCEDURE IF EXISTS AddNotificationOnPosts;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddNotificationOnPosts`(arg_from_user INT(11),arg_on_post_id INT(11),arg_in_group_id INT(11))
BEGIN
    DECLARE num_rows INT DEFAULT NULL;
    DECLARE insert_result INT DEFAULT NULL;
    DECLARE user_id INT DEFAULT NULL;

    DECLARE done INT DEFAULT 0;
    DECLARE var_user_id INT DEFAULT NULL;
        DECLARE c1 CURSOR FOR 
        SELECT user_id 
        FROM user_rights 
        WHERE user_rights.right = 101 AND user_rights.group_id  = arg_in_group_id 
        ORDER BY user_id DESC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    IF(arg_from_user IS NULL OR arg_from_user = '')
    THEN
        SELECT "0" AS response;
    ELSEIF(arg_on_post_id IS NULL OR arg_on_post_id = '')
    THEN
        SELECT "0" AS response;
    ELSEIF(arg_in_group_id IS NULL OR arg_in_group_id = '')
    THEN
        SELECT "0" AS response;
    ELSE
        SELECT count(notification_id) FROM notifications_posts 
        WHERE 
        from_user = arg_from_user AND
        on_post_id = arg_on_post_id AND
        in_group_id = arg_in_group_id
        INTO num_rows;

        /* MODIFY 1*/
        UPDATE user_info SET notifications = 1 WHERE user_id = 145;
    END IF;

    IF num_rows = 0
    THEN
        INSERT INTO notifications_posts(from_user,on_post_id,in_group_id) VALUES(arg_from_user,arg_on_post_id,arg_in_group_id);
        SELECT ROW_COUNT() INTO insert_result;

        /* MODIFY 2*/
        UPDATE user_info SET notifications = 1 WHERE user_id = 1;

        IF insert_result > 0 
        THEN

        /* MODIFY 3*/
        UPDATE user_info SET notifications = 1 WHERE user_id = 5;

            /* Increment the notifications for every user*/
            OPEN c1;
            read_loop: LOOP
                FETCH c1 INTO var_user_id;
                    IF done THEN
                        LEAVE read_loop;
                    ELSE
                        /* MODIFY 4*/
                        UPDATE user_info SET notifications = 1 WHERE user_id = 1;
                    END IF;
            END LOOP;
            CLOSE c1;

            SELECT "1" AS response;
        ELSE
            SELECT "0" AS response;
        END IF;

    ELSE
        SELECT "0" AS response;
    END IF;
END $$
DELIMITER ;

这工作得很好，除了线条

 UPDATE user_info SET notifications = 1 WHERE user_id = 1;

不会工作，但在简单的纯 SQL(phpmyadmin) 中，此查询工作正常。有什么问题？

这个脚本是做什么的？它帮助我能够向某些用户发布通知，当您在 group1 中发布something 时，所有在该组中拥有 right101 的用户必须得到通知

 UPDATE user_info SET notifications = notifications  + 1 WHERE user_id = var_user_id;

像我以前在 PHP 中那样使用游标作为 FOR LOOP

这有什么问题吗？程序不能更新数据吗？!希望我让自己明白了。

Answer 1

不要听起来冒昧，但数据是否能让你超越

IF num_rows = 0

作为提示，如果您在 SQL Management Studio 中运行，您可以像普通代码一样使用断点调试 sql。我建议在该行上放置一个断点，看看它是否真的被击中了。