MySQL存储过程返回错误的值

Im new to MySQL stored procedures and I was following some tutorial on how to use them, but I ran into an interesting thing with the following:

我是MySQL存储过程的新手，我正在学习一些如何使用它们的教程，但是我遇到了一个有趣的事情:

DELIMITER $$
CREATE DEFINER=`user`@`%` PROCEDURE `CalculateScores`(IN ID INT, OUT test INT)
BEGIN
    SELECT COUNT(*)
    INTO test
    FROM myTable
    WHERE id = ID;
END$$
DELIMITER ;

I run it with this:

我是这样运行的:

CALL CalculateScores(252, @test);

and then just:

然后就:

SELECT @test;

The strange thing is that @test returns the total row count of the entire table not just for the id I sent as a parameter.

奇怪的是，@test不仅返回作为参数发送的id，还返回整个表的行总数。

What am I missing here? The tutorial never mention this, and I can't find an answer to why this is happening, I might suck at searching..

我错过了什么?本教程中没有提到这一点，我也找不到为什么会发生这种情况的答案，我可能在搜索方面很糟糕。

1 个解决方案

#1

It looks like MySQL cannot differentiate between id and ID:

看起来MySQL无法区分id和id:

SELECT COUNT(*)
INTO test
FROM myTable
WHERE id = ID;

And it treats it like 1 = 1 which is always true (if column is not nullable).

它把它看成1 = 1，这总是正确的(如果列不可空)。

You could add alias to indicate that id is column and not parameter.

您可以添加别名来表明id是列而不是参数。

CREATE PROCEDURE `CalculateScores`(IN ID INT, OUT test INT)
BEGIN
    SELECT COUNT(*)
    INTO test
    FROM myTable t
    WHERE t.id = ID;
END

SqlFiddleDemo

SqlFiddleDemo

#1