mysql event命令

最新推荐文章于 2024-10-23 07:45:00 发布

☞风雨中☜回荡着的气息

最新推荐文章于 2024-10-23 07:45:00 发布

阅读量340

点赞数 10

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_43261942/article/details/138583025

版权

查看事件调度器是否打开

show variables like ‘%event_scheduler%’;

开启时间调度器

set GLOBAL event_scheduler = ON;

查看时间调度器线程

show processlist;

show events;

SELECT
EVENT_SCHEMA as ‘数据库’,
EVENT_NAME as ‘事件名’,
LAST_EXECUTED as ‘上次执行时间’,
Status as ‘状态’
#LAST_ERROR as ‘上次错误’
FROM
information_schema.EVENTS
WHERE
EVENT_SCHEMA = event_test;

drop event event_vehicle_gas_stat;

创建事件立即执行

create event event_now
on schedule at NOW()
do insert into events_list values(DEFAULT,‘event1’,now())

select DAY(CURRENT_DATE())

例子

# 创建加油几率统计事件---车辆统计
CREATE EVENT event_vehicle_gas_stat
ON SCHEDULE at NOW() DO
BEGIN
    DECLARE CUR_YEAR SMALLINT DEFAULT YEAR(CURRENT_DATE());
    DECLARE CUR_MONTH TINYINT DEFAULT MONTH(CURRENT_DATE());
    DECLARE CUR_DAY TINYINT DEFAULT DAY(CURRENT_DATE()) - 1;
    DECLARE CURSOR1_NOT_EMPTY TINYINT(1) DEFAULT TRUE;
    DECLARE HAS_RECORD TINYINT(1) DEFAULT TRUE;
    DECLARE STAT_STATISTICS_CODE VARCHAR(32);
    DECLARE STAT_GAS_TYPE VARCHAR(32);
    DECLARE STAT_GAS_STAGE VARCHAR(32);
		DECLARE STAT_SALE_AMOUNT VARCHAR(32);
		DECLARE STAT_FILL_UP_NUM VARCHAR(32);
		DECLARE STAT_OPER_COUNT VARCHAR(32);
    DECLARE STAT_ID LONG;
		
		-- MySQL要求所有的局部变量、条件处理器（Handlers）和游标的声明必须放在BEGIN...END块的开头
    DECLARE VEHICLE_STATS CURSOR FOR 
        SELECT statistics_code, gas_type,gas_stage,
               SUM(sale_amount) AS sale_amount,
               SUM(fill_up_num) as fill_up_num,
               SUM(oper_count) as oper_count
        FROM dtms_refuel_statistics 
        WHERE `year` = CUR_YEAR AND `month` = CUR_MONTH AND statistics_dimension = '3' and statistics_type = '1'
        GROUP BY statistics_code, gas_type,gas_stage;

		DECLARE CONTINUE HANDLER FOR NOT FOUND SET CURSOR1_NOT_EMPTY = FALSE;
		
-- 1、根据车辆统计
    OPEN VEHICLE_STATS;
    FETCH VEHICLE_STATS INTO STAT_STATISTICS_CODE, STAT_GAS_TYPE,STAT_GAS_STAGE,STAT_SALE_AMOUNT,STAT_FILL_UP_NUM,STAT_OPER_COUNT;

    WHILE CURSOR1_NOT_EMPTY DO
		
			-- 1. 刷新车辆月统计数据
        SELECT id INTO STAT_ID FROM dtms_refuel_statistics 
        WHERE `year` = CUR_YEAR AND `month` = CUR_MONTH AND statistics_dimension = '2' and statistics_type = '1' 
        and statistics_code = STAT_STATISTICS_CODE and gas_type = STAT_GAS_TYPE and gas_stage = STAT_GAS_STAGE;

        IF STAT_ID IS NOT NULL THEN
            -- 更新操作
            UPDATE dtms_refuel_statistics 
            SET `sale_amount` = STAT_SALE_AMOUNT, 
                `fill_up_num` = STAT_FILL_UP_NUM, 
                `oper_count` = STAT_OPER_COUNT 
            WHERE id = STAT_ID;
        ELSE
            -- 新增月记录
            INSERT INTO dtms_refuel_statistics (id,statistics_type,statistics_code,goods,statistics_dimension,`year`,`month`,`day`,fill_up_num,oper_count,sale_amount,gas_stage,gas_type)
             VALUES(default,1,STAT_STATISTICS_CODE,STAT_GAS_TYPE,2,CUR_YEAR,CUR_MONTH,null,STAT_FILL_UP_NUM,STAT_OPER_COUNT,STAT_SALE_AMOUNT,STAT_GAS_STAGE,STAT_GAS_TYPE);
        END IF;
				
				
			-- 2. 刷新年统计数据
				SELECT id INTO STAT_ID FROM dtms_refuel_statistics 
        WHERE `year` = CUR_YEAR  AND statistics_dimension = '1' and statistics_type = '1' 
        and statistics_code = STAT_STATISTICS_CODE and gas_type = STAT_GAS_TYPE and gas_stage = STAT_GAS_STAGE;

        IF STAT_ID IS NOT NULL THEN
            -- 更新操作
            UPDATE dtms_refuel_statistics 
            SET `sale_amount` = STAT_SALE_AMOUNT, 
                `fill_up_num` = STAT_FILL_UP_NUM, 
                `oper_count` = STAT_OPER_COUNT 
            WHERE id = STAT_ID;
        ELSE
            -- 新增月记录
            INSERT INTO dtms_refuel_statistics (id,statistics_type,statistics_code,goods,statistics_dimension,`year`,`month`,`day`,fill_up_num,oper_count,sale_amount,gas_stage,gas_type)
             VALUES(default,1,STAT_STATISTICS_CODE,STAT_GAS_TYPE,1,CUR_YEAR,null,null,STAT_FILL_UP_NUM,STAT_OPER_COUNT,STAT_SALE_AMOUNT,STAT_GAS_STAGE,STAT_GAS_TYPE);
        END IF;
				
        FETCH VEHICLE_STATS INTO STAT_STATISTICS_CODE, STAT_GAS_TYPE,STAT_GAS_STAGE,STAT_SALE_AMOUNT,STAT_FILL_UP_NUM,STAT_OPER_COUNT;
    END WHILE;

    CLOSE VEHICLE_STATS;
		
END;


# 创建加油几率统计事件---车队统计
CREATE EVENT event_vehicle_gas_stat
ON SCHEDULE at NOW() DO
BEGIN
    DECLARE CUR_YEAR SMALLINT DEFAULT YEAR(CURRENT_DATE());
    DECLARE CUR_MONTH TINYINT DEFAULT MONTH(CURRENT_DATE());
    DECLARE CUR_DAY TINYINT DEFAULT DAY(CURRENT_DATE()) - 1;
    DECLARE CURSOR1_NOT_EMPTY TINYINT(1) DEFAULT TRUE;
    DECLARE HAS_RECORD TINYINT(1) DEFAULT TRUE;
    DECLARE STAT_STATISTICS_CODE VARCHAR(32);
    DECLARE STAT_GAS_TYPE VARCHAR(32);
    DECLARE STAT_GAS_STAGE VARCHAR(32);
		DECLARE STAT_SALE_AMOUNT VARCHAR(32);
		DECLARE STAT_FILL_UP_NUM VARCHAR(32);
		DECLARE STAT_OPER_COUNT VARCHAR(32);
    DECLARE STAT_ID LONG;
		
		DECLARE CARRIER_STATS CURSOR FOR 
        SELECT statistics_code, gas_type,gas_stage,
               SUM(sale_amount) AS sale_amount,
               SUM(fill_up_num) as fill_up_num,
               SUM(oper_count) as oper_count
        FROM dtms_refuel_statistics 
        WHERE `year` = CUR_YEAR AND `month` = CUR_MONTH AND statistics_dimension = '3' and statistics_type = '2'
        GROUP BY statistics_code, gas_type,gas_stage;

		DECLARE CONTINUE HANDLER FOR NOT FOUND SET CURSOR1_NOT_EMPTY = FALSE;
		
	-- 2、根据车队统计
    OPEN CARRIER_STATS;
    FETCH CARRIER_STATS INTO STAT_STATISTICS_CODE, STAT_GAS_TYPE,STAT_GAS_STAGE,STAT_SALE_AMOUNT,STAT_FILL_UP_NUM,STAT_OPER_COUNT;

    WHILE CURSOR1_NOT_EMPTY DO
		
			-- 1. 刷新车辆月统计数据
        SELECT id INTO STAT_ID FROM dtms_refuel_statistics 
        WHERE `year` = CUR_YEAR AND `month` = CUR_MONTH AND statistics_dimension = '2' and statistics_type = '2' 
        and statistics_code = STAT_STATISTICS_CODE and gas_type = STAT_GAS_TYPE and gas_stage = STAT_GAS_STAGE;

        IF STAT_ID IS NOT NULL THEN
            -- 更新操作
            UPDATE dtms_refuel_statistics 
            SET `sale_amount` = STAT_SALE_AMOUNT, 
                `fill_up_num` = STAT_FILL_UP_NUM, 
                `oper_count` = STAT_OPER_COUNT 
            WHERE id = STAT_ID;
        ELSE
            -- 新增月记录
            INSERT INTO dtms_refuel_statistics (id,statistics_type,statistics_code,goods,statistics_dimension,`year`,`month`,`day`,fill_up_num,oper_count,sale_amount,gas_stage,gas_type)
             VALUES(default,2,STAT_STATISTICS_CODE,STAT_GAS_TYPE,2,CUR_YEAR,CUR_MONTH,null,STAT_FILL_UP_NUM,STAT_OPER_COUNT,STAT_SALE_AMOUNT,STAT_GAS_STAGE,STAT_GAS_TYPE);
        END IF;
				
				
			-- 2. 刷新年统计数据
				SELECT id INTO STAT_ID FROM dtms_refuel_statistics 
        WHERE `year` = CUR_YEAR  AND statistics_dimension = '1' and statistics_type = '2' 
        and statistics_code = STAT_STATISTICS_CODE and gas_type = STAT_GAS_TYPE and gas_stage = STAT_GAS_STAGE;

        IF STAT_ID IS NOT NULL THEN
            -- 更新操作
            UPDATE dtms_refuel_statistics 
            SET `sale_amount` = STAT_SALE_AMOUNT, 
                `fill_up_num` = STAT_FILL_UP_NUM, 
                `oper_count` = STAT_OPER_COUNT 
            WHERE id = STAT_ID;
        ELSE
            -- 新增月记录
            INSERT INTO dtms_refuel_statistics (id,statistics_type,statistics_code,goods,statistics_dimension,`year`,`month`,`day`,fill_up_num,oper_count,sale_amount,gas_stage,gas_type)
             VALUES(default,2,STAT_STATISTICS_CODE,STAT_GAS_TYPE,1,CUR_YEAR,null,null,STAT_FILL_UP_NUM,STAT_OPER_COUNT,STAT_SALE_AMOUNT,STAT_GAS_STAGE,STAT_GAS_TYPE);
        END IF;
				
        FETCH CARRIER_STATS INTO STAT_STATISTICS_CODE, STAT_GAS_TYPE,STAT_GAS_STAGE,STAT_SALE_AMOUNT,STAT_FILL_UP_NUM,STAT_OPER_COUNT;
    END WHILE;

    CLOSE CARRIER_STATS;
		
		
END;