AUTO_INCREMENT on INSERT ... ON DUPLICATE KEY UPDATE - Source

Revision: 108979
User: Sebastian Stamm
Date: 2021-08-09 14:18

I'm not sure what the expected behavior is:

Using an example table:
<<code>>
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;
<</code>>

<<code>>
INSERT INTO tmp (t) VALUES('1') ON DUPLICATE KEY UPDATE t = t;
SELECT LAST_INSERT_ID();
<</code>>
Returns 1

Again:
<<code>>
INSERT INTO tmp (t) VALUES('1') ON DUPLICATE KEY UPDATE t = t;
SELECT LAST_INSERT_ID();
<</code>>
Returns also 1.

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

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

<<code>>
INSERT INTO tmp (t) VALUES('1') ON DUPLICATE KEY UPDATE t = t;
SELECT LAST_INSERT_ID();
<</code>>
Returns 3. Should i expect the ID of t= '1' or the current AUTO_INCREMENT (which is 3)?

<<code>>
INSERT INTO tmp (t) VALUES('2') ON DUPLICATE KEY UPDATE t = t;
SELECT LAST_INSERT_ID();
<</code>>
Returns 3 But AUTO_INCREMENT on table is already 6

<<code>>
INSERT INTO tmp (t) VALUES('3') ON DUPLICATE KEY UPDATE t = t;
SELECT LAST_INSERT_ID();
<</code>>
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

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.

AUTO_INCREMENT on INSERT ... ON DUPLICATE KEY UPDATE - Source

Answer

Products

Services

Pricing

Resources

About Us

Download MariaDB