mysql - mysql 存储过程中的设置和选择无法正常工作

Question

我有一个 MySQl 存储过程，其行为异常并给出错误的输出。

CREATE PROCEDURE GetVotes(IN postId INT)
BEGIN

DECLARE upVotes INT DEFAULT 0;
DECLARE downVotes INT DEFAULT 0;
DECLARE stars INT DEFAULT 0;

SET upVotes := ( SELECT count(upvote) FROM threadvote WHERE upvote = 1 AND postid = postId );

SET downVotes := (SELECT count(downvote) FROM threadvote WHERE downvote = 1 AND postid = postId );

SET stars := (SELECT count(starred) FROM threadvote WHERE starred = 1 AND postid = postId );

SELECT upVotes as numupvotes, downVotes as numdownvotes , stars as numstars;

END

它就像蛋糕一样简单，但仍然占用我的时间，因为它给出了错误的输出。SET 语句使用表中的所有行计数设置变量(即它似乎忽略了 where 子句)。

示例数据:

itemid userid   postid upvote   downvote starred
9      15       9        1        0        0
12     16       9        1        0        1
13     15       7        1        0        0
14     15       8        1        0        1
15     16       7        1        0        1
16     16       8        0        1        0
17     16       11       0        1        0
18     15       11       0        1        0

现在 CALL GetVotes(8); 给我:

numupvotes numdownvotes numstars
5          3            2

应该是:

numupvotes numdownvotes numstars
1          1            1

另外，使用 @ 符号声明会出现语法错误。例如

DECLARE @upVotes INT DEFAULT 0;

给出语法错误。

请让我知道这里出了什么问题。或者我缺少一些 mysql 服务器设置？

Answer 1

您遇到名称冲突问题。也就是说，postid = postid 几乎总是正确的。使用前缀来区分参数:

CREATE PROCEDURE GetVotes(IN in_postId INT)
BEGIN

    DECLARE upVotes INT DEFAULT 0;
    DECLARE downVotes INT DEFAULT 0;
    DECLARE stars INT DEFAULT 0;

    SELECT up_Votes := SUM(upvote = 1), downvote := SUM(downvote = 1),
           stars := SUM(starred = 1)
    FROM threadvote tv
    WHERE tv.postid = in_postId;

    SELECT upVotes as numupvotes, downVotes as numdownvotes , stars as numstars;

END