MySQL存储过程选择语句找到不准确的结果(MySQL Stored Procedure select statement finding inaccurate result)

MySQL存储过程选择语句找到不准确的结果(MySQL Stored Procedure select statement finding inaccurate result)

我有一个存储过程，应该只是检查一个电子邮件是否已经存在，并根据是否找到ID返回布尔值TRUE或FALSE

该表看起来像这样：

结果是这样的：

这是非常不正确的

程序如下：

CREATE PROCEDURE `EmailExists` (email VARCHAR(255), OUT output BOOL) email_exists_proc:BEGIN DECLARE existing_id CHAR(36); DECLARE existing_email2 varchar(255); DECLARE found_name varchar(255); SELECT `id`, `email`, `display_name` INTO existing_id, existing_email2, found_name FROM `account` WHERE `email` = email LIMIT 1; SELECT "EMail Exists", existing_email2, " == ", email, " ? ", existing_id, "name: ", found_name, existing_id IS NOT NULL; SELECT existing_id IS NOT NULL INTO output; END$$

这是非常基本的查询，这是电话：

CALL EmailExists("something@something.com", @exists); SELECT @exists; # this returns 1 right now, it should be 0, but it's "finding" the provided email, somehow.

我想念的是什么，我在这里做错了什么？ 为什么existing_email2最终会镜像email ？ 该值不存在于表格中。

I have a stored procedure that's supposed to simply check if an email already exists and return a boolean TRUE or FALSE based on if an ID is found

The table looks like this:

The result is this:

Which is wildly incorrect

Here's the procedure:

CREATE PROCEDURE `EmailExists` (email VARCHAR(255), OUT output BOOL) email_exists_proc:BEGIN DECLARE existing_id CHAR(36); DECLARE existing_email2 varchar(255); DECLARE found_name varchar(255); SELECT `id`, `email`, `display_name` INTO existing_id, existing_email2, found_name FROM `account` WHERE `email` = email LIMIT 1; SELECT "EMail Exists", existing_email2, " == ", email, " ? ", existing_id, "name: ", found_name, existing_id IS NOT NULL; SELECT existing_id IS NOT NULL INTO output; END$$

It's pretty basic query, here's the call:

CALL EmailExists("something@something.com", @exists); SELECT @exists; # this returns 1 right now, it should be 0, but it's "finding" the provided email, somehow.

What I missing, what am I doing wrong here? Why does existing_email2 end up mirroring email? The value doesn't exist in the table.

最满意答案

限定对查询中列的引用，因此引用列的标识实际上是引用列而不是过程变量。

反引号用于转义标识符。 反引号不标识哪些标识符引用列以及哪些引用过程变量。

将表别名a分配给帐户表，并使用a.限定所有列引用a.

SELECT a.`id` , a.`email` , a.`display_name` FROM `account` a WHERE a.`email` = email LIMIT 1 INTO existing_id , existing_email2 , found_name ;

---

最佳做法是使用与列名称不同的过程变量的名称。

我个人的偏好是为过程值使用不同的名称

... PROCEDURE `EmailExists` (as_email VARCHAR(255), ... ^^

然后这个

WHERE a.`email` = as_email

对人类读者来说不太模糊。

然后，为了获得原始代码中的行为，我们会写下：

WHERE as_email = as_email

这使得它更明显为什么accounts每一行都满足为as_email提供的任何非NULL值的条件。

---

参考： https ： //dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html

名称存储例程中的冲突

相同的标识符可能用于例程参数，本地变量和表列。 而且，可以在嵌套块中使用相同的本地变量名称。 例如：

在这种情况下，标识符不明确，并应用以下优先规则：

局部变量优先于常规参数或表格列。

例程参数优先于表列。

Qualify the references to columns in the queries, so identifiers meant to reference a column are actually referencing a column, rather than a procedure variable.

The backticks are used to escape identifiers. The backticks do not identify which identifiers reference columns and which reference procedure variables.

Assigning table alias a to the account table, and qualifying all column references with a.

SELECT a.`id` , a.`email` , a.`display_name` FROM `account` a WHERE a.`email` = email LIMIT 1 INTO existing_id , existing_email2 , found_name ;

---

Best practice is to use names for procedure variables that differ from column names.

My personal preference would be to use a different name for the procedure value

... PROCEDURE `EmailExists` (as_email VARCHAR(255), ... ^^

And then this

WHERE a.`email` = as_email

is less ambiguous to the human reader.

Then, to get the behavior in the original code, we would have written:

WHERE as_email = as_email

which makes it a little more obvious why every row in accounts satisfies that condition for any non-NULL value provided for as_email.

---

Reference: https://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html

Name Conflicts within Stored Routines

The same identifier might be used for a routine parameter, a local variable, and a table column. Also, the same local variable name can be used in nested blocks. For example:

In such cases, the identifier is ambiguous and the following precedence rules apply:

A local variable takes precedence over a routine parameter or table column.

A routine parameter takes precedence over a table column.