Prevent Auto Increment Skip On Duplicate Key Update

Question

I have a table with

(ID INT auto_incrment primary key, 
tag VARCHAR unique)

I want to insert multiple tags at one. Like this:

INSERT INTO tags (tag) VALUES ("java"), ("php"), ("phyton");

If I would execute this, and "java" is already in the table, I'm getting an error. It doesn't add "php" and "python".

If I do it like this :

INSERT INTO tags (tag) VALUES ("java"), ("php"), ("phyton") 
    ON DUPLICATE KEY UPDATE tag = VALUES(tag)

it gets added without an error, but it skips 2 values at the ID field.

Example: I have Java with ID = 1 and I run the query. Then PHP will be 3 and Phyton 4. Is there a way to execute this query without skipping the IDs?

I don't want big spaces between them. I also tried INSERT IGNORE.

Thank you!

While you should nto care that much about continuous values, if you want to change that behaviour, you can set `innodb_autoinc_lock_mode=0` in your config file, see e.g. [Auto Increment skipping numbers?](https://stackoverflow.com/q/17798835/6248528) But be aware of potential side effects (and which cases it covers), so read the link to the manual in that answer. You need to have the permissions to change the server configuration. Otherwise, you can calculate the next number manually when you insert (so you cannot use the autoincrement feature) - or stop worrying about gaps. — Solarflare, Oct 03 '17 at 13:45

score 0 · Answer 1 · answered Oct 05 '17 at 23:18

See "SQL #1" in http://mysql.rjweb.org/doc.php/staging_table#normalization . It is more complex but avoids 'burning' ids. It has the potential drawback of needing the tags in another table. A snippet from that link:

# This should not be in the main transaction, and it should be
#    done with autocommit = ON
# In fact, it could lead to strange errors if this were part
#    of the main transaction and it ROLLBACKed.
INSERT IGNORE INTO Hosts (host_name)
    SELECT DISTINCT s.host_name
        FROM Staging AS s
        LEFT JOIN Hosts AS n  ON n.host_name = s.host_name
        WHERE n.host_id IS NULL;

By isolating this as its own transaction, we get it finished in a hurry, thereby minimizing blocking. By saying IGNORE, we don't care if other threads are 'simultaneously' inserting the same host_names. (If you don't have another thread doing such INSERTs, you can toss the IGNORE.)

(Then it goes on to talk about IODKU.)

score 0 · Answer 2 · answered Apr 21 '21 at 17:32

INNODB engine Its main feature is to support ACID type transactions.

What it usually does that I point out is not a "problem", is that the engine will "reserve" the id before knowing if it is a duplicate or not. This is a solution, but it depends on your table, if we are talking about a very large one you should do some tests first because the AUTO_INCREMENT function helps you to follow the ordering of the id.

I'll give you some examples:

INSERT INTO tags (java,php,python) VALUES ("val1"), ("val2"), ("val3") 
ON DUPLICATE KEY UPDATE java = VALUES(java), id = LAST_INSERT_ID(id); 

SELECT LAST_INSERT_ID();

ALTER TABLE tags AUTO_INCREMENT = 1;

Note: I added LAST_INSERT_ID () to you because every time you insert or update it always gives you an inserted or reserved id. Each time INSERT INTO is called, AUTO_INCREMNT must be followed.

Prevent Auto Increment Skip On Duplicate Key Update

2 Answers2