停止MySQL重用AUTO_INCREMENT ID [英] Stop MySQL Reusing AUTO_INCREMENT IDs

问题描述

我有一个带有AUTO_INCREMENT主键的表.如果表中的最后一行被删除，则下一个插入的行将具有相同的ID.

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.

有没有一种方法可以使MySQL表现得像t-SQL，而不重用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.

推荐答案

在这种情况下，您可能不应该在可公开访问的地方使用AUTO_INCREMENT索引.

In this case, you probably should not be using AUTO_INCREMENT indices in publicly accessible places.

要么从其他数据派生关键字段，要么使用其他机制创建您的ID.尽管您需要了解(可能很严重的)性能影响，但我以前使用的一种方法是使用键"表来跟踪最后使用的键并对其进行递增.

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.

我过去使用过6个字符的字母数字键:

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

这篇关于停止MySQL重用AUTO_INCREMENT ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！