-----------已设置event_scheduler on----------
下面的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'));能定时执行pr_rpt_all这个存储过程吗?
下面的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'));能定时执行pr_rpt_all这个存储过程吗?
create procedure ...
create event...然后你现在表中的记录是什么样?一条记录都没产生?还是产生了24条记录但某些字段不是你所需要的?
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;drop procedure if exists pr_rpt_daycode;
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;
----
drop procedure if exists pr_rpt_all;
create procedure pr_rpt_all(
in p_day varchar(10)
)
begin
call pr_rpt_daycode(p_day);
end;我查出来一条数据都没有
mysql> show events;
+------+----------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db | Name | Definer | Time zone | Type | Execute at | Interval 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 |gbk | gbk_chinese_ci | latin1_swedish_ci |
+------+----------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.02 sec)mysql> show create table rpt_daycode;
+-------------+-----------------------------------------------------------------
| Table | Create Table
+-------------+-----------------------------------------------------------------
| rpt_daycode | 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
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+-----------------------------------------------------------------
1 row in set (0.00 sec)mysql> show create procedure pr_rpt_all;
+------------+----------+--------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+------------+----------+--------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| pr_rpt_all | | CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_rpt_al
l`(
in p_day varchar(10)
)
begin
call pr_rpt_daycode(p_day);
end | latin1 | latin1_swedish_ci | latin1_swedish_ci |
+------------+----------+-------------------------------------------------------
1 row in set (0.00 sec)mysql> show create procedure pr_rpt_all;