Search code examples
mysqlauto-incrementreset

How to prevent mySQL from resetting auto increment value?

I have a table to make temporary id`s . When i delete all of the rows of my table , auto increment value for this table will reset to 0 . but i don't want to reset the auto increment. What can i do?

Solution

  • Compare TRUNCATE TABLE:

    Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

    ... with DELETE FROM:

    If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value is not reused for a MyISAM or InnoDB table. If you delete all rows in the table with DELETE FROM tbl_name (without a WHERE clause) in autocommit mode, the sequence starts over for all storage engines except InnoDB and MyISAM. There are some exceptions to this behavior for InnoDB tables, as discussed in Section 13.3.5.3, “AUTO_INCREMENT Handling in InnoDB”.

    For MyISAM tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. In this case, reuse of values deleted from the top of the sequence occurs even for MyISAM tables. See Section 3.6.9, “Using AUTO_INCREMENT”.