如何在MySQL中'insert if not exists'？-Java 学习之路

704

我开始使用谷歌搜索，发现这个article谈论互斥表 .

我有一张约有1400万条记录的表格 . 如果我想以相同的格式添加更多数据，有没有办法确保我想要插入的记录不存在而不使用一对查询（即，一个查询要检查，一个要插入是结果集是空）？

字段上的 unique 约束是否保证 insert 如果已经存在则会失败？

似乎只有一个约束，当我通过php发出插入时，脚本呱呱叫 .

9 回答

INSERT INTO `table` (value1, value2) 
SELECT 'stuff for value1', 'stuff for value2' FROM `table` 
WHERE NOT EXISTS (SELECT * FROM `table` 
      WHERE value1='stuff for value1' AND value2='stuff for value2') 
LIMIT 1

或者，外部 SELECT 语句可以引用 DUAL ，以便处理表最初为空的情况：

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

回复于 2024-05-19T00:58:34+08:00

24
如果可以接受异常，任何简单约束都应该完成 . 例子：
- 主键如果不是代理
- 列上的唯一约束
- 多列唯一约束
对不起，这看起来似乎很简单 . 我知道您与我们分享的链接看起来很糟糕 . ;-(

但我永远不会给出这个答案，因为它似乎满足了你的需要 . （如果没有，它可能会触发您更新您的要求，这也是“好事”（TM）） .

Edited ：如果插入会破坏数据库唯一约束，则会在数据库级别抛出异常，由驱动程序中继 . 它肯定会因为失败而停止你的脚本 . 在PHP中必须能够解决这种情况......
回复于 2024-05-19T00:58:34+08:00
18

使用 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上找到解释

Post from bogdan.org.ua according to Google's webcache:

2007年10月18日开始：从最新的MySQL开始， Headers 中提供的语法是不可能的 . 但是有几种非常简单的方法可以实现使用现有功能所期望的功能 . 有3种可能的解决方案：使用INSERT IGNORE，REPLACE或INSERT ... ON DUPLICATE KEY UPDATE . 想象一下，我们有一个表：CREATE TABLEtranscripts（
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 INTOtranspts
SETensembl_transcript_id ='ENSORGT00000000001'，
transcript_chrom_start = 12345，
transcript_chrom_end = 12678;
如果记录存在，它将被覆盖;如果它还不存在，它将被创建 . 但是，对于我们的情况，使用此方法效率不高：我们不需要覆盖现有记录，只需跳过它们就可以了 . 方法2：使用INSERT IGNORE也很简单：INSERT IGNORE INTOtranscriptpts
SETensembl_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查询的其他帖子 .

回复于 2024-05-19T00:58:34+08:00

706

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

回复于 2024-05-19T00:58:34+08:00

on duplicate key update，或insert ignore可以成为MySQL的可行解决方案 .

Example of on duplicate key update update based on 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;

Example of insert ignore based on 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] ... ]

回复于 2024-05-19T00:58:34+08:00

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

如果记录存在，它将被覆盖;如果它还不存在，它将被创建 .

回复于 2024-05-19T00:58:34+08:00

请尝试以下方法：

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

回复于 2024-05-19T00:58:34+08:00

165
如果您有 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 仅在数据集不存在时执行 .
回复于 2024-05-19T00:58:34+08:00

尝试：

// Check if exist cod = 56789
include "database.php";

$querycheck = mysql_query ("SELECT * FROM `YOURTABLE` WHERE `xxx` = '56789';");
$countrows = mysql_num_rows($querycheck);
if($countrows == '1')
{
  // Exist 
}
else
{
 // .... Not exist
}

或者你可以这样做：

// Check if exist cod = 56789
include "database.php";

$querycheck = mysql_query ("SELECT * FROM `YOURTABLE` WHERE `xxx` = '56789';");
$countrows = mysql_num_rows($querycheck);
while($result = mysql_fetch_array($querycheck))
{
    $xxx = $result['xxx'];
    if($xxx == '56789')
    {
      // Exist
    }
    else
    {
      // Not exist
    }
}

这种方法快速简便 . 为了提高大表INDEX列'xxx'（在我的例子中）中查询的速度 .

回复于 2024-05-19T00:58:34+08:00

如何在MySQL中'insert if not exists'？

9 回答

相关问题