使用 SELECT 结果集通过 MySQL 存储过程运行 UPDATE 查询
我正在尝试了解 MySQL 存储过程,我想检查用户登录凭据是否有效,如果有效,则更新用户在线状态:
-- DROP PROCEDURE IF EXISTS checkUser;
DELIMITER //
CREATE PROCEDURE checkUser(IN in_email VARCHAR(80), IN in_password VARCHAR(50))
BEGIN
SELECT id, name FROM users WHERE email = in_email AND password = in_password LIMIT 1;
-- If result is 1, UPDATE users SET online = 1 WHERE id = "result_id";
END //
DELIMITER ;
How Can I make this if-statement based on the resultsets number of rows == 1 or id 不为空?
I'm trying to understand MySQL Stored Procedures, I want to check if a users login credentials are valid and if so, update the users online status:
-- DROP PROCEDURE IF EXISTS checkUser;
DELIMITER //
CREATE PROCEDURE checkUser(IN in_email VARCHAR(80), IN in_password VARCHAR(50))
BEGIN
SELECT id, name FROM users WHERE email = in_email AND password = in_password LIMIT 1;
-- If result is 1, UPDATE users SET online = 1 WHERE id = "result_id";
END //
DELIMITER ;
How Can I make this if-statement based on the resultsets number of rows == 1 or id IS NOT NULL?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
注意:我还没有测试过这个。 MySQL 可能不喜欢对其当前打开游标的表进行 UPDATE。
PS:您应该重新考虑如何存储密码。
重新评论
RETURN
与OUT
与结果集:RETURN
仅用于存储函数,而不是存储过程。当您想要在另一个 SQL 表达式中调用例程时,可以使用存储函数。您可以使用
OUT
参数,但必须首先声明一个用户变量才能作为该参数传递。然后您必须选择该用户变量才能获取其值。从存储过程返回结果集时,最简单的方法是使用
SELECT
查询。NB: I have not tested this. It's possible that MySQL doesn't like UPDATE against a table it currently has a cursor open for.
PS: You should reconsider how you're storing passwords.
Re comment about
RETURN
vs.OUT
vs. result set:RETURN
is used only in stored functions, not stored procedures. Stored functions are used when you want to call the routine within another SQL expression.You can use an
OUT
parameter, but you have to declare a user variable first to pass as that parameter. And then you have to select that user variable to get its value anyway.When returning result sets from a stored procedure, it's easiest to use a
SELECT
query.使用:
为什么你的 SELECT 上有
LIMIT 1
?您真的希望电子邮件和密码多次出现在数据库中吗?Use:
Why do you have
LIMIT 1
on your SELECT? Do you really expect an email and password to be in the db more than once?如果结果返回 1,您可以尝试使用 if 语句
我看了你的代码,似乎没有什么返回 true 所以你必须重构它,
正如上面所写的,天啊,这确实是真的,为什么你的选择查询中有一个限制 1,其中只能存在一个电子邮件地址?
像这样的东西
You could try an if statement if you have an result which returns 1
i looked at yor code, it seems nothing returns a true so you have to refactor it,
as above omg wrote thats realy true why do you have an limit 1 in your select query where only one emailadress can exisst?
something like this