MySQL - 声明变量时出错

Question

我写了一个小代码块(稍后在 MySQL 的事件中使用，但在声明看似标准的 INT 变量时出现错误:

BEGIN
      DECLARE myvar INT;
      SELECT TIMESTAMPDIFF INTO myvar (MINUTE,(select user_hb_stamp from eclipse_users where username = 'user1'),(SELECT NOW()));

      IF (myvar > 5)
            UPDATE eclipse_users SET logged=0 WHERE username = 'user1';
END

我做错了什么？

提前致谢。

Answer 1

您的select 语句格式不正确。试试这个:

  SELECT myvar := TIMESTAMPDIFF(MINUTE,
                                (select user_hb_stamp from eclipse_users where username = 'user1'),
                                NOW());

虽然您可以对变量使用into，但我更喜欢直接设置它们——除非您编写的代码需要与Oracle 兼容。 (然后所有的函数调用都不起作用。)

一个更典型的写法是:

  SELECT myvar := TIMESTAMPDIFF(MINUTE, user_hb_stamp, NOW())
  FROM eclipse_users 
  WHERE username = 'user1';

编辑:

至于declare本身，问题可能是缺少delimiter声明。尝试:

delimiter $$

create procedure . . .
begin
    declare p_myvar int;

    select p_myvar := TIMESTAMPDIFF(MINUTE, user_hb_stamp, NOW())
    from eclipse_users 
    where username = 'user1';

    . . .
end$$
delimiter ;