如何在mysql中插入多个值并避免重复

How would I insert multiple rows or values and avoid duplicates in the following schema.

如何插入多个行或值，并避免在下面的模式中重复。

table schema is

表模式是

id,subject1,subject2,subject3

id is auto incremented.
A duplicate would be where all subject1,subject2,subject3 already exist in a record in the exact same order.

id是自动递增。一个副本是指所有的subject1,subject2,subject3已经以完全相同的顺序存在于一个记录中。

INSERT INTO "table_name" ("subject1","subject2","subject3")  
VALUES ("cats", "dogs", "hamsters")  
VALUES ("squirrels", "badgers", "minxes")  
VALUES ("moose", "deer", "ocelots")

In the table let's say I already have a record for

在表格中假设我已经有一个记录

id,subject1,subject2,subject3
1,"cats", "dogs", "hamsters"

so I want it to just insert

我想让它插入

VALUES ("squirrels", "badgers", "minxes")  
VALUES ("moose", "deer", "ocelots")

I've seen answers about avoiding duplicates for single items, but not for 3.

我看到过关于避免重复的答案，但不是3。

3 个解决方案

#1

You want to add the UNIQUE constraint to your table. If you write the UNIQUE constraint out separately, it becomes clearer how to apply it to arbitrary combinations of columns.

您想要将唯一的约束添加到您的表中。如果将唯一约束单独写出来，就可以更清楚地知道如何将它应用于任意的列组合。

CREATE TABLE table_name (
    subject1 VARCHAR(30),
    subject2 VARCHAR(30),
    subject3 VARCHAR(30),
    UNIQUE (subject1, subject2, subject3)
);

#2

You need to get around with the unique key on three columns.

您需要在三列上使用唯一的键。

Example of table definition

表定义的例子

CREATE TABLE `table_name` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'PK',
  `subject1` varchar(64) NOT NULL,
  `subject2` varchar(64) NOT NULL,
  `subject3` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `subjects` (`subject1`,`subject1`, `subject3`)
) ENGINE=InnoDB

#3

To add the constraint in now:

现在加入约束:

ALTER TABLE {tablename}
ADD CONSTRAINT {constraintname} UNIQUE (subject1, subject2, subject3)

#1