我有一个包含大约1400万条记录的表。如果我想以相同的格式添加更多数据,有没有一种方法可以确保我要插入的记录不存在,而不需要使用一对查询(即,一个查询用于检查,一个查询用于在结果集为空时插入)?
在字段上设置唯一约束是否能保证如果已经存在,则插入操作将失败?
似乎仅仅使用约束,当我通过PHP执行插入时,脚本会出错。
使用INSERT IGNORE INTO table
。
还有INSERT … ON DUPLICATE KEY UPDATE
语法,你可以在13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement中找到解释。
来自 bogdan.org.ua 的文章,根据 Google's webcache:
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:使用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,则表中重复现有唯一索引或主键值的行会导致重复键错误,并且语句将被中止。”如果记录尚不存在,则将创建该记录。
第二种方法存在几个潜在的弱点,包括在任何其他问题发生时不会终止查询(请参阅手册)。因此,应在未使用IGNORE关键字进行测试的情况下使用此方法。
方法3:使用INSERT ... ON DUPLICATE KEY UPDATE:
第三个选项是使用INSERT ... ON DUPLICATE KEY UPDATE
语法,在UPDATE部分中只做一些无意义的操作,比如计算0+0(Geoffray建议对id=id赋值,以使MySQL优化引擎忽略此操作)。这种方法的优点是它仅忽略重复键事件,并仍然在其他错误上中止。
最后注意:这篇文章的灵感来源于Xaprb。我还建议查看他关于编写灵活的SQL查询的其他帖子。
INSERT … ON DUPLICATE KEY UPDATE
更好,因为它不会删除行,保留任何auto_increment
列和其他数据。 - redolentINSERT ... ON DUPLICATE KEY UPDATE
方法不会增加任何AUTO_INCREMENT列的插入失败。可能是因为它并没有真正失败,而是被更新了。 - not2qubit解决方案:
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
需要唯一键约束),则可以使用此变量。 - rabuddetable
(value1, value2)
SELECT 'stuff for value1', 'stuff for value2' FROM (select 1) x
WHERE NOT EXISTS (SELECT * FROM table
WHERE value1='stuff for value1' AND value2='stuff for value2'); - noonex在MySQL中,ON DUPLICATE KEY UPDATE 或 INSERT IGNORE 可以成为可行的解决方案。
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;
一个基于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
命令查看任何警告。另一个重要提示:唯一约束条件不能与NULL值一起使用,例如,行1(1,NULL)和行2(1,NULL)都将被插入(除非违反其他约束条件,如主键)。很遗憾。 - Simon East请尝试以下方法:
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
下面是一个 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'
)
);
?>
mysql_*
扩展自PHP 5.5.0起已被弃用,并在PHP 7.0.0中删除。相反,应使用mysqli或PDO_MySQL扩展。在选择MySQL API时,还可以参考MySQL API概述获取更多帮助。 - DharmanREPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;
如果记录已经存在,它将被覆盖;如果它还不存在,它将被创建。
REPLACE
可能会删除行并插入新行以代替更新。这样会导致约束条件删除其他对象并触发删除触发器。 - xmedekoREPLACE INTO
或许是对这个问题最糟糕的回答。 - undefinedON 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
。
meta_key
表中存在的次数中。如果在末尾添加 LIMIT 1
,它会起作用,但仍然感觉不太安全/不专业。我更喜欢在插入后的选择部分写成这样:SELECT * FROM (SELECT DISTINCT ?, ?, ?) as tmp
- 这样即使忘记写 LIMIT 1
,也会更加安全。 - Janeks MalinovskisSELECT
开始就违背了只是交付一大批INSERT
并且不想担心重复的整个目的。 - warren如果您已经有了唯一或主键,其他的答案可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...
或者 REPLACE INTO ...
(请注意,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,则会生成一个新的自动递增标识符)。t
中丢弃来自表 s
的潜在新行(由于对表格 t
限制为 LIMIT 1),它将始终触发 ON DUPLICATE KEY
,这将更新 some_value
列。s
给出)。注意:关系数据库中的每个表都应该至少有一个主键自动递增的 id
列。如果没有,请添加它,即使您乍一看不需要它。它绝对是这个“技巧”所需要的。