下面是mysql的定时任务
create event if not exists rpt_stat
on schedule every 1 day
starts timestamp '2010-09-01 02:00:00'
on completion not preserve
do call pr_rpt_all(date_format(current_date-1 ,'%Y.%m.%d'));-----------event_scheduler on----------
create event if not exists rpt_stat
on schedule every 1 day
starts timestamp '2010-09-01 02:00:00'
on completion not preserve
do call pr_rpt_all(date_format(current_date-1 ,'%Y.%m.%d'));-----------event_scheduler on----------
解决方案 »
- 一个远程访问MYSQL的问题
- mysql in 排序
- windows 2003上安装MySQL5.1.35报错
- 怎么删除最后的10万条数据?速度希望能尽可能快.
- 进入MySql
- 请教Mysql安装好后,但是不能启动,该如何解决?
- 请高手指点!在线等
- 请问我的PHP老是无法连接数据库MySql为什么呢???
- 用Mysql编过桌面应用程序的兄弟们,你们的Mysql应用程序在启动时,是否一定要手动启动Mysql Server呢?我的应用程序为什么一启动就要我填写一个对话框,并启动Mysql Server,fo=
- sql语句在mysql cluster执行时间过长?
- MySQL乱码问题,急!
- MySQL重装到start service无法响应
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.00 sec)mysql>
mysql> show events;
+------+----------+----------------+-----------+-----------+------------+-------
---------+----------------+---------------------+------+---------+------------+-
---------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interv
al value | Interval field | Starts | Ends | Status | Originator |
character_set_client | collation_connection | Database Collation |
+------+----------+----------------+-----------+-----------+------------+-------
---------+----------------+---------------------+------+---------+------------+-
---------------------+----------------------+--------------------+
| csdn | rpt_stat | root@localhost | SYSTEM | RECURRING | NULL | 1
| DAY | 2010-09-01 02:00:00 | NULL | ENABLED | 0 |
latin1 | latin1_swedish_ci | latin1_swedish_ci |
+------+----------+----------------+-----------+-----------+------------+-------
---------+----------------+---------------------+------+---------+------------+-
---------------------+----------------------+--------------------+
1 row in set (0.02 sec)mysql>
然后通过定时任务来 定时调用存错过程 但是 存储过程里面的时间维表 全部为NULL
@p_day varchar --日期类型参数 yyyy.mm.dd
)as
begin
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 00:00:00',@p_day+' 00:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 01:00:00',@p_day+' 01:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 02:00:00',@p_day+' 02:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 03:00:00',@p_day+' 03:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 04:00:00',@p_day+' 04:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 05:00:00',@p_day+' 05:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 06:00:00',@p_day+' 06:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 07:00:00',@p_day+' 07:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 08:00:00',@p_day+' 08:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 09:00:00',@p_day+' 09:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 10:00:00',@p_day+' 10:59:59',@p_day,substring(@p_day,1,7));
insert into rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 11:00:00',@p_day+' 11:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 12:00:00',@p_day+' 12:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 13:00:00',@p_day+' 13:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 14:00:00',@p_day+' 14:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 15:00:00',@p_day+' 15:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 16:00:00',@p_day+' 16:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 17:00:00',@p_day+' 17:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 18:00:00',@p_day+' 18:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 19:00:00',@p_day+' 19:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 20:00:00',@p_day+' 20:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 21:00:00',@p_day+' 21:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 22:00:00',@p_day+' 22:59:59',@p_day,substring(@p_day,1,7));
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 23:00:00',@p_day+' 23:59:59',@p_day,substring(@p_day,1,7));
commit;
end;
类似于这个的表 然后传参数 插入值
CONCAT(@p_day+' 18:00:00')
这样。http://blog.chinaunix.net/u3/116107/showart.php?id=2307078这里介绍一个工具,可以批量把MSSQL的存储过程转成MYSQL的。
解决方法,把所有的 @p_day 替换成 p_day
另外 MYSQL的字符串加连也不是 'abcd' + 'efgh' ,MYSQL中需要使用 concat
insert into dbo.rpt_datetime(start_time,end_time,d_day,d_month)
values(@p_day+' 23:00:00',@p_day+' 23:59:59',@p_day,substring(@p_day,1,7));这句能用。 之后再改成存储过程。 确认存储过程没有问题后再 写你的EVENT。 否则你以为是你的EVENT有什么问题,结果最终只是SQL语句根本不对。
create event if not exists rpt_stat
on schedule every 1 day
starts timestamp '2010-09-01 02:00:00'
on completion not preserve
do call pr_rpt_all(date_format(current_date-1 ,'%Y.%m.%d'));目的就是 按时执行我写的存储过程 但是 我运行后 查询时间那个表,结果表中没有数据
date_format(current_date-1 ,'%Y.%m.%d')是参数 系统前一天
出来的数据是
create procedure pr_rpt_daycode(
in p_day varchar(10)
)begin
declare v_month varchar(7);
set v_month=substr(p_day,1,7);
delete from rpt_daycode where stat_day=p_day;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 00:00:00'),concat(p_day,' 00:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 01:00:00'),concat(p_day,' 01:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 02:00:00'),concat(p_day,' 02:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 03:00:00'),concat(p_day,' 03:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 04:00:00'),concat(p_day,' 04:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 05:00:00'),concat(p_day,' 05:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 06:00:00'),concat(p_day,' 06:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 07:00:00'),concat(p_day,' 07:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 08:00:00'),concat(p_day,' 08:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 09:00:00'),concat(p_day,' 09:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 10:00:00'),concat(p_day,' 10:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 11:00:00'),concat(p_day,' 11:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 12:00:00'),concat(p_day,' 12:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 13:00:00'),concat(p_day,' 13:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 14:00:00'),concat(p_day,' 14:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 15:00:00'),concat(p_day,' 15:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 16:00:00'),concat(p_day,' 16:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 17:00:00'),concat(p_day,' 17:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 18:00:00'),concat(p_day,' 18:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 19:00:00'),concat(p_day,' 19:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 20:00:00'),concat(p_day,' 20:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 21:00:00'),concat(p_day,' 21:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 22:00:00'),concat(p_day,' 22:59:59'),p_day,v_month;
insert into rpt_daycode(stat_stime,stat_etime,stat_day,stat_month)
select concat(p_day,' 23:00:00'),concat(p_day,' 23:59:59'),p_day,v_month;
commit;
end;
create procedure pr_rpt_all(
in p_day varchar(10)
)
begin
call pr_rpt_daycode(p_day);
end;
另外你的图大家看不到。 建议能象#1楼那样直接贴文本。
create table rpt_daycode(
stat_stime varchar(19) not null,
stat_etime varchar(19) not null,
stat_day varchar(10) not null,
stat_month varchar(10) not null
)type=innodb default charset=gbk;
/*开启功能*/
SET GLOBAL event_scheduler = ON;DELIMITER //
DROP EVENT IF EXISTS `eventlog`;
CREATE EVENT IF NOT EXISTS `eventlog`ON SCHEDULE
EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE,'09:00:00')
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
DELETE FROM TAB_LOG WHERE TAB_LOG.DT_TIME < DATE_ADD(NOW(),INTERVAL "0" DAY);
END;
//
DELIMITER ;
请问,我每天9点执行删除3天前的日志表记录,怎么在第二天不执行呢?
环境是Linux下的mysql数据库,我测试方法是:将linux下的date时间改成8:58分 然后mysql数据创建了event定时执行,即以上sql代码,创建的sql脚本没有问题,单独测试过语句,另外我将每天执行(EVERY 1 DAY)改成每分钟执行都能正常执行,但是用我修改Linux时间去模拟每天执行,却执行不了啊~~~求解释……等待中……