如果不存在则插入 - 按所有列查找

I want to insert new row without duplicates.

我想插入没有重复的新行。

Whole idea is to insert new row only if indentical pair does not exist already, not just one key, meaning both columns must exist together to call it a duplicate.

整个想法是仅在不存在同义对时插入新行,而不仅仅是一个键,这意味着两个列必须一起存在才能将其称为重复。

 parent_id | report_date
 1         | 2015-12-15
 1         | 2015-10-10
(1         | 2015-10-10) NOT ALLOWED  
 2         | 2011-05-04

1 个解决方案

#1

Method 1: Add Unique Index

方法1:添加唯一索引

ALTER TABLE `tablename` ADD UNIQUE `unique_index`(`parent_id`, `report_date`);

Now if you tried to update, query execution will fail.

现在,如果您尝试更新,查询执行将失败。

Method 2: Check while inserting

方法2:插入时检查

Best solution is to add unique index as suggested by @pyNoob in comments. But say if you can't do that, may be worried about disk space because of indexing?, then following query will work:-

最佳解决方案是在评论中添加@pyNoob建议的唯一索引。但是如果你不能这样做,可能因为索引而担心磁盘空间?,那么下面的查询将会起作用: -

INSERT INTO tablename (parent_id, report_date)
SELECT * FROM (SELECT '1', '2015-10-10') AS tmp
WHERE NOT EXISTS (
    SELECT parent_id, report_date FROM tablename where parent_id = '1' and report_date = '2015-10-10'
) LIMIT 1;

#1