INSERT ON DUPLICATE KEY do nothing

Question

I have a table with a unique key for two columns   CREATE  TABLE  xpo   user permanent gift     id  INT UNSIGNED NOT NULL AUTO INCREMENT    fb user id  INT UNSIGNED NOT NULL    gift id  INT UNSIGNED NOT NULL    purchase timestamp  TIMESTAMP NULL DEFAULT now     PRIMARY KEY   id     UNIQUE INDEX  user gift UNIQUE    fb user id  ASC   gift id  ASC       I want to insert a row into that table  but if the key exists  to do nothing  I don t want an error to be generated because the keys exist   I know that there is the following syntax   INSERT     ON DUPLICATE KEY UPDATE       but is there something like   INSERT     ON DUPLICATE KEY DO NOTHING

User · Answer

HOW TO IMPLEMENT  insert if not exist    1  REPLACE INTO  pros     simple    cons     too slow   auto-increment key will CHANGE increase by 1  if there is entry matches unique key or primary key  because it deletes the old entry then insert new one    2  INSERT IGNORE  pros     simple    cons     auto-increment key will not change if there is entry matches unique key or primary key but auto-increment index will increase by 1  some other errors warnings will be ignored such as data conversion error    3  INSERT     ON DUPLICATE KEY UPDATE  pros     you can easily implement  save or update  function with this   cons     looks relatively complex if you just want to insert not update   auto-increment key will not change if there is entry matches unique key or primary key but auto-increment index will increase by 1    4  Any way to stop auto-increment key increasing if there is entry matches unique key or primary key   As mentioned in the comment below by  toien   auto-increment column will be effected depends on innodb autoinc lock mode config after version 5 1  if you are using innodb as your engine  but this also effects concurrency  so it needs to be well considered before used  So far I m not seeing any better solution

User · Answer

Use ON DUPLICATE KEY UPDATE       Negative   because the UPDATE uses resources for the second action   Use INSERT IGNORE       Negative   MySQL will not show any errors if something goes wrong  so you cannot handle the errors  Use it only if you don   t care about the query

User · Answer

Yes  use INSERT     ON DUPLICATE KEY UPDATE id id  it won t trigger row update even though id is assigned to itself    If you don t care about errors  conversion errors  foreign key errors  and autoincrement field exhaustion  it s incremented even if the row is not inserted due to duplicate key   then use INSERT IGNORE

[mysql] INSERT ... ON DUPLICATE KEY (do nothing)

HOW TO IMPLEMENT 'insert if not exist'?

1. `REPLACE INTO`

2. `INSERT IGNORE`

3. `INSERT ... ON DUPLICATE KEY UPDATE`

4. Any way to stop auto-increment key increasing if there is entry matches `unique key` or `primary key`?

Examples related to mysql

Examples related to sql

Examples related to unique-key

[mysql] INSERT ... ON DUPLICATE KEY (do nothing)

HOW TO IMPLEMENT 'insert if not exist'?

1. REPLACE INTO

2. INSERT IGNORE

3. INSERT ... ON DUPLICATE KEY UPDATE

4. Any way to stop auto-increment key increasing if there is entry matches unique key or primary key?