如何阻止auto_increment始终将下一个数字设置为maxnumber + 1 [英] How to block auto_increment to set the next number always as maxnumber + 1

问题描述

我有一个带有auto_incremented字段的表.

I have a table with auto_incremented field.

通常自动递增的工作方式是始终从最大值+ 1开始.

The way normally auto increment works is it always start with the max value + 1.

例如如果我插入两条记录，则自动递增字段的初始值将为1和2.
现在，当我通过显式地将id字段值提到为100来添加第三行时. 此后，如果我添加第四条记录，则自动增量值将为101，而不是3.

For e.g. if I insert two records, auto increment field takes 1 and 2 as the value initially.
Now when I add a third row by explicitly mentioning the id field value as 100. After this, If I add a fourth record, auto increment value will be 101, not 3.

我的问题:

mysql中是否有任何方法可以强制自动递增以遵循其序列?
如果遇到重复，可以跳过该过程.

Is there any way in mysql to enforce auto increment to follow its series?
If it encounters a duplicate, it can skip that.

推荐答案

我怀疑是否可以做到这一点.想象一下这种情况:您插入5行ID为1至5的行，并删除ID为2至4的行，最后得到两行，其中ID为1和ID为5的行.现在，您插入另一行.您希望数据库现在使用什么ID(2或6)?

I doubt if this can be done. Imagine this scenario: You insert 5 rows with ids 1 through 5 and delete the rows with ids 2 through 4, and end up with two rows, the one with id 1 and id 5. Now you insert another row. What id would you expect the DB to use now, 2, or 6?

我不想让数据库做前者，因为我的程序可能依赖某些主键 not (想想删除的博客帖子具有唯一的ID.您想要吗?有人点击与该ID相对应的URL而不是仅显示404时看到另一个博客文章?)

I for one wouldn't want the database to do the former, because my program could rely on some primary keys not being there (think a deleted blog post with a unique id. Would you want someone to see a different blogpost when they hit the URL corresponding to that id versus just showing a 404?)

回到您的问题，DB并不真正了解以下两种情况之间的区别:

Coming back to your question, the DB doesn't really know the difference between the following two situations:

• 像您提到的那样，手动插入了id = 100的行.
• 存在ID为1到100的100行，并且删除了3到99行.

当然，您可能有一个回收ID的用例，但您必须自己做.但是在执行此操作之前，请确保您确实想要它:)

Of course, you might have a use-case for recycling ids, but you'll have to do it yourself. But before doing that, make sure you really want it :)

这篇关于如何阻止auto_increment始终将下一个数字设置为maxnumber + 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！