Stop MySQL Reusing AUTO_INCREMENT IDs

Question

I have a table with an AUTO_INCREMENT primary key. If the last row in the table is deleted, the next-inserted row will take the same ID.

Is there a way of getting MySQL to behave like t-SQL, and not reuse the ID? Then if the deleted row is erroneously referenced from something external to the database, no rows will be returned, highlighting the error.

Answer 1

Mysql manual says:

In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.

It seems there is such a behavior possible for the engines, other than MyISAM

Answer 2

As of MySQL version 8, MySQL no longer re-uses AUTO_INCREMENT ID values, fixing the long-standing (opened in 2003!!) bug #199.

For more info, see this blog post by MySQL Community Manager lefred: https://lefred.be/content/bye-bye-bug-199/

Answer 3

In this case, you probably should not be using AUTO_INCREMENT indices in publicly accessible places.

Either derive a key field from other data, or use a different mechanism to create your id's. One way I've used before, although you need to be aware of the (potentially severe) performance implications, is a "keys" table to track the last-used key, and increment that.

That way, you can use any type of key you want, even non-numeric, and increment them using your own algorithm.

I have used 6-character alpha-numeric keys in the past:

CREATE TABLE `TableKeys` (
  `table_name` VARCHAR(8) NOT NULL,
  `last_key` VARCHAR(6) NOT NULL,
  PRIMARY KEY (`table_name`)
);

SELECT * FROM `TableKeys`;

table_name | last_key
-----------+---------
users      | U00003A2
articles   | A000166D
products   | P000009G

Answer 4

That's not the way our MySQL databases work, when a record is deleted the next inserted has the next number, not the one that was deleted.

Answer 5

As I understand it, there is no way of doing this. You might consider working around it by adding a deleted flag, and then setting the deleted flag instead of removing the row.

The "right" answer is that once a row is deleted, you shouldn't be referencing it. You can add foreign keys to make sure that the db will not allow rows to be deleted that are referenced elsewhere in the db.