Geek asks and answers

How do I insert multiple values ​​in mysql and avoid duplicates

How to insert multiple rows or values ​​and avoid duplicates in the following diagram.

table layout

id,subject1,subject2,subject3

id is automatically incremented.
A duplicate will be where all subject1, subject2, subject3 already exist in the record in the same order.

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

In the table, you can say that I already have an entry 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 saw answers about excluding duplicates for individual elements, but not for 3.

+3
source share
3 answers

You want to add a constraint UNIQUEto the table. If you write a constraint UNIQUEseparately, it becomes clear 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
source

.

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
+1

To add a restriction currently:

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

More articles:


All Articles