Stop MySQL Reusing AUTO_INCREMENT IDs

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.

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

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/

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.