MySQL 存储过程没有返回正确的结果 [英] MySQL stored procedure not returning correct results

问题描述

我有一个存储过程，用于检查用户是否已经存在，无论它使用数据库中的第一行返回的电子邮件使用什么输入.如果我手动运行 select 语句，我会得到正确的结果.任何建议为什么?

I have a stored procedure that checks if a user exists already, regardless of the input used for the email it comes back with the first line in the database. If I run the select statements manually I get the correct results. Any suggestions why?

CREATE DEFINER=`myusername`@`localhost` PROCEDURE `User_Auth`(IN `Email` VARCHAR(30))
BEGIN
    DECLARE User_ID INT(30);
        SELECT `User ID` INTO User_ID FROM `Users` WHERE `Email` = Email LIMIT 1;

    IF (User_ID > 0) THEN
        SELECT * FROM `Users` WHERE `User ID` = User_ID;
    ELSE
        SELECT concat('No Users Found: ', Customer_Name);
    END IF;
END

推荐答案

你的问题无疑出在这里:

Your problem no doubt lies here:

SELECT `User ID` INTO User_ID FROM `Users` WHERE `Email` = Email LIMIT 1;

一点;您的参数名称与列名称相同.这可以实际工作，但让它这样做的规则可能很奇怪.(我想如果你改变引号，它可能会起作用).

To whit; your parameter name is the same as your column name. That can actually work, but the rules to make it do so can be weird. (I think if you switched the quotes around, it might work).

但实际上，您应该做的是将 Email 参数重命名为其他名称:

But really, what you should do is rename the Email parameter to something else:

CREATE DEFINER=`myusername`@`localhost` PROCEDURE `User_Auth`(IN `EmailAddress` VARCHAR(30))
BEGIN
    DECLARE User_ID INT(30);
        SELECT `User ID` INTO User_ID FROM `Users` WHERE `Email` = EmailAddress  LIMIT 1;

    IF (User_ID > 0) THEN
        SELECT * FROM `Users` WHERE `User ID` = User_ID;
    ELSE
        SELECT concat('No Users Found: ', Customer_Name);
    END IF;
END

这篇关于MySQL 存储过程没有返回正确的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！