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

Answer 2

I don't think you can prevent the counter from being incremented, for the reasons given in the answers the question to which you've linked.

You have three options:

1. Live with skipped identifiers; do you really expect to use up 64-bits?

2. Check for existence of the existing record prior to attempting the INSERT:

   DELIMITER ;;
   
   IF NOT EXISTS (SELECT * FROM `table` WHERE username = 'foo') THEN
     INSERT INTO `table` (username) VALUES ('foo');
   END IF;;
   
   DELIMITER ;
   

   Or, better yet, use the FROM dual WHERE NOT EXISTS ... form suggested by @ypercube.

3. Reset the counter after each insert. If performing the INSERT operation within a stored procedure, you could do this using a handler for duplicate key errors:

   DELIMITER ;;
   
   CREATE PROCEDURE TEST(IN uname CHAR(16) CHARSET latin1) BEGIN
     DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN
       -- WARNING: THIS IS NOT SAFE FOR CONCURRENT CONNECTIONS
       SET @qry = CONCAT(
         'ALTER TABLE `table` AUTO_INCREMENT = ',
         (SELECT MAX(id) FROM `table`)
       );
       PREPARE stmt FROM @qry;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;
       SET @qry = NULL;
     END;
     INSERT INTO `table` (username) VALUES (uname);
   END;;
   DELIMITER ;
   

   Bearing in mind the (valid) concerns @ypercube raised in his comments beneath regarding this strategy, you might instead use:

   SELECT AUTO_INCREMENT - 1
   FROM   INFORMATION_SCHEMA.TABLES
   WHERE  table_schema = 'db_name' AND table_name = 'table';