MySQL 游标未在过程内获取

Question

我有以下表

albums:

+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| album_id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| band_id        | int(11)     | YES  | MUL | NULL    |                |
| release_date   | varchar(45) | YES  |     | NULL    |                |
| name           | varchar(45) | YES  |     | NULL    |                |
| format         | varchar(45) | YES  |     | NULL    |                |
| music_genre_id | int(11)     | YES  | MUL | NULL    |                |
| label_id       | int(11)     | YES  | MUL | NULL    |                |
| avg_rating     | float       | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+

和 music_ ratings

+-----------------+---------+------+-----+---------+----------------+
| Field           | Type    | Null | Key | Default | Extra          |
+-----------------+---------+------+-----+---------+----------------+
| music_rating_id | int(11) | NO   | PRI | NULL    | auto_increment |
| user_id         | int(11) | YES  | MUL | NULL    |                |
| album_id        | int(11) | YES  | MUL | NULL    |                |
| rating          | int(11) | YES  |     | NULL    |                |
+-----------------+---------+------+-----+---------+----------------+

每次插入 *music_ rating* 后，我想更新专辑表中的平均评级。我有一个触发器，它调用一个过程。问题是，该过程不起作用，由于某种原因游标没有从表中获取数据。 （我单独调用该过程以确保它不是触发器起作用。表已经有几行了，所以不是那样的。）

我的过程非常简单，看起来像这样

DELIMITER $$

CREATE PROCEDURE avg_album_calc(IN id_album INT)
BEGIN

    DECLARE done INT DEFAULT 0; 
    DECLARE rating INT; 
    DECLARE cur CURSOR FOR SELECT `rating` FROM `music_ratings` WHERE `album_id`=id_album;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;   
    SET @ct=0; 
    SET @sm=0;
    REPEAT
        FETCH cur INTO rating;
        IF NOT done
        THEN 
            SET @ct = @ct +1;
            SET @sm = @sm + rating;     
        END IF;

    UNTIL done END REPEAT;
    UPDATE albums SET avg_rating = @sm/@ct WHERE album_id = id_album;
    CLOSE cur;
END$$
DELIMITER ;

我回显了光标的结果在 FETCH cur INTO rating; 命令后添加 SELECT rating，它会显示为 null。

Answer 1

您无需计算 avg_ rating 并将其存储在 albums 表中。您可以即时计算 -

SELECT a.album_id, a.name, AVG(mr.rating) FROM albums a
  LEFT JOIN music_ratings mr
    ON a.album_id = mr.album_id
GROUP BY a.album_id