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

#1 楼

使用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的帖子：


18日2007年10月

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

共有3种可能的解决方案：使用INSERT IGNORE，REPLACE或
INSERT…ON DUPLICATE KEY UPDATE。

想象我们有一个表：来自Ensembl，并且由于各种原因，流水线
在执行的任何步骤都可能会中断。因此，我们需要确保两个事情：重复执行管道不会破坏我们的数据库
重复执行不会因以下原因而死亡“重复
主键”错误。

方法1：使用REPLACE

这很简单：

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;

如果记录存在，它将被覆盖；如果尚不存在
，则将创建它。但是，对于我们的情况，使用此方法效率不高
：我们不需要覆盖现有记录，可以很好地
跳过它们。

方法2：使用INSERT IGNORE也非常简单：

REPLACE 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值会导致重复键错误，并且该语句将中止。”。如果
记录尚不存在，则会创建该记录。 br />
第二种方法有许多潜在的弱点，包括
如果出现任何其他问题，则不放弃查询（请参见
手册）。因此，如果以前没有使用
IGNORE关键字进行测试，就应该使用它。

方法3：使用INSERT…ON DUPLICATE KEY UPDATE：

第三种选择是使用INSERT … ON DUPLICATE KEY UPDATE
语法，并且在UPDATE部分中什么也不做，没有任何意义
（空）操作，例如计算0 + 0（Geoffray建议为MySQL优化引擎执行
id = id赋值，忽略此
操作）。这种方法的优点是它只忽略重复的键事件，并且仍然会因其他错误而中止。

最后，请注意：本文受Xaprb启发。我还建议
咨询他的其他有关编写灵活的SQL查询的文章。

#2 楼

解决方案：

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) 

说明：

最里面的查询

SELECT * FROM `table` 
      WHERE `value1`='stuff for value1' AND `value2`='stuff for value2' LIMIT 1

已使用如WHERE NOT EXISTS -condition检测是否已经存在包含要插入数据的行。找到此类行后，查询可能会停止，因此LIMIT 1（微优化，可以省略）。

中间查询

SELECT 'stuff for value1', 'stuff for value2' FROM DUAL

代表要插入的值。 DUAL指的是默认情况下在所有Oracle数据库中都存在的特殊的一行，一列表（请参阅https://en.wikipedia.org/wiki/DUAL_table）。在MySQL-Server版本5.7.26上，省略FROM DUAL时得到了一个有效的查询，但是较旧的版本（如5.5.60）似乎需要FROM信息。如果最里面的查询找到匹配的数据，则使用WHERE NOT EXISTS中间查询返回空结果集。

外部查询

INSERT INTO `table` (`value1`, `value2`) 

插入数据，如果中间查询返回的话。

#3 楼

使用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] ... ]

#4 楼

如果可以接受异常，则任何简单的约束都可以完成工作。示例：


主键（如果不能替代）
列上的唯一约束
多列唯一约束

对不起，这似乎在欺骗性简单。我知道您与我们分享的链接看起来很糟糕。 ;-(

但是我还是不遗余力地给出这个答案，因为它似乎满足了您的需求。（否则，它可能会触发您更新需求，这也将是“一件好事”（TM） ）。

编辑：如果插入操作会破坏数据库唯一约束，则驱动程序将在数据库级别引发异常，该异常由驱动程序中继，它肯定会停止脚本，但会失败。在PHP中可以解决这种情况...

#5 楼

这是一个PHP函数，仅当表中所有指定的列值都不存在时才会插入行。


如果其中一列不同，则将添加该行。
如果表为空，则将添加行。

如果存在所有指定列均具有指定值的行，则不会添加该行。

function insert_unique($table, $vars)
{
  if (count($vars)) {
    $table = mysql_real_escape_string($table);
    $vars = array_map('mysql_real_escape_string', $vars);

    $req = "INSERT INTO `$table` (`". join('`, `', array_keys($vars)) ."`) ";
    $req .= "SELECT '". join("', '", $vars) ."' FROM DUAL ";
    $req .= "WHERE NOT EXISTS (SELECT 1 FROM `$table` WHERE ";

    foreach ($vars AS $col => $val)
      $req .= "`$col`='$val' AND ";

    $req = substr($req, 0, -5) . ") LIMIT 1";

    $res = mysql_query($req) OR die();
    return mysql_insert_id();
  }

  return False;
}

示例用法：

<?php
insert_unique('mytable', array(
  'mycolumn1' => 'myvalue1',
  'mycolumn2' => 'myvalue2',
  'mycolumn3' => 'myvalue3'
  )
);
?>

#6 楼

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

如果记录存在，它将被覆盖；如果尚不存在，则会创建它。

#7 楼

请尝试以下操作：

IF (SELECT COUNT(*) FROM beta WHERE name = 'John' > 0)
  UPDATE alfa SET c1=(SELECT id FROM beta WHERE name = 'John')
ELSE
BEGIN
  INSERT INTO beta (name) VALUES ('John')
  INSERT INTO alfa (c1) VALUES (LAST_INSERT_ID())
END

#8 楼

如果您有一个UNIQUE索引可以用ON DUPLICATE KEY或INSERT IGNORE进行检查，则有几个答案可以解决该问题。并非总是如此，并且由于UNIQUE具有长度限制（1000字节），您可能无法更改它。例如，我不得不使用WordPress（wp_postmeta）中的元数据。

我终于用两个查询解决了它：

UPDATE wp_postmeta SET meta_value = ? WHERE meta_key = ? AND post_id = ?;
INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT DISTINCT ?, ?, ? FROM wp_postmeta WHERE NOT EXISTS(SELECT * FROM wp_postmeta WHERE meta_key = ? AND post_id = ?);

查询1是常规UPDATE查询，当所涉及的数据集不存在时无效。查询2是一个INSERT，它取决于NOT EXISTS，即仅当数据集不存在时才执行INSERT。

#9 楼

值得注意的是，无论语句是否成功，INSERT IGNORE仍将像普通的INSERT一样递增主键。

这会导致您的主键出现间隙，这可能会使程序员的心理不稳定。或者，如果您的应用程序设计不佳且依赖完美的增量主键，则可能会令人头疼。

查看innodb_autoinc_lock_mode = 0（服务器设置，并带来轻微的性能损失），或者先使用SELECT以确保您的查询不会失败（该查询还会带来性能提升和额外的代码）。

#10 楼

在没有已知主键的情况下进行更新或插入

如果您已经具有唯一键或主键，则使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...或REPLACE INTO ...的其他答案应该可以正常工作（请注意，如果存在则替换为deletes，然后插入-这样就可以了不会部分更新现有值）。

但是如果您具有some_column_id和some_type的值，则已知它们的组合是唯一的。并且您要更新some_value（如果存在），或者插入（如果不存在）。而且您只想在一个查询中执行此操作（以避免使用事务）。这可能是一个解决方案：

 INSERT INTO my_table (id, some_column_id, some_type, some_value)
SELECT t.id, t.some_column_id, t.some_type, t.some_value
FROM (
    SELECT id, some_column_id, some_type, some_value
    FROM my_table
    WHERE some_column_id = ? AND some_type = ?
    UNION ALL
    SELECT s.id, s.some_column_id, s.some_type, s.some_value
    FROM (SELECT NULL AS id, ? AS some_column_id, ? AS some_type, ? AS some_value) AS s
) AS t
LIMIT 1
ON DUPLICATE KEY UPDATE
some_value = ?
 

基本上，查询以这种方式执行（较简单）



通过WHERE子句匹配选择一个现有行。
该联合产生一个潜在的新行（表s），其中列值为显式给定（s.id为NULL，因此它将生成一个新的自动增量标识符）。
如果找到现有行，则丢弃表s中潜在的新行（由于表t上的LIMIT 1） ），它将始终触发一个ON DUPLICATE KEY，该UPDATE和some_value列。
如果找不到现有行，则插入潜在的新行（如表s所给）。

注意：关系数据库中的每个表都应至少具有一个主要的自动增量id列。如果您没有此功能，请添加它，即使您一眼不需要时也可以添加它。此“技巧”绝对是必需的。