mysql 存储过程创建事件_mysql 创建事件调用存储过程

-- 删除存储过程

drop PROCEDURE event_insert

-- 创建存储过程

create PROCEDURE event_insert()

begin

INSERT into settlement(date,num)

select create_time,id from car_info ;

end ;

-- 调用存储过程

call event_insert();

-- 1：先查看是否开启事件

SELECT @@event_scheduler;

SHOW VARIABLES LIKE 'event_scheduler';

SELECT @@event_scheduler;

-- 2：如果关闭了先开启

set global event_scheduler=1;

SET GLOBAL event_scheduler = ON;

-- 删除事件

drop event event_yy_statistical

-- 创建事件 -- 没3秒执行一次

CREATE EVENT event_yy_statistical

ON SCHEDULE  EVERY 3 SECOND

ON COMPLETION PRESERVE

DO

call event_insert();

ENABLE

COMMENT '运营统计报表事件'

-- 创建事件 每天15点10执行--

CREATE EVENT event_yy_statistical

ON SCHEDULE

EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 0 HOUR), INTERVAL 5 MINUTE)

ON COMPLETION PRESERVE

DO

call event_insert();

ENABLE

COMMENT '运营统计报表事件'

-- 开启事件

alter event event_yy_statistical ON COMPLETION PRESERVE ENABLE;

-- 关闭事件

alter event event_yy_statistical ON COMPLETION PRESERVE DISABLE;

-- 事件的语法参考：

CREATE

[DEFINER = { user | CURRENT_USER }]

EVENT

[IF NOT EXISTS]

event_name

ON SCHEDULE schedule

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE | DISABLE ON SLAVE]

[COMMENT 'comment']

DO event_body;

schedule:

AT timestamp [+ INTERVAL interval] ...

| EVERY interval

[STARTS timestamp [+ INTERVAL interval] ...]

[ENDS timestamp [+ INTERVAL interval] ...]

interval:

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

WEEK | SECOND | YEAR_MONTH | DAY_HOUR |

DAY_MINUTE |DAY_SECOND | HOUR_MINUTE |

HOUR_SECOND | MINUTE_SECOND}