MariaDB unnecessary AUTO_INCREMENT with ON DUPLICATE clause

Question

I have a table, I named 'srcCodDsc', that has 3 column:

ID : INT PRIMARY KEY with AUTO_INCREMENT
locId : VARCHAR
locDsc : VARCHAR

I also defined an unique index on locId, to avoid duplication of a locId value.

Now the problem :

INSERT INTO srcCodDsc (locId,locDsc) VALUES ('012_002','value to set') 
ON DUPLICATE KEY UPDATE locDsc=VALUES(locDsc);

The query update correctly the locDsc value of the row with locId = '012_002', but I discover that the AUTO_INCREMENT next value counter is incremented at every execution of the query, even if a new line is not inserted.

I check it with command:

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA='berna' AND TABLE_NAME='srcCodDsc';

How to turn the query into a conditional form that, after checking if the record with locId = '012_002' exists, in one case does insert, in the other one does update and if locDsc is already worth the new value does nothing?

NOTE: the same query in mySql works and not update auto_increment next value.

Thank you

Rick James · Accepted Answer · 2018-10-01 21:44:00Z

0

For efficiency, various INSERT-like statements quickly grab the AUTO_INCREMENT values that it might need, then they proceed to do the query. In your case, the id was not needed, so it was "burned".

I believe this works the same in both MySQL and MariaDB. If you are finding a difference, please provide the version numbers of both, plus the value of the variable innodb_autoinc_lock_mode.

Note also: (before MySQL 8.0) If you stop and restart MySQL/MariaDB between burning the last id in the table, that id will be reused. (This leads to a different type of surprise, hence the change in 8.0.)

answered Oct 1, 2018 at 21:44

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MariaDB unnecessary AUTO_INCREMENT with ON DUPLICATE clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related