Unanswered: Prevent autoinc in On Duplicate Key Update
Using MySQL 5.5 innoDB table. Customers table with an autoinc col, CustID, Fullname. CustID in set to Unique. I have the following statement:
INSERT INTO Customers (CustID, Fullname) VALUES (5, 'Nadia Bruschetta') ON DUPLICATE KEY UPDATE Fullname=VALUES(Fullname);
When a custId is found the record is not inserted which is good, however the autoinc is incremented which is undesirable. The result is the autoinc column has gaps. From what I read it seems to be a bug(?).
- Is there a workaround?
- Is it still the case in MySQL 5.6 or has it been fixed
Gaps in numbering are not necessarily a bad thing and is very normal in most applications. However, if your requirement is to not have gaps then you will need to develop your own auto increment solution. This is not a bug, this is the way the database server works.