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

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

我有一张约有 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 的 webcache从 bogdan.org.ua 发布：

2007 年 10 月 18 日
首先：从最新的 MySQL 开始，标题中提供的语法是不可能的。但是，有几种非常简单的方法可以使用现有功能来完成预期的工作。
有 3 种可能的解决方案：使用 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 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 EXISTS - 条件可检测是否已经存在包含要插入数据的行。找到此类行后，查询可能会停止，因此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`)

插入数据（如果中间查询返回了任何数据）。

使用重复键更新或插入忽略可以是可行的 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] ... ]

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

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

抱歉，这看似简单。我知道您与我们分享的链接看起来很糟糕。 ;-(

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

编辑：如果插入将打破数据库唯一约束，则驱动程序将在数据库级别引发异常。它肯定会停止您的脚本，但会失败。在 PHP 中必须有可能解决这种情况...

这是一个 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'
  )
);
?>

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

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

请尝试以下操作：

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

如果您有一个可以使用ON DUPLICATE KEY或INSERT IGNORE进行检查的UNIQUE索引，那么有几个答案可以解决该问题。情况并非总是如此，并且由于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当数据集确实只执行不存在的。

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

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

查看innodb_autoinc_lock_mode = 0 （服务器设置，并且会带来轻微的性能下降），或者先使用 SELECT 来确保查询不会失败（这也会带来性能下降和额外的代码）。

没有已知主键的更新或插入

如果您已经有一个唯一键或主键，则使用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 ，该ON DUPLICATE KEY UPDATE将UPDATE some_value列。
如果找不到现有行，则插入潜在的新行（如表s ）。

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