How to insert if not exists in MySQL

Question

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

User · Answer

Update or insert without known primary key

If you already have a unique or primary key, the other answers with either INSERT INTO ... ON DUPLICATE KEY UPDATE ... or REPLACE INTO ... should work fine (note that replace into deletes if exists and then inserts - thus does not partially update existing values).

But if you have the values for some_column_id and some_type, the combination of which are known to be unique. And you want to update some_value if exists, or insert if not exists. And you want to do it in just one query (to avoid using a transaction). This might be a solution:

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

Basically, the query executes this way (less complicated than it may look):

Select an existing row via the WHERE clause match.
Union that result with a potential new row (table s), where the column values are explicitly given (s.id is NULL, so it will generate a new auto-increment identifier).
If an existing row is found, then the potential new row from table s is discarded (due to LIMIT 1 on table t), and it will always trigger an ON DUPLICATE KEY which will UPDATE the some_value column.
If an existing row is not found, then the potential new row is inserted (as given by table s).

Note: Every table in a relational database should have at least a primary auto-increment id column. If you don't have this, add it, even when you don't need it at first sight. It is definitely needed for this "trick".

User · Answer

Try the following   IF  SELECT COUNT    FROM beta WHERE name    John   gt  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

User · Answer

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

User · Answer

Something worth noting is that INSERT IGNORE will still increment the primary key whether the statement was a success or not just like a normal INSERT would    This will cause gaps in your primary keys that might make a programmer mentally unstable  Or if your application is poorly designed and depends on perfect incremental primary keys  it might become a headache   Look into innodb autoinc lock mode   0  server setting  and comes with a slight performance hit   or use a SELECT first to make sure your query will not fail  which also comes with a performance hit and extra code

User · Answer

Solution   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     Explanation   The innermost query  SELECT   FROM  table         WHERE  value1   stuff for value1  AND  value2   stuff for value2  LIMIT 1   used as the WHERE NOT EXISTS-condition detects if there already exists a row with the data to be inserted  After one row of this kind is found  the query may stop  hence the LIMIT 1  micro-optimization  may be omitted    The intermediate query   SELECT  stuff for value1    stuff for value2  FROM DUAL   represents the values to be inserted  DUAL refers to a special one row  one column table present by default in all Oracle databases  see https   en wikipedia org wiki DUAL table   On a MySQL-Server version 5 7 26 I got a valid query when omitting FROM DUAL  but older versions  like 5 5 60  seem to require the FROM information  By using WHERE NOT EXISTS the intermediate query returns an empty result set if the innermost query found matching data   The outer query  INSERT INTO  table    value1    value2      inserts the data  if any is returned by the intermediate query

User · Answer

use INSERT IGNORE INTO table  see http   bogdan org ua 2007 10 18 mysql-insert-if-not-exists-syntax html  there s also INSERT     ON DUPLICATE KEY UPDATE syntax  you can find explanations on dev mysql com    Post from bogdan org ua according to Google s webcache      18th October 2007      To start  as of the latest MySQL  syntax presented in the title is not   possible  But there are several very easy ways to accomplish what is   expected using existing functionality       There are 3 possible solutions  using INSERT IGNORE  REPLACE  or   INSERT     ON DUPLICATE KEY UPDATE       Imagine we have a table   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        Now imagine that we have an automatic pipeline importing transcripts   meta-data from Ensembl  and that due to various reasons the pipeline   might be broken at any step of execution  Thus  we need to ensure two   things            repeated executions of the pipeline will not destroy our   database   repeated executions will not die due to    duplicate   primary key    errors          Method 1  using REPLACE      It   s very simple   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  However  using this method isn   t efficient   for our case  we do not need to overwrite existing records  it   s fine   just to skip them       Method 2  using INSERT IGNORE Also very simple   INSERT IGNORE INTO  transcripts  SET  ensembl transcript id     ENSORGT00000000001    transcript chrom start    12345   transcript chrom end    12678        Here  if the    ensembl transcript id    is already present in the   database  it will be silently skipped  ignored    To be more precise    here   s a quote from MySQL reference manual     If you use the IGNORE   keyword  errors that occur while executing the INSERT statement are   treated as warnings instead  For example  without IGNORE  a row that   duplicates an existing UNIQUE index or PRIMARY KEY value in the table   causes a duplicate-key error and the statement is aborted       If the   record doesn   t yet exist  it will be created       This second method has several potential weaknesses  including   non-abortion of the query in case any other problem occurs  see the   manual   Thus it should be used if previously tested without the   IGNORE keyword       Method 3  using INSERT     ON DUPLICATE KEY UPDATE       Third option is to use INSERT     ON DUPLICATE KEY UPDATE   syntax  and in the UPDATE part just do nothing do some meaningless    empty  operation  like calculating 0 0  Geoffray suggests doing the   id id assignment for the MySQL optimization engine to ignore this   operation   Advantage of this method is that it only ignores duplicate   key events  and still aborts on other errors       As a final notice  this post was inspired by Xaprb  I   d also advise to   consult his other post on writing flexible SQL queries

User · Answer

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

User · Answer

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   gt   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     lt  php insert unique  mytable   array     mycolumn1    gt   myvalue1      mycolumn2    gt   myvalue2      mycolumn3    gt   myvalue3           gt

User · Answer

on duplicate key update  or insert ignore can be viable solutions with 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          Or   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          Or   INSERT  LOW PRIORITY   HIGH PRIORITY   IGNORE       INTO  tbl name   col name           SELECT           ON DUPLICATE KEY UPDATE       col name expr            col name expr

User · Answer

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

[php] How to 'insert if not exists' in MySQL?

Update or insert without known primary key

Examples related to php

Examples related to mysql

Examples related to sql

Examples related to primary-key

Examples related to sql-insert