Stop MySQL Reusing AUTO_INCREMENT IDs

问题：

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.

回答：

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