Mysql Event Scheduler 使用局部变量 [英] Mysql Event Scheduler using local variable

问题描述

我正在尝试在 mysql 中创建报告计划，但查询取决于局部变量 fdate.如何让 mysql 加载 fdate 变量，然后在第二个事件中运行查询?谢谢

I am trying to create a report schedule in mysql, but the query depends on the local variable fdate. How can I have mysql load the fdate variable and then run the query in the 2nd event? Thanks

CREATE EVENT fdate
  ON SCHEDULE
  EVERY 1 DAY
starts '2018-03-26 07:30:00'
Do
 set @fdate = 2018031; #EOM prior month

create event report_1 on SCHEDULE
   EVERY 1 day
 starts '2018-03-26 07:31:00'
DO
 <Here is the Query depending up fdate>

推荐答案

我认为每个事件都在一个新会话中执行，并且用户定义的变量具有会话作用域.所以你不能真正在事件中使用这些变量，并让它们的值从一个事件执行到另一个事件执行.

I think each event executes in a new session, and user-defined variables have session scope. So you can't really use those variables in events and have their value survive from one event execution to another.

这是一个测试来证明它:

Here's a test to show it:

mysql> create table log ( i int );

mysql> create event e on schedule every 1 minute do
    -> insert into log set i = @a := coalesce(@a+1, 1);

如果 @a 变量从一个事件执行到下一个事件都保持其值，我们将在我的 log 表中得到一系列递增的值.

If the @a variable retains its value from one event execution to the next, we'll get a series of incrementing values in my log table.

几分钟后，我们看到我们没有得到递增的值.每次执行都从 1 开始计数器.

A few minutes later, we see that we don't get incrementing values. Each execution starts the counter over at 1.

mysql> select * from log;
+------+
| i    |
+------+
|    1 |
|    1 |
|    1 |
+------+

但是你可以DECLARE一个局部变量，并在一个事件的复合语句体中使用它.

But you can DECLARE a local variable, and use it in a compound statement body for an event.

CREATE EVENT fdate
  ON SCHEDULE
  EVERY 1 DAY
  STARTS '2018-03-26 07:30:00'
DO BEGIN
  DECLARE fdate INT;
  SET fdate = DATE_FORMAT(LAST_DAY(CURDATE() - INTERVAL 1 MONTH), '%Y%m%d');
  # following query uses fdate 
END

这篇关于Mysql Event Scheduler 使用局部变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！