如何'插入如果不存在'在 MySQL？

I started by googling, and found this article which talks about mutex tables.

I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I want to insert does not already exist without using a pair of queries (ie, one query to check and one to insert is the result set is empty)?

Does a unique constraint on a field guarantee the insert will fail if it's already there?

It seems that with merely a constraint, when I issue the insert via php, the script croaks.

Any simple constraint should do the job, if an exception is acceptable. Examples :

• primary key if not surrogate
• unique constraint on a column
• multi-column unique constraint

Sorry is this seems deceptively simple. I know it looks bad confronted to the link you share with us. ;-(

But I neverleless give this answer, because it seem to fill your need. (If not, it may trigger your updating your requirements, which would be "a Good Thing"(TM) also).

Edited: If an insert would break the database unique constraint, an exception is throw at the database level, relayed by the driver. It will certainly stop your script, with a failure. It must be possible in PHP to adress that case ...

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 

Alternatively, the outer SELECT statement can refer to DUAL in order to handle the case where the table is initially empty:

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 

Here is a PHP function that will insert a row only if all the specified columns values don't already exist in the table.

• If one of the columns differ, the row will be added.

• If the table is empty, the row will be added.

• If a row exists where all the specified columns have the specified values, the row won't be added.

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

Example usage :

<?php
insert_unique('mytable', array(
  'mycolumn1' => 'myvalue1',
  'mycolumn2' => 'myvalue2',
  'mycolumn3' => 'myvalue3'
  )
);
?>

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

If the record exists, it will be overwritten; if it does not yet exist, it will be created.

Try the following:

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

Try:

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

Or you can do:

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

This method is fast and easy. For improve the speed of the query in your big table INDEX columns 'xxx' ( In my example ).

There are several answers that cover how to solve this if you have a UNIQUE index that you can check against with ON DUPLICATE KEY or INSERT IGNORE. That is not always the case, and as UNIQUE has a length constraint (1000 bytes) you might not be able to change that. For example, I had to work with metadata in WordPress (wp_postmeta).

I finally solved it with two queries:

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 = ?);

Query 1 is a regular UPDATE query with no effect when the dataset in question is not there. Query 2 is an INSERT which depends on a NOT EXISTS, i.e. the INSERT is only executed when the dataset doesn't exist.