如何在 MySQL 中执行“如果不存在则插入"? [英] How can I do 'insert if not exists' in MySQL?

问题描述

我从谷歌搜索开始，找到了文章如何在讨论互斥表的标准 SQL 中编写 INSERT if NOT EXISTS 查询.

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

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

似乎仅仅是一个约束，当我通过 PHP 发出插入时，脚本会发出咝咝声.

解决方案

使用INSERT IGNORE INTO table.

还有 INSERT ... ON DUPLICATE KEY UPDATE 语法，您可以在 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE 语句.

---

来自 bogdan.org.ua 根据 Google 的网络缓存:

<块引用>

2007 年 10 月 18 日

开始:从最新的 MySQL 开始，标题中的语法不是可能的.但是有几种非常简单的方法可以完成预计使用现有功能.

有 3 种可能的解决方案:使用 INSERT IGNORE、REPLACE 或插入……在重复的密钥更新中.

假设我们有一张桌子:

创建表`成绩单`(`ensembl_transcript_id` varchar(20) 非空，`transcript_chrom_start` int(10) 无符号非空，`transcript_chrom_end` int(10) 无符号非空，主键(`ensembl_transcript_id`)) 引擎=InnoDB 默认字符集=latin1;

现在假设我们有一个自动导入成绩单的管道来自 Ensembl 的元数据，以及由于各种原因管道可能会在执行的任何步骤中被破坏.因此，我们需要确保两个事情:

<块引用>

1. 重复执行管道不会破坏我们的>数据库

<块引用>

2. 重复执行不会因为重复"而死亡>主键的错误.

方法一:使用REPLACE

很简单:

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

如果记录存在，将被覆盖；如果还没有存在，它将被创建.但是，使用这种方法效率不高对于我们的案例:我们不需要覆盖现有记录，这很好只是为了跳过它们.

方法二:使用INSERT IGNORE 也很简单:

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

这里，如果‘ensembl_transcript_id’已经存在于数据库，它将被悄悄跳过(忽略).(更准确地说，这是 MySQL 参考手册中的一段话:如果你使用 IGNORE关键字，执行 INSERT 语句时发生的错误是改为作为警告处理.例如，没有 IGNORE，一行复制表中现有的 UNIQUE 索引或 PRIMARY KEY 值导致重复键错误并且语句被中止.".)如果记录尚不存在，它将被创建.

第二种方法有几个潜在的弱点，包括在发生任何其他问题时不中止查询(请参阅手动的).因此，如果之前没有测试过，应该使用它忽略关键字.

方法 3:使用 INSERT ... ON DUPLICATE KEY UPDATE:

第三个选项是使用 INSERT ... ON DUPLICATE KEY UPDATE语法，在 UPDATE 部分什么都不做，做一些无意义的(空)操作，比如计算 0+0(Geoffray 建议做id=id 赋值给 MySQL 优化引擎忽略这个手术).这种方法的优点是它只忽略重复关键事件，但仍会因其他错误而中止.

最后通知:这篇文章的灵感来自 Xaprb.我也建议请参阅他关于编写灵活的 SQL 查询的另一篇文章.

I started by googling and found the article How to write INSERT if NOT EXISTS queries in standard SQL which talks about mutex tables.

I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I want to insert does not already exist without using a pair of queries (i.e., one query to check and one to insert is the result set is empty)?

Does a unique constraint on a field guarantee the insert will fail if it's already there?

It seems that with merely a constraint, when I issue the insert via PHP, the script croaks.

解决方案

Use INSERT IGNORE INTO table.

There's also INSERT … ON DUPLICATE KEY UPDATE syntax, and you can find explanations in 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement.

---

Post from bogdan.org.ua according to Google's webcache:

18th October 2007

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

There are 3 possible solutions: using INSERT IGNORE, REPLACE, or INSERT … 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 transcripts meta-data from Ensembl, and that due to various reasons the pipeline might be broken at any step of execution. Thus, we need to ensure two things:

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 yet exist, it will be created. However, using this method isn’t efficient for our case: we do not need to overwrite existing records, it’s fine just 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 the database, it will be silently skipped (ignored). (To be more precise, here’s a quote from MySQL reference manual: "If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted.".) If the record doesn’t yet exist, it will be created.

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

Method 3: using INSERT … ON DUPLICATE KEY UPDATE:

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

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

这篇关于如何在 MySQL 中执行“如果不存在则插入"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！