吴环宇 吴环宇 - 3 months ago 9
MySQL Question

Call a stored function in mysql,But get an error:Subquery returns more than 1 row

In mysql database,I have a table called 'usertest' which has some user information stored in it. And I create a UDF called 'getId' successfully. This is how it created:

create function getId(name varchar(255))
returns int
begin
declare id_found int;
set id_found=(select Id from usertest where Name=name);
return id_found;
end


It should be OK.However,when I call

select getId('mike');


mysql engine reports an error:

Subquery returns more than 1 row.


But in table usertest I have only one row where Name='mike'.That's wired.Someone can tell me why? Thank you in advance :)

Answer

Column names and variables are case-insensitive, so Name and name are the same thing. It's not comparing the Name column with the name variable, it's comparing the name variable with itself, so it's always true.

Use a different name for the function parameter.

create function getId(name_param varchar(255))
returns int
begin
declare id_found int;
set id_found=(select Id from usertest where Name=name_param);
return id_found;
end

or use a table name prefix.

create function getId(name varchar(255))
returns int
begin
declare id_found int;
set id_found=(select Id from usertest where usertest.Name=name);
return id_found;
end

See MySQL : When stored procedure parameter name is the same as table column name

Comments