如何在MySQL中创建触发器以仅在不存在时插入新行？

Question

我想要的是仅当该日期不存在时才插入新行，则应将其忽略。该表没有主键和唯一键，我在此表中使用它bar_opening_details。 我无法在此处添加这样的主要和唯一约束。INSERT IGNORE。 因为每天都会明智地输入具有相同 item_id 的项目。

CREATE definer=`root`@`localhost` TRIGGER `store`.`bar_recvd_details_after_insert` beforeINSERT
on `bar_recvd_details` FOR each row BEGIN
DECLARE mydate  date;DECLARE mydate1 DATE;DECLARE myid    INT;SELECT Max(close_date)
INTO   mydate
FROM   bar_opening_details;SELECT item_id
INTO   myid
FROM   bar_opening_details
WHERE  op_date=mydate
AND    item_id=new.item_id;SELECT op_date
INTO   mydate1
FROM   bar_opening_details
WHERE  op_date=mydate
AND    item_id=new.item_id;IF(myid != new.item_id
AND
mydate1 != mydate) then
INSERT INTO `store`.`bar_opening_details`
            (
                        `item_cid`,
                        `item_id`,
                        `op_date`,
                        `op_value`,
                        `close_date`,
                        `close_val`
            )
            VALUES
            (
                        new.item_cid,
                        new.item_id,
                        mydate,
                        '0',
                        mydate,
                        '0'
            );ENDIF;END 

Answer 1

就这样解决了

CREATE DEFINER=`root`@`localhost` TRIGGER `store`.`bar_recvd_details_BEFORE_INSERT` BEFORE INSERT ON `bar_recvd_details` FOR EACH ROW
BEGIN
declare mydate date;
SELECT max(close_date) into mydate FROM bar_opening_details;
 
 IF NOT EXISTS (SELECT 1 FROM bar_opening_details WHERE item_id = NEW.item_id and op_date=mydate) THEN
    INSERT INTO bar_opening_details (item_cid,
item_id,
op_date,
op_value,
close_date,
close_val)
    VALUES (NEW.item_cid,NEW.item_id,mydate,'0',mydate,'0');
END IF;
END