I've got a MySql table with an auto-increment primary key, and it seems that all of the various upsert methods (INSERT IGNORE and ON DUPLICATE KEY UPDATE) suffer from the, uh, feature that the auto-increment field increments, even if a row is updated and not inserted. This means that gaps are introduced into the table, which I find undesirable.
So the question is: is there any way to upsert records in a table with an auto-increment field without auto-incrementing that field, if the upsert in fact merely updates the row. To my mind, this is the way upsert should behave, but it doesn't seem to.
When the AUTO_INCREMENT column reaches the upper limit of data type then the subsequent effort to generate the sequence number fails. That is why it is advised to use a large enough integer data type for the AUTO_INCREMENT column to hold the maximum sequence value required by us.
It could have multiple causes: Check if the auto_increment value on the table itself, has the next highest value. Mind that if you have transactions where you INSERT a row and rollback the transaction, that auto_increment value will be gone/skipped.
You don't have to specify NOT NULL on the column definition with AUTO_INCREMENT . You can leave it off, and MySQL will make the column NOT NULL . And if you specify NULL in place of NOT NULL , MySQL will accept the syntax, but it will ignore that, and make the column NOT NULL anyway.
Each table can have only one AUTO_INCREMENT column. It must defined as a key (not necessarily the PRIMARY KEY or UNIQUE key). In some storage engines (including the default InnoDB), if the key consists of multiple columns, the AUTO_INCREMENT column must be the first column.
This "problem" is only in InnoDB
.
It is by design, and intended to improve concurrency: another thread can use an AUTO_INCREMENT
without having to wait for the results of an UPSERT
operation.
From the docs:
After a server startup, for the first insert into a table
t
,InnoDB
executes the equivalent of this statement:SELECT MAX(ai_col) FROM t FOR UPDATE;
…
InnoDB
initializes but does not increment the value and stores it for use by later inserts…
When accessing the auto-increment counter,
InnoDB
uses a special table-levelAUTO-INC
lock that it keeps to the end of the currentSQL
statement, not to the end of the transaction. The special lock release strategy was introduced to improve concurrency for inserts into a table containing anAUTO_INCREMENT
column. Nevertheless, two transactions cannot have theAUTO-INC
lock on the same table simultaneously, which can have a performance impact if theAUTO-INC
lock is held for a long time. That might be the case for a statement such asINSERT INTO t1 ... SELECT ... FROM t2
that inserts all rows from one table into another.
MyISAM
does not exhibit this behavior, since it's AUTO_INCREMENT
algorithm is implemented differently (due to its limited ability to support concurrent DML
).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With