How to prevent mySQL from resetting auto increment

Question

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?

Answer 1

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”.

Answer 2

Resetting auto increment value to 0 can be caused by a few reasons.

Reason 1

You use TRANCATE table to empty the table. TRUNCATE not only deletes the rows but resets the auto increment value by design.

Use DELETE FROM table instead.

Reason 2

The table engine is InnoDB. InnoDB doesn't store auto increment value on disk thus forgets it when the MySQL server is shut down. When the MySQL is started again, the InnoDB engine restores the auto increment value this way: SELECT (MAX(id) + 1) AS auto_increment FROM table. This is a bug that is fixed in MySQL version 8.0.

Change the table engine: ALTER TABLE table ENGINE = MyISAM. Or update the MySQL server to version 8.0 when it is released.