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

问题描述

我从谷歌搜索开始，发现这个文章，它讨论了互斥表.

I started by googling, and found this article which talks about mutex tables.

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

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 (ie, one query to check and one to insert is the result set is empty)?

对字段的unique约束是否可以保证insert如果已经存在则失败?

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

似乎只有 一个约束，当我通过php发出插入操作时，脚本发出了吱吱作响的声音.

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

推荐答案

使用INSERT IGNORE INTO table

请参见 http://bogdan .org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html

还有INSERT … ON DUPLICATE KEY UPDATE语法，您可以在 根据

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

2007年10月18日

18th October 2007

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

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.

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

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

想象我们有一张桌子:

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;

现在想象一下，我们有一个自动管道导入成绩单 来自Ensembl的元数据，并且由于各种原因，管道 在执行的任何步骤都可能会损坏.因此，我们需要确保两个 事情:

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. 重复执行管道不会破坏我们的 数据库

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

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

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

方法1:使用REPLACE

这很简单:

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.

方法2:使用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值 导致重复键错误，并且该语句被中止.".如果 记录尚不存在，它将被创建.

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.

第二种方法有一些潜在的弱点，包括 万一发生其他任何问题，请不要放弃查询(请参阅 手动的).因此，如果先前测试时没有 IGNORE关键字.

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.

方法3:使用INSERT…ON重复键更新:

第三种选择是使用INSERT … ON DUPLICATE KEY UPDATE 语法，而在UPDATE部分则什么也不做，没有任何意义 (空)操作，例如计算0 + 0(Geoffray建议您执行 id = id分配给MySQL优化引擎忽略它 手术).这种方法的优点是它只忽略重复项 关键事件，但仍会因其他错误而中止.

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.

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

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屋！