Avoiding failed inserts to avoid spurious autoincrement

auto-increment, innodb, mysql, sql

Solution

You should use this:

INSERT INTO tableX (username) 
  SELECT 'foo' AS username
  FROM dual
  WHERE NOT EXISTS
        ( SELECT *
          FROM tableX 
          WHERE username = 'foo'
        ) ;

If you want to include values for more columns:

INSERT INTO tableX (username, dateColumn) 
  SELECT 'foo'                       --- the aliases are not needed             
       , NOW()                       --- actually
  FROM dual
  WHERE NOT EXISTS
        ( SELECT *
          FROM tableX 
          WHERE username = 'foo'
        ) ;

Problem

My problem is the same as Why does MySQL autoincrement increase on failed inserts?, but instead of increasing my `id` field, I would prefer just to rewrite the `INSERT` query that is causing me trouble. Pretend I have a database with two fields, `id` and `username`, where `id` is a primary key and `username` is a unique key. I'm essentially looking for syntax that would do `INSERT...IF NOT EXISTS`. Right now, I do ``` INSERT INTO `table`(`username`) VALUES ('foo') ON DUPLICATE KEY UPDATE `id`=`id` ``` I only have one thread writing to the database, so I don't need any sort of concurrency protection. Suggestions?

Original source

Related problems