如果列的组合（允许重复）是唯一的，则MySQL仅插入新行 [英] MySQL only insert new row if combination of columns (which allow duplicates) is unique

问题描述

由于MySQL不支持IF EXISTS，我很难想到在MySQL中执行类似下面伪操作的语法：

Since IF EXISTS isn't supported by MySQL I am struggling to think of the syntax for doing something like the following pseudo in MySQL:

IF ((select count(*) from table where col1='var1' AND col2='var2' AND col3='var3' AND col4='var4' AND col5='var5')>0) then
    combination of vars exist in record - do a thing;
ELSE
    combination of vars does not exist in record - insert record;
END IF;

我应该认为CASE会适合这种情况，但对于我的生活，我无法想到正确的语法。我使用唯一索引，但每个列都需要允许重复，它只是所有列的匹配组合，需要是唯一的，而不是字段本身。

I should have thought CASE would suit this but for life of me I'm unable to think of the correct syntax. I'd use unique indexes but every one of the columns needs to allow duplicates, it's only a matching combination of all the columns that needs to be unique, not the fields themselves.

我被告知在所有列中使用复合键可以避免重复插入但是从我收集的内容中我需要使用不同的插入。

I'm told using a composite key across all the columns would avoid duplicate inserts but from what I gather I need to use a different insert then.

短版本：在MySQL中，如果在现有行中找不到精确指定的列匹配，如何插入新行。

Short version: In MySQL, how do I insert new row only if an exact specified match of columns is not found in an existing row.

任何建议或建议将不胜感激。

Any advice or suggestions would be greatly appreciated.

推荐答案

创建复合唯一索引。这将允许各个字段中的任意数量的重复，但组合必须是唯一的。

Create a composite unique index. This will allow any number of duplicates in the individual fields, but the combination needs to be unique.

CREATE UNIQUE INDEX ix_uq ON test (field1, field2, field3);

...并使用 INSERT IGNORE 来如果未违反唯一索引，则插入。如果是，则忽略插入。

...and use INSERT IGNORE to insert if the unique index is not violated. If it is, just ignore the insert.

INSERT IGNORE INTO test (field1,field2,field3) VALUES (1,1,1);

一个用于测试的SQL助手。

如果你想插入除非有重复，并且有更新，你也可以使用 INSERT INTO ... ON DUPLICATE KEY UPDATE ;

If you want to insert unless there's a duplicate, and update if there is, you can also use INSERT INTO ... ON DUPLICATE KEY UPDATE;

INSERT INTO test (field1, field2, field3) VALUES (1,1,1)
  ON DUPLICATE KEY UPDATE field4=field4+1;

另一个SQLfiddle 。

这篇关于如果列的组合（允许重复）是唯一的，则MySQL仅插入新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！