mySQL存储过程where子句问题

I've got a mySql stored procedure that looks like this--

我有一个看起来像这样的mySql存储过程 -

delimiter |
create procedure GetEmployeeById(in ID varchar(45))
begin
  select id,
      firstName,
      lastName,
      phone,
      address1,
      address2,
      city,
      state,
      zip,
      username,
      password,
      emptypeid
  from myschema.tblemployees t
  where
      t.id=ID limit 1;
end |
delimiter;

If i don't have the limit 1 in place, it always returns all of the rows in the table--with each record's id value set to the ID parameter. Why can't i just use where id=ID, and why does it return all of the records when i do? What are the implications of me using limit 1? Why am i programming on a saturday night?

如果我没有限制1,它总是返回表中的所有行 - 每个记录的id值设置为ID参数。为什么我不能只使用id = ID的地方,为什么我这样做会返回所有记录?我使用限制1的含义是什么?为什么我在星期六晚上编程?

2 个解决方案

#1

Because, it's comparing t.id with itself, which will always be true. Call your formal parameter something else.

因为,它将t.id与自身进行比较,这将永远是真实的。调用你的形式参数别的东西。

#2

Column names in MySQL are not case-sensitive. The id column in your query hides the parameter named ID, so your where clause is really using two different expressions to refer to the same column. And of course a column's value is always equal to itself, so all the records match. Use a different name for the input parameter.

MySQL中的列名称不区分大小写。查询中的id列隐藏了名为ID的参数,因此where子句实际上使用两个不同的表达式来引用同一列。当然,列的值总是等于它自己,因此所有记录都匹配。为输入参数使用不同的名称。