诶~~~ 还以为万事大吉了又 有莫名其妙的错误......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
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
select bpfh from bupin_fh order by id desc limit 1改成这种形式。
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
execute stml;
set @nbpfh=nbpfh;
CREATE TABLE `bupin_fh` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bpfh` varchar(16) NOT NULL,
`bpmcc` varchar(64) CHARACTER SET utf8 NOT NULL,
`bpmcj` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
`bpdj` int(11) DEFAULT '0',
`bpdgd` int(6) DEFAULT '5',
`bpjhq` int(6) DEFAULT '6',
`adduser` varchar(16) DEFAULT 'SYSTEM' COMMENT '作操员名字',
`bptime` datetime DEFAULT NULL,
`sjfh` varchar(16) DEFAULT NULL,
`xpzks` int(11) DEFAULT '0' COMMENT '新品在库量数',
`zgpzks` int(11) DEFAULT '0' COMMENT '中古品在库数',
`zkfh` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '在库番号',
PRIMARY KEY (`id`,`bpfh`)
) ENGINE=InnoDB AUTO_INCREMENT=2127 DEFAULT CHARSET=gbk
CREATE TABLE `old_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bpfh` varchar(16) DEFAULT NULL,
`xpzks` int(11) DEFAULT NULL,
`zgpzks` int(11) DEFAULT NULL,
`zkfh` varchar(255) DEFAULT NULL,
`idate` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk每天零晨0点 开始将bupin_fh表中每行,取bpfh,xpzks,zgpzks,zkfh四个值插入old_data表中
insert into old_data(bpfh,xpzks,zgpzks,zkfh,idate)values(@nbpfh,@nxpzks,@nzgpzks,@nzkfh,@ndate);
类似这样吧~~~~~~~
那你直接运行这一句不就行了?
insert into old_data (bpfh,xpzks,zgpzks,zkfh,idate) select bpfh,xpzks,zgpzks,zkfh,idate from bupin_fh;
这样不知道行不行 至少没报错,那等一会看看它执行效果怎么样
改为如下
insert into old_data (bpfh,xpzks,zgpzks,zkfh,idate) select bpfh,xpzks,zgpzks,zkfh,now() from bupin_fh;
event_scheduler = on
才可以运行.
终于解决了 ,谢谢拉.