The Ultimate Guide to High Availability with MariaDB

AUTO_INCREMENT on INSERT ... ON DUPLICATE KEY UPDATE

I'm not sure what the expected behavior is:

Using an example table:

CREATE TABLE `tmp` (
	`i` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`t` CHAR(50) NOT NULL COLLATE 'utf8_general_ci',
	`modified` TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
	PRIMARY KEY (`i`) USING BTREE,
	UNIQUE INDEX `t` (`t`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

INSERT INTO tmp (t) VALUES('1') ON DUPLICATE KEY UPDATE t = t;
SELECT LAST_INSERT_ID();

Returns 1

Again:

INSERT INTO tmp (t) VALUES('1') ON DUPLICATE KEY UPDATE t = t;
SELECT LAST_INSERT_ID();

Returns also 1.

INSERT INTO tmp (t) VALUES('2') ON DUPLICATE KEY UPDATE t = t;
SELECT LAST_INSERT_ID();

Returns 3 (So even ON DUPLICATE KEY UPDATE was performed, the AUTO_INCREMENT was incremented)

INSERT INTO tmp (t) VALUES('1') ON DUPLICATE KEY UPDATE t = t;
SELECT LAST_INSERT_ID();

Returns 3. Should i expect the ID of t= '1' or the current AUTO_INCREMENT (which is 3)?

INSERT INTO tmp (t) VALUES('2') ON DUPLICATE KEY UPDATE t = t;
SELECT LAST_INSERT_ID();

Returns 3 But AUTO_INCREMENT on table is already 6

INSERT INTO tmp (t) VALUES('3') ON DUPLICATE KEY UPDATE t = t;
SELECT LAST_INSERT_ID();

Returns 6

Is it intended, that even INSERT ... ON DUPLICATE KEY UPDATE increments the AUTO_INCREMENT. And the ON DUPLICATE KEY does not provide the effected key though LAST_INSERT_ID?

Tested with MariaDB 10.5.10

Answer Answered by Daniel Black in this comment.

The last_insert_id returns only the last inserted value which is intended. Hitting an "ON DUPLICATE KEY UPDATE" doesn't count as an insertion so returns the value used in the last successful insert.

auto_increment in cases like this can contain missing values. Its part of the architecture that its easier to allocate an auto_increment value before it detected that a duplicate occurs.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.