Avoiding failed inserts to avoid spurious autoincrement

Question

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?

Answer 1

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'
        ) ;