最近网站访问量增加,每天新增了很多操作记录,查询的时候一般只查询六个月的,六月之前的很少用到,就想到用mysql的定时任务,定期将数据同步到另一张表中,在这里记录一下过程,如果有不明白的地方,可以私信我喔,或者可以查看这个链接地址我觉得写得很详细:https://zhuanlan.zhihu.com/p/71735348
准备工作
开启事件调度器
查看时间调度器是否开启
show variables like '%event_scheduler%';
开启事件调度器
set global event_scheduler =1;
关闭事件调度器
set global event_scheduler =0;
创建存储过程
CREATE PROCEDURE operation_record_archive()
BEGIN
/*
1.先将六个月之前的数据转移到历史表中
2.删除操作记录表六个月之前的数据
*/
INSERT INTO operation_record_history(
`account`,
`customer_type`,
`ip_address`,
`company_id`,
`browser_code`,
`browser_type`,
`address`,
`log_time`,
`operation_time`,
`operation_type`,
`operation_address`,
`url`,
`operation_province`,
`operation_city`,
`operation_area`,
`operation_content`,
`operation_num`,
`ops_note`,
`is_delete`,
`create_time`,
`status`,
`note`,
`associated_id`,
`telephone`
)SELECT
`account`,
`customer_type`,
`ip_address`,
`company_id`,
`browser_code`,
`browser_type`,
`address`,
`log_time`,
`operation_time`,
`operation_type`,
`operation_address`,
`url`,
`operation_province`,
`operation_city`,
`operation_area`,
`operation_content`,
`operation_num`,
`ops_note`,
`is_delete`,
`create_time`,
`status`,
`note`,
`associated_id`,
`telephone`
FROM
operation_record
WHERE
is_delete = 0
and date(create_time) < date(date_add(now(),interval -6 month));
delete from operation_record where date(create_time) < date(date_add(now(),interval -6 month));
end
查看存储过程
select * from mysql.proc where db=’‘ and type=’procedure’ order by name;
创建定时任务
每天零晨三点执行
create event `operation_record_archive_event`
on schedule EVERY 1 DAY STARTS date_add(date(curdate() + 1),interval 3 hour)
do call OPERATION_RECORD_ARCHIVE_PROC();
查看定时任务
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
关闭定时任务
ALTER EVENT operation_record_archive DISABLE;