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

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 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/