问题描述
我的查询返回正确的值,但是当我将查询作为存储过程运行时,它返回不正确的结果.这是我返回正确值的查询
My query returns correct values , but when i run the query as a stores procedure it returns incorrect results. this is my query which returns correct values
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = 5;
当我运行这个存储过程时,它返回所有行并显示 profieId 作为传入的 profileId ,这是我的存储过程
and when i run this stored procedure, it is returning all rows and shows profieId as the passed in profileId , this is my stored procedure
CREATE DEFINER=`root`@`localhost` PROCEDURE `getImagesForUser`(in profileId long)
BEGIN
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = profileId;
END
这就是我调用程序的方式
this is how i call the procedure
CALL `tfm`.`getImagesForUser`(5);
请看截图
查询截图
这是存储过程的错误结果
this is the incorrect result from stored procedure
你可以看到,mysql 说所有图像都属于我传入的 profileId 5 .我的存储过程有什么问题
you can see, mysql says all the images belongs to profileId 5 , which i passed in.What is wrong with my stored procedure
推荐答案
您的问题是您的输入参数与表中的字段同名,而在您的查询中 MySQL 将 profileId
解释为改为字段名称.因此,您的 where profileId = profileId
始终为真,您将获得所有行.更改输入参数的名称,例如
Your problem is that your input parameter has the same name as the field in your table, and inside your query MySQL interprets profileId
as being the field name instead. Thus your where profileId = profileId
is always true, and you get all rows. Change the name of the input parameter e.g.
CREATE DEFINER=`root`@`localhost` PROCEDURE `getImagesForUser`(in searchProfileId long)
BEGIN
select imageId,imageVerified,isCoverImage,isDeleted,isProfileImage,path,profileId
from images where profileId = searchProfileId;
END
这篇关于存储过程返回错误值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!