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.

Neddie

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

Karilynn

In every other regard, AUTO_INCREMENT serves my purposes fine, and keeps the insert queries simple. I think I'll use the "ALTER TABLE x AUTO_INCREMENT = y" construct to force the identity forwards on deletion. – Neddie

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/

Byelaw

Answer 3

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.

Revere

I think it resets when you restart the db. – Jenjena

InnoDB resets when you restart the database. When InnoDB starts up, it finds the highest increment and then start from there. MyISAM caches the increment ID, so this doesn't happen. – Koehler

In MySQL I'm using (5.7.x), once a record with highest Key is deleted, next new record >immediately< reuses that Key value. No DB restart needed. – Shinshina

I've noticed this behavior as well. It seems like using the id as a "last record" indicator isn't a good practice. – What

Answer 4

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.

Jenjena

Sorry, I just reread your question and see that you are not worried about other db elements referencing the deleted row, but rather being referenced "external to the db". That would indicate something like perhaps a user bookmarks a link which includes the id in the url, then when they reuse that bookmark, instead of getting no results, they get the wrong results. In this case, I'd say the deleted flag is probably the best way to go, and change your presentation logic to display an appropriate "deleted" user message when that data is accessed. – Jenjena

The problem with a deleted flag is that you've got to remember to use it as a constraint in every query that accesses the data (and there are many in my case), and then consider any indexing implications – Neddie

Answer 5

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

Rockweed