mysql 查看事件状态语句和修改办法

1、查看所有事件

show events

        

2、 查看事件状态

SHOW VARIABLES LIKE 'event_scheduler'

ON 事件开启

OFF 事件停止

3、更改事件状态

SET GLOBAL event_scheduler =1;
SET GLOBAL event_scheduler =ON;

4、创建事件 

 

BEGIN
	
	-- 售票数据结果表推送
	
	-- 当日数据归集
	DELETE FROM ylxs_dp.ylxs_jqpwfx_mpcsqs WHERE SUBSTR(sj,1,10) = (SUBSTR(NOW(),1,10)) and sjlx ='3';
	INSERT INTO ylxs_dp.ylxs_jqpwfx_mpcsqs (sl,jqmc,sj,jdmc,sjsj,sjlx)
	SELECT
	count(0) as sl,
	'玉龙雪山' as jqmc,
	orderTime as sj,
	scenicAreaName as jdmc,
	NOW() as sjsj,
	3 as sjlx
FROM
	ylxs_hldc.t_dm_ticket_data
	WHERE
	scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
	and SUBSTR(orderTime,1,10) = (SUBSTR(NOW(),1,10))
	GROUP BY scenicAreaName,SUBSTR(orderTime,1,13);
	
		INSERT INTO ylxs_dp.ylxs_jqpwfx_mpcsqs (sl,jqmc,sj,jdmc,sjsj,sjlx)
	SELECT
	count(0) as sl,
	'玉龙雪山' as jqmc,
	orderTime as sj,
	'玉龙雪山景区门票' as jdmc,
	NOW() as sjsj,
	3 as sjlx
FROM
	ylxs_hldc.t_dm_ticket_data
	WHERE
	scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
	and SUBSTR(orderTime,1,10) = (SUBSTR(NOW(),1,10))
	GROUP BY SUBSTR(orderTime,1,13);
	
	
	-- 当月数据归集
	DELETE FROM ylxs_dp.ylxs_jqpwfx_mpcsqs WHERE SUBSTR(sj,1,7) = (SUBSTR(NOW(),1,7)) and sjlx ='2';
	INSERT INTO ylxs_dp.ylxs_jqpwfx_mpcsqs (sl,jqmc,sj,jdmc,sjsj,sjlx)
	SELECT
	count(0) as sl,
	'玉龙雪山' as jqmc,
	orderTime as sj,
	scenicAreaName as jdmc,
	NOW() as sjsj,
	2 as sjlx
FROM
	ylxs_hldc.t_dm_ticket_data
	WHERE
	scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
	and SUBSTR(orderTime,1,7) = (SUBSTR(NOW(),1,7))
	GROUP BY scenicAreaName,SUBSTR(orderTime,1,10);
	
			INSERT INTO ylxs_dp.ylxs_jqpwfx_mpcsqs (sl,jqmc,sj,jdmc,sjsj,sjlx)
	SELECT
	count(0) as sl,
	'玉龙雪山' as jqmc,
	orderTime as sj,
	'玉龙雪山景区门票' as jdmc,
	NOW() as sjsj,
	2 as sjlx
FROM
	ylxs_hldc.t_dm_ticket_data
	WHERE
	scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
	and SUBSTR(orderTime,1,7) = (SUBSTR(NOW(),1,7))
	GROUP BY SUBSTR(orderTime,1,10);
	
	
	
	
	-- 本年数据归集
	DELETE FROM ylxs_dp.ylxs_jqpwfx_mpcsqs WHERE SUBSTR(sj,1,4) = (SUBSTR(NOW(),1,4)) and sjlx ='1';
	INSERT INTO ylxs_dp.ylxs_jqpwfx_mpcsqs (sl,jqmc,sj,jdmc,sjsj,sjlx)
	SELECT
	count(0) as sl,
	'玉龙雪山' as jqmc,
	orderTime as sj,
	scenicAreaName as jdmc,
	NOW() as sjsj,
	1 as sjlx
FROM
	ylxs_hldc.t_dm_ticket_data
	WHERE
	scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
	and SUBSTR(orderTime,1,4) = (SUBSTR(NOW(),1,4))
	GROUP BY scenicAreaName,SUBSTR(orderTime,1,7);
	
	INSERT INTO ylxs_dp.ylxs_jqpwfx_mpcsqs (sl,jqmc,sj,jdmc,sjsj,sjlx)
	SELECT
	count(0) as sl,
	'玉龙雪山' as jqmc,
	orderTime as sj,
	'玉龙雪山景区门票' as jdmc,
	NOW() as sjsj,
	1 as sjlx
FROM
	ylxs_hldc.t_dm_ticket_data
	WHERE
	scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
	and SUBSTR(orderTime,1,4) = (SUBSTR(NOW(),1,4))
	GROUP BY SUBSTR(orderTime,1,7);
	
	END