如何重用 auto_increment 值? [英] How to reuse auto_increment values?

问题描述

可能的重复:
mysql中id的碎片(auto_increment列)

我的数据库中有此列.假设它的名字是threadid".它包含为每个线程分配的唯一 id 以进行区分.

I have this column in my database. Let's say its name is 'threadid'. It contains unique ids given to each thread for distinction.

线程ID987654321

threadid 9 8 7 6 5 4 3 2 1

假设我删除了 ID 为 5 和 6 的线程.

Let's say I have deleted threads with id 5 and 6.

线程ID9874321

threadid 9 8 7 4 3 2 1

但是当删除后有提交时，给那个线程的唯一id是10.不是5.我觉得这不整洁.

But when there is a submission after the deletion, the unique id given to that thread is 10. not 5. I think this is not neat.

如何获得列中的最小可能值?(在本例中为 5.)

How do I get the least possible value in the column? (in this case, 5.)

我认为我可以使用 MIN() 获得列的最小值并保持 +1 直到我获得未使用的唯一值，但我认为这太复杂了.

I think I can get the minimum value of the column using MIN() and keep +1 until I get the unused, unique value, but I think that's too complicated.

有没有什么简单的方法可以做到这一点?

Is there any simple way to do this?

谢谢.

推荐答案

我同意其他人的看法，即实现自己的查找最小开放数是一个非常糟糕的主意.但是在我工作的地方，我们得到了一组封闭的数字，当一个数字空闲时，我们必须重用.

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.

这是我们如何做到的.

我们不删除行，而是将每列的所有值设置为空，所以你要做的是SELECT min(id) WHERE columnA IS NULL，如果返回了什么，我们重用，否则插入新行.

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.

这篇关于如何重用 auto_increment 值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！