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

我从谷歌搜索开始，找到了关于互斥锁表的文章如何写INSERT如果不存在的查询在标准SQL。

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

字段上的unique约束是否保证insert在已经存在的情况下会失败？

似乎在仅仅的约束下，当我通过PHP发出插入时，脚本会发出声音。

1018313 次浏览
小开

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

  • 主键如果不是代理项
  • 列的唯一约束
  • 多列唯一约束

对不起，如果这看起来很简单。我知道它看起来很糟糕，面对你与我们分享的链接。；-（

但我还是给出了这个答案，因为它似乎满足了你的需求。（如果不是，它可能会触发你更新你的需求，这也将是一件好事（TM））。

如果插入会破坏数据库唯一约束，则会在数据库级别抛出异常，由驱动程序中继。它肯定会停止您的脚本，导致失败。在PHP中必须能够解决这种情况…

小开
最佳答案

使用INSERT IGNORE INTO table。

还有INSERT … ON DUPLICATE KEY UPDATE语法，您可以在13.2.6.2INSERT… ON DUPLICATE KEY UPDATE语句中找到解释。

发布自bogdan.org.ua

2007年10月18日

开始：从最新的MySQL开始，标题中呈现的语法不是有可能。但有几个非常简单的方法来完成需要使用现有功能。

有三种可能的解决方案：使用INSERT IGNORE、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;

现在假设我们有一个自动导入成绩单的管道来自Ensembl的元数据，由于各种原因，管道在执行的任何一步都可能被破坏。因此，我们需要确保两个内容：

  1. 管道的重复执行不会破坏我们的>数据库
  1. 重复执行不会因为重复而死>主键错误。

方法一：使用REPLACE

这很简单：

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

如果记录存在，它将被覆盖；如果还没有存在，将被创建。但是，使用此方法效率不高对于我们的情况：我们不需要覆盖现有记录，没关系#36825；过他们

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

第二种方法有几个潜在的弱点，包括在发生任何其他问题时不中止查询（请参阅手动）。因此，如果之前在没有忽略关键字。

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

第三种选择是使用INSERT … ON DUPLICATE KEY UPDATE语法，在UPDATE部分只是什么都不做做一些毫无意义的事情（空）操作，如计算0+0（Geoffray建议执行id=id分配给MySQL优化引擎以忽略此操作）。这种方法的优点是它只忽略重复键事件，并仍然中止其他错误。

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

小开

在MySQL中，关于重复密钥更新或插入忽略可以是可行的解决方案。

一个例子ON DUPLICATE KEY UPDATE更新基于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;

INSERT IGNORE基于mysql.com

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

或：

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

或：

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

解决方案：

INSERT INTO `table` (`value1`, `value2`)SELECT 'stuff for value1', 'stuff for value2' FROM DUALWHERE 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条件，检测是否已经存在要插入数据的行。找到一行后，查询可能会停止，因此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`)

插入中间查询返回的数据（如果有）。

小开

这是一个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;}

示例用法：

<?phpinsert_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')ELSEBEGININSERT 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是一个依赖于NOT EXISTS的INSERT，即INSERT仅在数据集不存在时执行。

小开

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

这将导致主键中的空白，这可能会使程序员精神不稳定。或者，如果您的应用程序设计不佳并且依赖于完美的增量主键，它可能会成为令人头痛的问题。

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

小开

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

如果你已经有一个唯一的或主键，其他答案INSERT INTO ... ON DUPLICATE KEY UPDATE ...或REPLACE INTO ...应该可以正常工作（注意，如果存在，则替换为删除，然后插入-因此不会部分更新现有值）。

但是如果你有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_valueFROM (SELECT id, some_column_id, some_type, some_valueFROM my_tableWHERE some_column_id = ? AND some_type = ?UNION ALLSELECT s.id, s.some_column_id, s.some_type, s.some_valueFROM (SELECT NULL AS id, ? AS some_column_id, ? AS some_type, ? AS some_value) AS s) AS tLIMIT 1ON DUPLICATE KEY UPDATEsome_value = ?

基本上，查询以这种方式执行（没有看起来那么复杂）：

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

注意：关系数据库中的每个表都应该至少有一个主自动增量id列。如果你没有，添加它，即使你第一眼不需要它。这个“技巧”肯定需要它。

小开 
INSERT INTO table_name (columns) VALUES (values) ON CONFLICT (id) DO NOTHING;

提交答案