Stop MySQL Reusing Auto_Increment Ids

Stop MySQL Reusing AUTO_INCREMENT IDs

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

Will MySQL reuse deleted ID's when Auto Increment is applied

InnoDB resets the auto_increment field when you restart the database.

When InnoDB restarts, it finds the highest value in the column and then starts from there.

This won't happen in MyISAM because it caches the last incremented id.

Update

This feature/bug has been around since 2003 and can lead to serious issues. Take the example below,

1. Table t1 has an auto-inc primary key.

2. Table t2 has a column for the primary key in t1 without a foreign key "constraint". In other words, when a row is deleted in t1 the corresponding rows in t2 are orphaned.

3. As we know with InnoDB restart, an id can be re-issued. Therefore orphaned rows in t2 can be falsely linked to new rows in t1.

This bug has been finally fixed in MySQL 8.0.0 WL#6204 - InnoDB
persistent max value for autoinc columns.

InnoDB will keep track of the maximum value and on restart preserve
that max value and start from there.

How to reuse auto_increment values?

I agree with the rest of everyone where it is a very bad idea to implement your own find minimal open number. But at where I work, we are given a closed set of number and when a number is free up, we must reuse.

Here is how we did it.

We do not delete the row, but set all values of every column null, So what you do is
SELECT min(id) WHERE columnA IS NULL, and if this returns something, we reuse, otherwise, insert a new row.

How to NOT recycle auto_increment IDs in InnoDB

The 5.5 docs suggests storing the auto-increment value elsewhere as you already have.

An alternative solution would be to emulate a SEQUENCE so you don't use auto-increment in the actual table itself. This has been discussed on SO before and again. The MYSQL Performance blog mentions it.

Yet another MySQL data screwing that other RDBMS don't have...

MySQL temporarily disable auto increment ID for data migration

Just building upon the comment from P.Salmon, it seems that it should just happen without any problem when you insert values to both the id and other fields at the same time.

However if you for some reason need to change the id given to the next inserted row in the table, you have use an alter table statement. Just like the one in the example on the page P.Salmon linked to https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

ALTER TABLE new_table AUTO_INCREMENT = 100;

how to prevent auto increment from skipping id numbers in a mysql database?

Just execute the following SQL query:

ALTER TABLE `tbl_name` AUTO_INCREMENT = 1;

…but it sounds like a terrible idea, so don't do it. Why is the value of your primary key so important? Uniqueness is far more important, and reseting it undermines that.

---

Related Topics