mysql定时执行查询插入数据操作

1.在数据库函数中创建一个自定义函数

2.CREATE DEFINER=`root`@`localhost` PROCEDURE `addAlarmToMesPool`()————创建一个函数

BEGIN

（中间部分就是SQL执行语句）

END

3.自定义函数中可以执行多条SQL语句 以';'区分

4.代码演示

CREATE DEFINER=`root`@`localhost` PROCEDURE `addAlarmToMesPool`()
BEGIN
INSERT INTO t_log_message_pool ( admin_id, alarm_id, print_point_name, policy_name, alarm_message, alarm_time, send_type, phone, email, state, createdate, updatetime,post_mail )
SELECT 
	sub.admin_id,
	lgam.id alarm_id,				
	ptst.print_point_name,  
	sub.NAME policy_name,
	lgam.alarm_message, 		
	lgam.alarm_time,	
	sub.send_type,
	sub.phone,
	sub.email,
	0 state,
	NOW( ) createdate,
	NOW( ) updatetime,
	sub.post_email	
FROM 
	t_log_alarm lgam
		INNER JOIN t_s_printstation ptst ON lgam.station_id = ptst.id
		INNER JOIN (
			SELECT 
				sbsb.admin_id,						
				admin.mobile_phone phone,	
				admin.mail email,					
				sbsb.member_type,					
				sbsb.member_id,						
				sbsb.send_type,						
				sbsb.updatedate,					
				tspt.NAME,								
				ptam.type,								
				ptam.alarm_code,					
				sbsb.email post_email
			FROM
				t_s_subscribe sbsb
				INNER JOIN t_s_policytype tspt ON sbsb.policytype_id = tspt.id
				INNER JOIN t_s_policytype_alarm ptam ON tspt.id = ptam.policytype_id
				INNER JOIN t_s_admin admin ON sbsb.admin_id = admin.id 
			WHERE
				sbsb.state = 1
				AND ptam.state = 1
				AND sbsb.member_type = 1
				AND tspt.type = 1
				AND admin.state = 1
		) sub ON  
				lgam.alarm_code = sub.alarm_code
				AND sub.type = 1  
				AND lgam.alarm_time >= sub.updatedate 
WHERE
	lgam.state = 1
	AND ptst.print_state <>- 1 	
GROUP BY
	lgam.id;	
UPDATE t_s_subscribe  SET updatedate = NOW( ) ;
END

5.创建定时执行时间

在数据库时间中创建时间  

5.1执行之定义函数  

5.2设置时间周期

5.3开启设置