Results 1 to 2 of 2
  1. #1
    Join Date
    May 2012

    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(?).
    My questions:
    - Is there a workaround?
    - Is it still the case in MySQL 5.6 or has it been fixed

  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts