关于事件特定时间自动记录 问题,延续~~~~
诶~~~ 还以为万事大吉了又 有莫名其妙的错误......
DELIMITER $$
CREATE EVENT aout_save
ON SCHEDULE
EVERY 1 DAY
DO
BEGIN
SET @cnt = (SELECT COUNT(*) FROM bupin_fh );
while @cnt >0 do
set @nbpfh=(select bpfh from bupin_fh limit @cnt-1,1);
set @nxpzks=(select xpzks from bupin_fh limit @cnt-1,1);
set @nzgpzks=(select zgpzks from bupin_fh limit @cnt-1,1);
set @nzkfh=(select zkfh from bupin_fh limit @cnt-1,1);
set @ndate=now();
insert into bupin_fh(bpfh,xpzks,zgpzks,zkfh,idate)values
(@nbpfh,@nxpzks,@nzgpzks,@nzkfh,@ndate);
set @cnt = @cnt - 1;
end while;
END $$
DELIMITER ;
错误如下
[SQL]
CREATE EVENT aout_save
ON SCHEDULE
EVERY 1 DAY
DO
BEGIN
SET @cnt = (SELECT COUNT(*) FROM bupin_fh );
while @cnt >0 do
set @nbpfh=(select bpfh from bupin_fh limit @cnt-1,1);
set @nxpzks=(select xpzks from bupin_fh limit @cnt-1,1);
set @nzgpzks=(select zgpzks from bupin_fh limit @cnt-1,1);
set @nzkfh=(select zkfh from bupin_fh limit @cnt-1,1);
set @ndate=now();
insert into bupin_fh(bpfh,xpzks,zgpzks,zkfh,idate)values
(@nbpfh,@nxpzks,@nzgpzks,@nzkfh,@ndate);
set @cnt = @cnt - 1;
end while;
END ;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@cnt-1,1);
set @nxpzks=(select xpzks from bupin_fh limit @cnt-1,1);
set @n' at line 8
[解决办法]
insert into old_data (bpfh,xpzks,zgpzks,zkfh,idate) select bpfh,xpzks,zgpzks,zkfh,idate=now() from bupin_fh;
改为如下
insert into old_data (bpfh,xpzks,zgpzks,zkfh,idate) select bpfh,xpzks,zgpzks,zkfh,now() from bupin_fh;
