MYSQL加触发器,在插入记录到一个表tb之前,比较表中是否有相同记录(col2相同的情况下,col3也相同,就算是相同记录)
如果没有,就插入记录代码CREATE TRIGGER no_duplicates_in_tb tb
BEFORE INSERT ON tb
FOR EACH ROW BEGIN
IF (NOT EXISTS (SELECT tb.f3
FROM tb
WHERE tb.f3 = NEW.f3 and tb.f2 = NEW.f2))
INSERT INTO tb(f1, f2, f3)
VALUES (NEW.f1, NEW.f2, NEW.f3)
END提示有语法错误。。求教怎么改!
如果没有,就插入记录代码CREATE TRIGGER no_duplicates_in_tb tb
BEFORE INSERT ON tb
FOR EACH ROW BEGIN
IF (NOT EXISTS (SELECT tb.f3
FROM tb
WHERE tb.f3 = NEW.f3 and tb.f2 = NEW.f2))
INSERT INTO tb(f1, f2, f3)
VALUES (NEW.f1, NEW.f2, NEW.f3)
END提示有语法错误。。求教怎么改!
DELIMITER $$
CREATE TRIGGER no_duplicates_in_tb
BEFORE INSERT ON tb
FOR EACH ROW BEGIN
SELECT COUNT(*) INTO @aa FROM tb WHERE tb.f3=new.f3 AND tb.f2 = NEW.f2;
IF @aa>=1 THEN
INSERT INTO tb(f1, f2, f3)
VALUES (NEW.f1, NEW.f2, NEW.f3);
ELSE
INSERT INTO dd VALUES(1);可以执行1个错误的SQL语句
END IF;
END$$
DELIMITER ;
然后直接用insert ignore into 插入数据即可 不用触发器