php - 如何在MySQL中插入“如果不存在”?





performance primary-key (9)


尝试以下操作:

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

我开始用google搜索,并找到了article谈论互斥表的article

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

一个字段上的unique约束是否保证insert将会失败,如果它已经在那里?

似乎只有一个约束,当我通过php发布插入时,脚本就会呱呱叫。




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



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 



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

根据Google的网络缓存从bogdan.org.ua发布:

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

方法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,则会复制现有UNIQUE索引或表中的PRIMARY KEY值导致重复键错误,并且语句中止。“)如果记录尚不存在,它将被创建。

第二种方法有几个潜在的弱点,包括在发生任何其他问题时不中断查询(参见手册)。 因此,如果以前没有使用IGNORE关键字进行测试,应该使用它。

还有一个选择:使用INSERT ... ON DUPLICATE KEY UPDATE语法,并且在UPDATE部分中,不做任何无意义的(空操作),就像计算0 + 0一样(Geoffray建议为MySQL优化做id = id赋值引擎忽略此操作)。 这种方法的优点是它只会忽略重复的按键事件,并且仍会在其他错误上中止。

作为最后通知:这篇文章受到Xaprb的启发。 我还建议在编写灵活的SQL查询时咨询他的另一篇文章。







尝试:

// 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'中的查询速度(在我的示例中)。




如果可以接受例外,任何简单的约束都应该完成这项工作。 例子 :

  • 主键如果不是代孕的话
  • 列上的唯一约束
  • 多列唯一约束

对不起,这看起来似乎很简单。 我知道它与您与我们分享的链接看起来很糟糕。 ;-(

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

已编辑 :如果插入操作会破坏数据库唯一性约束,则会在驱动程序传递的数据库级别引发异常。 它肯定会停止你的脚本,失败。 PHP中必须能够解决这个问题......




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

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




DATETIME,TIMESTAMP和DATE之间的比较

什么是[.fraction]?

  • DATETIME或TIMESTAMP值可以包括高达微秒(6位)精度的尾随小数秒部分。 特别是,插入DATETIME或TIMESTAMP列的值中的任何小数部分都将被存储而不是被丢弃。 这当然是可选的。

资料来源:





php sql mysql performance primary-key