How to implement "insert if not exists" operation in MySQL?

Question

I first found this article How to Write an INSERTifNOTEXISTS Query in Standard SQL by googling, which talks about mutex tables. I have a table with about 14 million records. If I want to add more data in the same format, is there a way to ensure that the record I want to insert does not exist without using a pair of queries (i.e. one query to check and one query to insert, with the result set is empty)? Does the unique constraint on a field guarantee that insert will fail if it already exists? Looks like just a constraint when I pass PH

P粉338969567 · Answer

solution:

INSERT INTO `table` (`value1`, `value2`) 
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL 
WHERE NOT EXISTS (SELECT * FROM `table` 
      WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1)

illustrate:

Innermost query

SELECT * FROM `table` 
      WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1

Used as a WHERE NOT EXISTS condition to detect whether a row containing the data to be inserted already exists. The query may stop after finding a row of this class, so LIMIT 1 (micro-optimization, can be omitted).

Intermediate query

SELECT 'stuff for value1', 'stuff for value2' FROM DUAL

represents the value to be inserted. DUAL refers to a special single row single table that exists by default in all Oracle databases (see https://en.wikipedia.org/wiki/DUAL_table). On MySQL-Server version 5.7.26, I get valid queries when FROM DUAL is omitted, but older versions (like 5.5.60) seem to require the FROM information. By using WHERE NOT EXISTS, if the innermost query finds matching data, the intermediate queries will return an empty result set.

External query

INSERT INTO `table` (`value1`, `value2`)

Insert data (if intermediate query returns any data).

P粉521013123 · Answer

Use INSERT IGNORE INTO table.

There is also INSERT … ON DUPLICATE KEY UPDATE syntax, which you can use in 13.2.6.2 INSERT … ON DUPLICATE KEY UPDATE statement .

Post from bogdan.org.ua According to Google's web cache: