如何在MySQL中插入“如果不存在”？

2018-06-02 19:55:59

我开始用google搜索，并找到了这篇谈论互斥表的文章。

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

一个字段上的unique约束是否保证insert将会失败，如果它已经在那里？

似乎只有一个约束，当我通过php发布插入时，脚本就会呱呱叫。

使用INSERT IGNORE INTO table

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

还有INSERT … ON DUPLICATE KEY UPDATE语法，你可以在dev.mysql.com找到解释

根据Google的网络缓存从bogdan.org.ua发布：

2007年10月18日

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

有三种可能的解决方案：使用INSERT IGNORE，REPLACE或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导入转录元数据，并且由于各种原因，流水线可能在任何执行步骤中被破坏。因此，我们需要确保两件事：1）重复执行管道不会破坏我们的数据库，2）重复执行不会因'重复主键'错误而死亡。

方法1：使用REPLACE

这很简单：

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

如果记录存在，它将被覆盖; 如果它还不存在，它将被创建。但是，对于我们的情况，使用这种方法效率不高：我们不需要覆盖现有记录，只需跳过它们即可。

方法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值导致重复键错误，并且语句中止。“）如果记录尚不存在，它将被创建。

第二种方法有几个潜在的弱点，包括在发生任何其他问题时不中断查询（参见手册）。因此，如果以前没有使用IGNORE关键字进行测试，应该使用它。

还有一个选择：使用INSERT ... ON DUPLICATE KEY UPDATE语法，并且在UPDATE部分中，不做任何无意义的（空操作），就像计算0 + 0一样（Geoffray建议为MySQL优化做id = id赋值引擎忽略此操作）。这种方法的优点是它只会忽略重复的按键事件，并且仍会在其他错误上中止。

作为最后通知：这篇文章受到Xaprb的启发。我还建议在编写灵活的SQL查询时咨询他的另一篇文章。

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

或者，外部SELECT语句可以引用DUAL以处理表初始为空的情况：

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

重复密钥更新，或插入忽略可以成为MySQL的可行解决方案。

基于mysql.com的重复密钥更新更新示例

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

基于mysql.com的插入忽略示例

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

要么：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

要么：

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

链接地址: http://www.djcxy.com/p/10051.html

上一篇: How to 'insert if not exists' in MySQL?

下一篇: Difference between "not equal" operators <> and != in PHP