我有一个叫product的表,为这个表建了一个触发器,触发器如下(略有改动):
DELIMITER $$CREATE
TRIGGER `productTrigger` AFTER INSERT ON `product`
FOR EACH ROW BEGIN
declare bookingtime varchar(15);
declare firstcomma int(2);
declare secondcomma int(2);
declare tmp varchar(500);
declare newcontent varchar(500);
declare i int(2) DEFAULT 1;
declare tmpIndex int(2);
declare param varchar(50);
declare judge BOOLEAN DEFAULT true;
declare required0 varchar(10);
declare required1 varchar(10);
declare codebaseCount int(1) DEFAULT 0;
set newcontent = concat(new.content,'&');
set firstcomma = LOCATE(',', newcontent);
set secondcomma = LOCATE(',', newcontent, firstcomma+1);
if firstcomma > 0 and secondcomma >0 then
set bookingtime = SUBSTRING(newcontent, 1, firstcomma-1);
set tmp = SUBSTRING(newcontent, secondcomma+1);
while (i>1 or judge) do
set tmpIndex = LOCATE('&', tmp, i);
set param = SUBSTRING(tmp, i, tmpIndex-i);
if(LOCATE('required0=',param)>0) then
set required0 = SUBSTRING(param, LOCATE('=',param)+1);
elseif(LOCATE('required1=',param)>0) then
set required1 = SUBSTRING(param, LOCATE('=',param)+1);
end if;
set i = tmpIndex+1;
set judge = false;
end while;
if length(required0)>0 then
select count(*) into codebaseCount from test where `required0`=required0;
if codebaseCount = 1 then
insert into infonew (`required0`,`required1`,`TIMESTAMP`) values (required0,required1,bookingtime);
end if;
end if;
end if;
END;
$$DELIMITER ;只要是对字段进行一些字符串的拆分,然后将拆分内容插入到另外的一个表里。
现在的情况是,我一旦建立了这个trigger,product表的数据会被不停的重复插入,注意是product表,不是infonew这个表,而且是mysql自己在不停的插入,哪位神仙遇到过这种情况呀!
DELIMITER $$CREATE
TRIGGER `productTrigger` AFTER INSERT ON `product`
FOR EACH ROW BEGIN
declare bookingtime varchar(15);
declare firstcomma int(2);
declare secondcomma int(2);
declare tmp varchar(500);
declare newcontent varchar(500);
declare i int(2) DEFAULT 1;
declare tmpIndex int(2);
declare param varchar(50);
declare judge BOOLEAN DEFAULT true;
declare required0 varchar(10);
declare required1 varchar(10);
declare codebaseCount int(1) DEFAULT 0;
set newcontent = concat(new.content,'&');
set firstcomma = LOCATE(',', newcontent);
set secondcomma = LOCATE(',', newcontent, firstcomma+1);
if firstcomma > 0 and secondcomma >0 then
set bookingtime = SUBSTRING(newcontent, 1, firstcomma-1);
set tmp = SUBSTRING(newcontent, secondcomma+1);
while (i>1 or judge) do
set tmpIndex = LOCATE('&', tmp, i);
set param = SUBSTRING(tmp, i, tmpIndex-i);
if(LOCATE('required0=',param)>0) then
set required0 = SUBSTRING(param, LOCATE('=',param)+1);
elseif(LOCATE('required1=',param)>0) then
set required1 = SUBSTRING(param, LOCATE('=',param)+1);
end if;
set i = tmpIndex+1;
set judge = false;
end while;
if length(required0)>0 then
select count(*) into codebaseCount from test where `required0`=required0;
if codebaseCount = 1 then
insert into infonew (`required0`,`required1`,`TIMESTAMP`) values (required0,required1,bookingtime);
end if;
end if;
end if;
END;
$$DELIMITER ;只要是对字段进行一些字符串的拆分,然后将拆分内容插入到另外的一个表里。
现在的情况是,我一旦建立了这个trigger,product表的数据会被不停的重复插入,注意是product表,不是infonew这个表,而且是mysql自己在不停的插入,哪位神仙遇到过这种情况呀!
难道有一条数据插入失败会导致mysql重复插入吗?