我首先在谷歌上搜索并找到了关于互斥表的文章How to write INSERT if NOT EXISTS queries in standard SQL。
我有一张包含约 1400 万条记录的表。如果我想以相同的格式添加更多数据,有没有办法确保我要插入的记录在不使用一对查询的情况下不存在(即,一个要检查的查询和一个要插入的查询是结果集是空的)?
字段上的 unique
约束是否保证 insert
已经存在时会失败?
似乎只有一个约束,当我通过 PHP 发出插入时,脚本会发出嘶哑的声音。
使用 INSERT IGNORE INTO table
。
还有 INSERT … ON DUPLICATE KEY UPDATE
语法,您可以在 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement 中找到解释。
根据 Google's 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` ) ) 引擎=InnoDB 默认字符集=latin1;现在想象一下,我们有一个从 Ensembl 导入转录元数据的自动管道,并且由于各种原因,管道可能在执行的任何步骤中被破坏。因此,我们需要确保两件事:
管道的重复执行不会破坏我们的 > 数据库
重复执行不会因为“重复 > 主键”错误而死。方法一:使用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 关键字进行测试,则应该使用它。方法 3:使用 INSERT ... ON DUPLICATE KEY UPDATE:第三个选项是使用 INSERT ... ON DUPLICATE KEY UPDATE 语法,并且在 UPDATE 部分什么都不做,做一些无意义(空)的操作,比如计算 0+0(Geoffray 建议做id=id 分配给 MySQL 优化引擎忽略这个操作)。这种方法的优点是它只忽略重复的关键事件,并且仍然会中止其他错误。最后通知:这篇文章的灵感来自 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
条件检测是否已存在包含要插入数据的行。找到这种类型的一行后,查询可能会停止,因此 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`)
插入数据,如果中间查询返回的话。
INSERT IGNORE
和 INSERT ON DUPLICATE KEY
需要唯一键约束),则此变体适用
stuff for value1
和 stuff for value2
相同怎么办?这会抛出一个 Duplicate column name
table
(value1, value2) SELECT 'stuff for value1', 'stuff for value2' FROM (select 1) x WHERE NOT EXISTS (SELECT * FROM table
而不是 DUAL WHERE value1='value1 的东西' AND value2='value2 的东西');
在 MySQL 中,ON DUPLICATE KEY UPDATE 或 INSERT IGNORE 可能是可行的解决方案。
基于 mysql.com 的 ON DUPLICATE KEY UPDATE 更新示例:
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 IGNORE 示例
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] ... ]
如果可以接受异常,则任何简单的约束都应该完成这项工作。例子:
主键如果不是代理
列上的唯一约束
多列唯一约束
对不起,如果这看起来很简单。我知道您与我们分享的链接看起来很糟糕。 ;-(
但我还是给出了这个答案,因为它似乎满足了你的需要。 (如果不是,它可能会触发您更新您的要求,这也是“一件好事”(TM))。
如果插入会破坏数据库唯一约束,则会在数据库级别引发异常,由驱动程序中继。它肯定会停止你的脚本,但失败了。在 PHP 中必须有可能解决这种情况......
INSERT IGNORE
基本上将所有错误都更改为警告,这样您的脚本就不会中断。然后,您可以使用命令 SHOW WARNINGS
查看任何警告。另一个重要提示:UNIQUE 约束不适用于 NULL 值,即。 row1 (1, NULL) 和 row2 (1, NULL) 都将被插入(除非另一个约束,例如主键被破坏)。不幸的。
尝试以下操作:
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
REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;
如果记录存在,将被覆盖;如果它还不存在,它将被创建。
REPLACE
可能会删除该行,然后插入而不是更新。副作用是约束可能会删除其他对象并触发删除触发器。
这是一个 PHP 函数,仅当表中不存在所有指定的列值时才会插入一行。
如果其中一列不同,则将添加该行。
如果表为空,则将添加该行。
如果存在所有指定列都具有指定值的行,则不会添加该行。函数 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 .= "选择'"。 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) . ") 限制 1"; $res = mysql_query($req) OR die();返回 mysql_insert_id(); } 返回假; }
示例用法:
<?php
insert_unique('mytable', array(
'mycolumn1' => 'myvalue1',
'mycolumn2' => 'myvalue2',
'mycolumn3' => 'myvalue3'
)
);
?>
mysql_*
扩展自 PHP 5.5.0 起已弃用,自 PHP 7.0.0 起已被删除。相反,应使用 mysqli 或 PDO_MySQL 扩展名。在选择 MySQL API 时,另请参阅 MySQL API Overview 以获得更多帮助。
如果您有可以使用 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
仅在数据集不存在时执行。
meta_key
中存在的重复行。如果在最后添加 LIMIT 1
- 它会起作用,但这仍然让人感觉不安全/不安全。我更喜欢插入后的选择部分,如下所示:SELECT * FROM (SELECT DISTINCT ?, ?, ?) as tmp
- 如果忘记写 LIMIT 1
,感觉更安全。
值得注意的是,无论语句成功与否,INSERT IGNORE 仍然会增加主键,就像普通的 INSERT 一样。
这将导致您的主键出现间隙,这可能会使程序员精神不稳定。或者,如果您的应用程序设计不佳并且依赖于完美的增量主键,则可能会令人头疼。
查看 innodb_autoinc_lock_mode = 0
(服务器设置,并带有轻微的性能损失),或首先使用 SELECT 以确保您的查询不会失败(这也带有性能损失和额外的代码)。
SELECT
开头会破坏仅移交大量 INSERT
而不想担心重复的整个目的。
在没有已知主键的情况下更新或插入
如果您已经有唯一键或主键,则 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_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 更新 some_value 列。
如果未找到现有行,则插入潜在的新行(由 table s 给出)。
注意:关系数据库中的每个表都应该至少有一个主自增列 id
。如果你没有这个,添加它,即使你一开始不需要它。这个“技巧”绝对需要它。
INSERT INTO ... SELECT FROM
格式。为什么你也是?
INSERT INTO... SELECT FROM...
解决方案。请给我一个相同答案的链接,如果你能找到它,我会删除这个答案,否则你会支持我的答案(交易?)。请务必验证您要链接的答案仅使用 1 个查询(用于更新 + 插入),没有事务,并且能够定位已知唯一的列的任何组合(因此单独的列不必须是唯一的)。
INSERT INTO table_name (columns) VALUES (values) ON CONFLICT (id) DO NOTHING;
ON CONFLICT
的其他数据库软件中运行(尽管在 SQLite 中不行,它会使用 IGNORE
而不是 DO NOTHING
)。
INSERT … ON DUPLICATE KEY UPDATE
更好,因为它不会删除行,保留任何auto_increment
列和其他数据。INSERT … ON DUPLICATE KEY UPDATE
方法确实会增加插入失败的任何 AUTO_INCREMENT 列。可能是因为它并没有真正失败,而是更新了。