Prevent auto increment on MySQL duplicate insert

Solution:

You could modify your INSERT to be something like this:

INSERT INTO tablename (tag)
SELECT $tag
FROM tablename
WHERE NOT EXISTS(
    SELECT tag
    FROM tablename
    WHERE tag = $tag
)
LIMIT 1
Copy code

Where$tag is the tag (properly quoted or as a placeholder of course) that you want to add if it isn't already there. This approach won't even trigger an INSERT (and the subsequent autoincrement wastage) if the tag is already there. You could probably come up with nicer SQL than that but the above should do the trick.

If your table is properly indexed then the extra SELECT for the existence check will be fast and the database is going to have to perform that check anyway.

This approach won't work for the first tag though. You could seed your tag table with a tag that you think will always end up being used or you could do a separate check for an empty table.

Solution:

I just found this gem...

http://www.timrosenblatt.com/blog/2008/03/21/insert-where-not-exists/

INSERT INTO [table name] SELECT '[value1]', '[value2]' FROM DUAL
WHERE NOT EXISTS(
    SELECT [column1] FROM [same table name]
    WHERE [column1]='[value1]'
    AND [column2]='[value2]' LIMIT 1
)
Copy code

If affectedRows = 1 then it inserted; otherwise if affectedRows = 0 there was a duplicate.

Solution:

The MySQL documentation for v 5.5 says:

"If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter 
is **not** incremented and LAST_INSERT_ID() returns 0, 
which reflects that no row was inserted."
Copy code

Ref: http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

Since version 5.1 InnoDB has configurable Auto-Increment Locking. See also http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-inc...

Workaround: use option innodb_autoinc_lock_mode=0 (traditional).

Solution:

I found mu is too short's answer helpful, but limiting because it doesn't do inserts on an empty table. I found a simple modification did the trick:

INSERT INTO tablename (tag)
SELECT $tag
FROM (select 1) as a     #this line is different from the other answer
WHERE NOT EXISTS(
    SELECT tag
    FROM tablename
    WHERE tag = $tag
)
LIMIT 1
Copy code

Replacing the table in the from clause with a "fake" table(select 1) as a allowed that part to return a record which allowed the insert to take place. I'm running mysql 5.5.37. Thanks mu for getting me most of the way there ....

Solution:

The accepted answer was useful, however I ran into a problem while using it that basically if your table had no entries it would not work as the select was using the given table, so instead I came up with the following, which will insert even if the table is blank, it also only needs you to insert the table in 2 places and the inserting variables in 1 place, less to get wrong.

INSERT INTO database_name.table_name (a,b,c,d)
SELECT 
    i.*
FROM
    (SELECT 
        $a AS a, 
            $b AS b,
            $c AS c,
            $d AS d
            /*variables (properly escaped) to insert*/
    ) i
        LEFT JOIN        
    database_name.table_name o ON i.a = o.a AND i.b = o.b /*condition to not insert for*/
WHERE
    o.a IS NULL
LIMIT 1 /*Not needed as can only ever be one, just being sure*/
Copy code

Hope you find it useful

Solution:

You can always addON DUPLICATE KEY UPDATE Read here (not exactly, but solves your problem it seems).

From the comments, by @ravi

Whether the increment occurs or not depends on the innodb_autoinc_lock_mode setting. If set to a non-zero value, the auto-inc counter will increment even if the ON DUPLICATE KEY fires

Solution:

I had the same problem but didn't want to use innodb_autoinc_lock_mode = 0 since it felt like I was killing a fly with a howitzer.

To resolve this problem I ended up using a temporary table.

create temporary table mytable_temp like mytable;
Copy code

Then I inserted the values with:

insert into mytable_temp values (null,'valA'),(null,'valB'),(null,'valC');
Copy code

After that you simply do another insert but use "not in" to ignore duplicates.

insert into mytable (myRow) select mytable_temp.myRow from mytable_temp 
where mytable_temp.myRow not in (select myRow from mytable);
Copy code

I haven't tested this for performance, but it does the job and is easy to read. Granted this was only important because I was working with data that was constantly being updated so I couldn't ignore the gaps.

Solution:

modified the answer from mu is too short, (simply remove one line) as i am newbie and i cannot make comment below his answer. Just post it here

the query below works for the first tag

 INSERT INTO tablename (tag)
 SELECT $tag
 WHERE NOT EXISTS(
    SELECT tag
    FROM tablename
    WHERE tag = $tag
)
Copy code

Solution:

I just put an extra statement after the insert/update query: ALTER TABLEtable_name AUTO_INCREMENT = 1 And then he automatically picks up the highest prim key id plus 1.