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

Answer 3

you can simply reset the auto increment field after the failed insert

ALTER TABLE table_name AUTO_INCREMENT =1;

this will not reset the auto increment to 1 but will reset it to the current maximum plus one

also note that the user you are connected with to the DB should has alter privilege to the targeted table

Answer 4

You can also check before insert that user if it was inserted before, it will cost an extra call into your DB but, if the user already exists, you can return the information that you found and a fake insert is avoided.

Answer 5

use INSERT IGNORE INTO table. Check the below post

http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html

You can do it like:

INSERT INTO `table`(`username`) 
VALUES ('foo')      
ON DUPLICATE KEY UPDATE `username`= 'foo' 

OR

INSERT IGNORE INTO `table`
SET `username` = 'foo'