MySQL在不存在的情况下根据3个值插入记录

Question

我需要在目标表中不存在记录的情况下向 MySQL 进行插入

我的查询

INSERT INTO comment (`mid`, `pid`, `comment_text`, `comment_date`, `comment_type`) 
 VALUES (180, 2, NULL, '2012-07-26 10:19:00', 'tag')  WHERE NOT EXISTS ( SELECT * FROM `comment` WHERE `mid`=180 AND `pid`=2 AND `comment_type`='tag')

但是当运行时我收到此错误

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 'WHERE NOT EXISTS ( SELECT * FROM comment WHERE mid=180 AND `pid' at line 2

本质上我想阻止重复行在与值匹配时添加到此表中

Answer 1

尝试:

INSERT IGNORE INTO comment
(
 `mid`, `pid`, `comment_text`, `comment_date`, `comment_type`
)
(
    SELECT 180, 2, NULL, '2012-07-26 10:19:00', 'tag'
    FROM comment
    WHERE `mid`=180 AND
          `pid`=2 AND
          `comment_type`='tag'
    LIMIT 1
);

编辑:更好的方法:

从表中删除重复项see here

ALTER IGNORE TABLE comment ADD UNIQUE KEY ix1(mid, pid, comment_type);

INSERT IGNORE INTO comment
(
 `mid`, `pid`, `comment_text`, `comment_date`, `comment_type`
)
VALUES 
(
 SELECT 180, 2, NULL, '2012-07-26 10:19:00', 'tag'
);