如果/然后存储proc，这个有什么问题？

Question

当我需要你使用@在mySQL中引用vars时，我很模糊。我来自mySQL，您经常使用它，但显然这在mySQL中是不正确的。

因此，下面的存储过程总是执行第一个IF块，即使会话值过期了--因为它总是在执行update语句。显然，我能找到的mySQL存储过程的唯一调试工具运行在Windows和Linux上。我在苹果电脑上。湿的。

所以是的。有人能看到这里出了什么问题吗？谢谢!

DELIMITER $$

CREATE DEFINER=`xxx`@`localhost` PROCEDURE `validate_session`(uid INT, token VARCHAR(256))
BEGIN
    DECLARE sessId INT DEFAULT NULL;

    SELECT sessId = id FROM UserSessions
    WHERE userId = uid
    AND sessionToken = token
    AND expires > INTERVAL 2 MINUTE + NOW() ORDER BY expires DESC LIMIT 1;

IF sessId IS NOT NULL THEN
    UPDATE UserSessions 
    SET expires = INTERVAL 2 HOUR + NOW() 
    WHERE id = sessId;
ELSE
    DELETE FROM UserSessions 
    WHERE userId = uid 
    AND sessionToken = token;
    SET @sessId = 0;

END IF;

SELECT sessId;
END

为了记录它在伪代码中应该做什么：

if we have a session for this user, with a matching token which has not expired {
    update the expiration time to 2 hours from now
    return the session id;
}
else {
    delete the (now stale) session
    return 0;
}

提前谢谢。

Answer 1

你不能设置sessId的值..。你在你的查询中做了比较。

我认为你需要这样的东西：

CREATE DEFINER=`xxx`@`localhost` PROCEDURE `validate_session`(uid INT, token VARCHAR(256))
BEGIN
    DECLARE sessId INT DEFAULT NULL;

    SELECT id 
        INTO sessId -- Here is the assignment
    FROM UserSessions
    WHERE userId = uid
      AND sessionToken = token
      AND expires > INTERVAL 2 MINUTE + NOW() 
    ORDER BY expires DESC 
    LIMIT 1;

    IF sessId IS NOT NULL THEN
        UPDATE UserSessions 
            SET expires = INTERVAL 2 HOUR + NOW() 
            WHERE id = sessId;
    ELSE
        DELETE FROM UserSessions 
            WHERE userId = uid 
            AND sessionToken = token;
        SET sessId = 0;
   END IF;

   SELECT sessId;
END

参考资料：

• 语法
• MySQL引用:局部变量范围和分辨率