mysql - 如何在 MySQL 中执行 'insert if not exists'？

Question

我从谷歌搜索开始并找到了文章 How to write INSERT if NOT EXISTS queries in standard SQL 讨论互斥表。

我有一张包含约 1400 万条记录的表。如果我想以相同的格式添加更多数据，有没有办法确保我要插入的记录在不使用一对查询的情况下不存在(即，一个要检查的查询和一个要插入的查询是结果集是空)？

字段上的 unique 约束是否保证 insert 已经存在时会失败？

似乎只有 一个约束，当我通过 PHP 发出插入时，脚本会发出嘶哑的声音。

Answer 1

使用 INSERT IGNORE INTO 表。

还有 INSERT … ON DUPLICATE KEY UPDATE 语法，您可以在 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement 中找到解释.

---

来自 bogdan.org.ua 的帖子根据 Google's webcache :

18th October 2007

To start: as of the latest MySQL, syntax presented in the title is notpossible. But there are several very easy ways to accomplish what isexpected using existing functionality.

There are 3 possible solutions: using INSERT IGNORE, REPLACE, orINSERT … ON DUPLICATE KEY UPDATE.

Imagine we have a table:

CREATE TABLE `transcripts` (
`ensembl_transcript_id` varchar(20) NOT NULL,
`transcript_chrom_start` int(10) unsigned NOT NULL,
`transcript_chrom_end` int(10) unsigned NOT NULL,
PRIMARY KEY (`ensembl_transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now imagine that we have an automatic pipeline importing transcriptsmeta-data from Ensembl, and that due to various reasons the pipelinemight be broken at any step of execution. Thus, we need to ensure twothings:

1. repeated executions of the pipeline will not destroy our> database

2. repeated executions will not die due to ‘duplicate> primary key’ errors.

Method 1: using REPLACE

It’s very simple:

REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

If the record exists, it will be overwritten; if it does not yetexist, it will be created. However, using this method isn’t efficientfor our case: we do not need to overwrite existing records, it’s finejust to skip them.

Method 2: using INSERT IGNORE Also very simple:

INSERT IGNORE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

Here, if the ‘ensembl_transcript_id’ is already present in thedatabase, it will be silently skipped (ignored). (To be more precise,here’s a quote from MySQL reference manual: “If you use the IGNOREkeyword, errors that occur while executing the INSERT statement aretreated as warnings instead. For example, without IGNORE, a row thatduplicates an existing UNIQUE index or PRIMARY KEY value in the tablecauses a duplicate-key error and the statement is aborted.”.) If therecord doesn’t yet exist, it will be created.

This second method has several potential weaknesses, includingnon-abortion of the query in case any other problem occurs (see themanual). Thus it should be used if previously tested without theIGNORE keyword.

Method 3: using INSERT … ON DUPLICATE KEY UPDATE:

Third option is to use INSERT … ON DUPLICATE KEY UPDATEsyntax, and in the UPDATE part just do nothing do some meaningless(empty) operation, like calculating 0+0 (Geoffray suggests doing theid=id assignment for the MySQL optimization engine to ignore thisoperation). Advantage of this method is that it only ignores duplicatekey events, and still aborts on other errors.

As a final notice: this post was inspired by Xaprb. I’d also advise toconsult his other post on writing flexible SQL queries.