如何在MySQL中“不存在时插入”?

使用 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

从bogdan.org.ua发布 Google's webcache :

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)重复执行管道不会破坏我们的 数据库,以及2)重复执行不会因__重复而死亡 主键错误。

方法1:使用替换

它非常简单:

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;

这里,如果在 数据库,将自动跳过(忽略)。(更准确地说, 这里是MySQL参考手册的引言:如果使用忽略 关键字,执行insert语句时发生的错误是 改为警告。例如,不忽略 复制表中现有的唯一索引或主键值 导致重复的键错误,语句将中止。如果 记录还不存在,它将被创建。

第二种方法有几个潜在的弱点,包括 如果出现任何其他问题,则不中止查询(请参见 手册)。因此,如果之前未使用 忽略关键字。

还有一个选项:在重复的密钥更新中使用insert_ 语法,在更新部分,什么都不做一些没有意义的事情 (空)操作,例如计算0+0(geoffray建议执行 id=mysql优化引擎忽略此项的id分配 操作)。此方法的优点是它只忽略重复 关键事件,但仍会因其他错误而中止。

最后一点要注意的是:这篇文章的灵感来自于xaprb。我也建议 关于编写灵活的SQL查询,请参考他的其他文章。

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 

on duplicate key update 或 insert ignore 可以是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] ... ]

如果可以接受异常,那么任何简单的约束都应该完成这项工作。实例:

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

对不起,这看起来很简单。我知道面对你和我们分享的链接,这看起来很糟糕。;。-(

但我永远不会给出这个答案,因为它似乎能满足你的需要。(如果没有,它可能会触发您更新需求,这也是“一件好事”(tm)。

编辑 :如果插入会破坏数据库唯一约束,则会在数据库级别引发异常,由驱动程序中继。它肯定会以失败停止您的脚本。一定有可能在PHP中解决这个问题…

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

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

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

尝试以下操作:

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

如果你有一个 UNIQUE 可用于检查的索引 ON DUPLICATE KEY 或 INSERT IGNORE . 情况并非总是如此,而且 独特的 有长度限制(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 ,即 插入 仅在数据集不存在时执行。

尝试:

// 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
    }
}

这种方法快速简便。为了提高大表索引列“xxx”中查询的速度(在我的示例中)。