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

Question

I first found this article through a Google search, How to write an INSERT if NOT EXISTS query in standard SQL, which discusses mutually exclusive 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 the

field guarantee that insert will fail if it already exists?

Looks like is just a constraint, and when I issue the insert via PHP, the script rattles.

Answer 1

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).

Answer 2

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: