MySQL 事件调度

1、打开数据库连接、找到事件，右键点击新建事件

2、在定义中编写要执行的事件sql代码，在计划中设计事件调度执行的规律。

编写sql时要注意格式要求：

（1）开头要写BEGIN,结尾处写END

（2）每一个sql结束处需要打上分好，“；”，区分每一条sql语句，漏写时，事件无法保存

（3）事件示例

BEGIN

-- 推送当天门票预测数据
	DELETE FROM ylxs_dp.ylxs_jqpwfx_mpydqktj WHERE SUBSTR(sj,1,10) = (SUBSTR(NOW(),1,10));
	
	-- 添加今日数据
INSERT INTO ylxs_jqpwfx_mpydqktj (jr,jqmc,sj,jdmc,jrwj,jrjp,mr)
SELECT
	count(0) as jr,
	'玉龙雪山' as jqmc,
	orderTime as sj,
	scenicAreaName as jdmc,
	0 as jrwj,
	0 as jrjp,
	0 as mr
FROM
	ylxs_hldc.t_dm_ticket_data
	WHERE
	scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
	and SUBSTR(orderTime,1,10) = (SUBSTR(NOW(),1,10))
	GROUP BY scenicAreaName;
	
INSERT INTO ylxs_jqpwfx_mpydqktj (jr,jqmc,sj,jdmc,jrwj,jrjp)
		SELECT
	count(0) as jr,
	'玉龙雪山' as jqmc,
	orderTime as sj,
	'玉龙雪山景区门票' as jdmc,
		0 as jrwj,
	0 as jrjp
FROM
	ylxs_hldc.t_dm_ticket_data
	WHERE
	scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
	and SUBSTR(orderTime,1,10) = (SUBSTR(NOW(),1,10));
	
	-- 添加昨日数据
		UPDATE
	ylxs_jqpwfx_mpydqktj
	SET
	zr = 
	(SELECT 
		IFNULL(count(0),0) as sl
	FROM
	ylxs_hldc.t_dm_ticket_data
	WHERE
		scenicAreaName = '冰川公园索道'
	and SUBSTR(orderTime,1,10) = date_sub(SUBSTR(NOW(),1,10),interval 1 day)
	)
	WHERE SUBSTR(sj,1,10) = (SUBSTR(NOW(),1,10)) and jdmc = '冰川公园索道';
	
			UPDATE
	ylxs_jqpwfx_mpydqktj
	SET
	zr = 
	(SELECT 
		IFNULL(count(0),0) as sl
	FROM
	ylxs_hldc.t_dm_ticket_data
	WHERE
		scenicAreaName = '云杉坪索道'
	and SUBSTR(orderTime,1,10) = date_sub(SUBSTR(NOW(),1,10),interval 1 day)
	)
	WHERE SUBSTR(sj,1,10) = (SUBSTR(NOW(),1,10)) and jdmc = '云杉坪索道';
	
				UPDATE
	ylxs_jqpwfx_mpydqktj
	SET
	zr = 
	(SELECT 
		IFNULL(count(0),0) as sl
	FROM
	ylxs_hldc.t_dm_ticket_data
	WHERE
		scenicAreaName = '印象丽江'
	and SUBSTR(orderTime,1,10) = date_sub(SUBSTR(NOW(),1,10),interval 1 day)
	)
	WHERE SUBSTR(sj,1,10) = (SUBSTR(NOW(),1,10)) and jdmc = '印象丽江';
	
					UPDATE
	ylxs_jqpwfx_mpydqktj
	SET
	zr = 
	(SELECT 
		IFNULL(count(0),0) as sl
	FROM
	ylxs_hldc.t_dm_ticket_data
	WHERE
		scenicAreaName = '牦牛坪索道'
	and SUBSTR(orderTime,1,10) = date_sub(SUBSTR(NOW(),1,10),interval 1 day)
	)
	WHERE SUBSTR(sj,1,10) = (SUBSTR(NOW(),1,10)) and jdmc = '牦牛坪索道';
	
						UPDATE
	ylxs_jqpwfx_mpydqktj
	SET
	zr = 
	(		SELECT
	count(0) as sl
FROM
	ylxs_hldc.t_dm_ticket_data
	WHERE
	scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
	and SUBSTR(orderTime,1,10) = date_sub(SUBSTR(NOW(),1,10),interval 1 day)
	)
	WHERE SUBSTR(sj,1,10) = (SUBSTR(NOW(),1,10)) and jdmc = '玉龙雪山景区门票';
	
	-- 添加玉龙雪山明日预测数据
							UPDATE
	ylxs_jqpwfx_mpydqktj
	SET
	mr = 
	(		SELECT
	tomorrow_max_value as sl
FROM
	hl_data_predict.kl_predict_data
	WHERE
	SUBSTR(create_date,1,10)  = SUBSTR(NOW(),1,10) ORDER BY SUBSTR(create_date,1,19) desc LIMIT 0,1
	)
	WHERE SUBSTR(sj,1,10) = SUBSTR(NOW(),1,10) and jdmc = '玉龙雪山景区门票';
	
	-- 添加今日检票数
	UPDATE ylxs_jqpwfx_mpydqktj 
SET jrjp = (
	SELECT
	SUM(jps) as jrjp
FROM
	ylxs_dp.ylxs_jqpwfx_jdjpsdqs
	WHERE
	SUBSTR(sj,1,10) = SUBSTR(NOW(),1,10)
	and jdmc = '玉龙雪山'
	and jrzr = '1'
	) 
WHERE
	SUBSTR( sj, 1, 10 ) = SUBSTR( NOW(), 1, 10 ) 
	AND jdmc = '玉龙雪山景区门票';
	
	-- 添加今日未检票数

UPDATE ylxs_jqpwfx_mpydqktj 
SET jrwj =  ABS((
SELECT 
COUNT(distinct touristCardNum) as sl
FROM 
ylxs_hldc.t_dm_ticket_data
WHERE
SUBSTR(travelTime,1,10) = SUBSTR(NOW(),1,10)
and scenicAreaName in ('冰川公园索道','云杉坪索道','印象丽江','牦牛坪索道','玉龙雪山景区门票')
	)-(	SELECT
	SUM(jps) as jrjp
FROM
	ylxs_dp.ylxs_jqpwfx_jdjpsdqs
	WHERE
	SUBSTR(sj,1,10) = SUBSTR(NOW(),1,10)
	and jdmc = '玉龙雪山'
	and jrzr = '1'
	))
WHERE
	SUBSTR( sj, 1, 10 ) = SUBSTR( NOW(), 1, 10 ) 
	AND jdmc = '玉龙雪山景区门票'; 
	
END

 （4）在计划中设置事件执行规律

 

 注意：一定要写事件开始时间，且要精确到秒钟，否则无法保存该事件

4、适用场景

1、对于某些需要实时更新的数据，我们可以针对业务需求，单独建立一张结果表，编写事件定时更新，而不需要在接口请求中，每次去获取原表的最新数据。

2、对于数据量特别大的表，哪怕加了索引，优化了sql，执行查询时间也严重偏长，我们可以以不同的时间维度分组，或者根据业务需求分租，定时推送到结果表中，同时，还可以设置数据库的读、取时间，来防止事件执行时间过长，数据库连接宕机

oracle.net.READ_TIMEOUT=30000

oracle.jdbc.ReadTimeout=30000

5、通过列表查看时间最近一次执行时间

6、查看事件是否开启

SHOW VARIABLES LIKE 'event_scheduler'

//开启命令-查询中运行
set global event_scheduler = on;

7、设置当前事件开启

SET GLOBAL event_scheduler = 1; 

8、好处

当我们把一些业务逻辑通过编写事件、搭建结果表、sql函数等方式放在sql中去实现后，业务代码中执行，可以一定程度上减少接口的响应时间，提升项目性能以及用户体验感。

 9、防止数据库连接池耗尽的超时时间设置

中文社区 (MOSC)

中间件 (MOSC)

0 Replies

Last updated on March 27, 2015

1 WebLogic Server自动回收使用超时的连接

WebLogic Server 数据源的配置项: Admin Console -> JDBC Data Source: Configuration: Connection Pool -> "Inactive Connection Timeout"

设置为大于0的秒数。WebLogic Server 将自动检测出使用时间过长的连接并强制收回, 输出警告信息到日志文件:BEA-001153: Forcibly releasing inactive connection. 占用时间过长的连接分为两种情形: 一是应用程序有bug, 没有调用javax.sql.Connection.close()归还连接, 另一种是长时间执行SQL一直没有完成。

2 设置 JDBC SQL Statement Timeout

WebLogic Server 数据源的配置项: Admin Console -> JDBC Data Source: Configuration: Connection Pool -> "Statement Timeout"

设置为大于0的秒数。当SQL语句执行时间过长, 超过这个时间时Oracle JDBC驱动程序会抛出java.sql.SQLException: ORA-01013: user requested cancel of current operation. 这个特性依赖于JDBC驱动程序如何实现java.sql.Statement.setQueryTimeout()方法。

3 JDBC driver Socket Timeout

WebLogic Server 数据源的配置项: Admin Console -> JDBC Data Source: Configuration: Connection Pool, 在"Properties"输入框输入属性名字和值，每行一个。例如 Oracle JDBC driver 支持的:

oracle.net.READ_TIMEOUT=30000

oracle.jdbc.ReadTimeout=30000

当 Socket 操作因为网络问题或者数据库服务器端没有回应导致超过这个时间, Oracle JDBC driver 会抛出 java.sql.SQLRecoverableException:To view full details, sign in with your My Oracle Support Community account.

Don't have a My Oracle Support

Community account? Click to get started!
 

10、MySQL获取当前日期是第几周

	set @d=SUBSTR(NOW(),1,10);

select (day(@d)+WEEKDAY(@d-interval day(@d) day)) div 7;