mysql定时函数_MySQL——定时函数+过程demo

-- 函数

-- 设置好时区

set time_zone = '+8:00';

-- 开启事件调度器

set GLOBAL event_scheduler = 1;

drop event if exists commission_zjdj_a_every_hours;

# 设置分隔符为 '$$'

DELIMITER $$

create event commission_zjdj_a_every_hours

on schedule every 1 HOUR starts '2018-06-24 23:59:59'

do

begin

CALL proc_zjdj_a_commission();

end $$

# 将语句分割符设置回 ';'

DELIMITER ;

-- 过程

###获取当天有效的资金对接A岗一次打包数据，保持当天一份

DROP PROCEDURE IF EXISTS proc_zjdj_a_commission;

CREATE PROCEDURE proc_zjdj_a_commission ()

BEGIN

DELETE

FROM

report_statistics_commission_inquiry

WHERE

type = 2

AND date_format(count_time, '%Y-%m') = date_format(now(), '%Y-%m');

#一次打包--> 资金对接A岗

INSERT INTO `report_statistics_commission_inquiry` (

`user_id`,

`order_code`,

`execute_time`,

`task_code`,

`count_time`,

`type`,

`ext1`,

`ext2`,

`ext3`

) SELECT

receive_id,

order_code,

finish_time,

task_code,

NOW(),

2,

NULL,

NULL,

NULL

FROM

my_task

WHERE

`status` = 2

AND task_code = 'T_YCDB_0004'

#排除重复订单

AND order_code NOT IN (

SELECT

order_code

FROM

report_statistics_commission_inquiry

WHERE

type = 2

) GROUP BY order_code;

END;