blocks|key|2002897|text|除非整个表被截断，否则MySQL将保持is的顺序。删除行不影响最后一个ID。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2002898|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

MySQL will maintain the order of IDs unless the entire table is truncated. Deletion of rows does not affect the last ID.

blocks|key|1042719|text|帮你自己个忙忘掉这件事吧。这将是一个巨大的痛苦实施，因此，除非有一个非常迫切的目标，这是不能接近的，否则(不可能)，我们谈论的是巨大的痛苦，没有任何好处。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1042720|MySql本身不支持这个场景正是因为这个原因:巨大的复杂性而没有任何好处。|1042721|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Do yourself a favor and forget about this. It is going to be a massive pain to implement, so unless there is a very compelling goal to achieve that cannot be approached otherwise (unlikely) we are talking massive pain for no gain.

MySql itself does not support this scenario for exactly this reason: massive complication for no benefit.

blocks|key|2002927|text|因此，为了引用完整性，我不建议重用ids。有了int和bigint的最大限制，您就不应该用尽唯一的值。唯一的值是参考数据库的核心。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2002928|这样，您就可以运行一个搜索子例程(而不是触发器)来找到最低可用的id。然后暂时关闭标识插入，插入数据，然后打开标识插入。|2002929|有很多理由不这样做！其中包括在同时输入多个值时插入数据之间的所有潜在冲突。|2002930|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

So, for referential integrity I don't recommend re-using ids. With the maximum limits on int and bigint you should never run out of unique values. Unique values are the core of referential databases. 

With that said, you could run a search sub-routine (or instead of trigger) to find the lowest available id. Then momentarily turn off identity inserts, insert the data, then turn identity inserts back on.

There are so many reasons not to do this! Among which are all of the potential collisions between inserting data when more than one value are coming in at the same time.

blocks|key|1013352|text|我同意其他人的意见，在实现自己的查找最小开放数是一个非常糟糕的想法。但是在我工作的地方，我们会得到一组封闭的数字，当一个数字被释放时，我们必须重新使用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1013353|我们是怎么做到的。|1013354|我们不删除该行，而是设置每个列空的所有值，因此您要做的是选择min(id)，其中columnA是空的，如果这返回了什么，我们就重用，否则，插入一个新的行。|1013355|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

blocks|key|1042784|text|MySQL+4294967295中INT的最大数量--如果您每分钟创建1000个线程--每天需要1440000+(1000x60x24)id。大约8.17年后你就会用完身份证了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1042785|BIGINT的最大数量为18446744073709551615，这足以为35+096年创建每分钟1000000000(10亿)个ids。|offset|length|style|BOLD|1042786|因此，您应该可以随意浪费ids，而不必担心它们。|1042787|entityMap^0|0|11|7|1A|N|0|0|O|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]|$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@$D|S|E|T|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|K|$]]

The maximum number of INT in MySQL 4294967295 if you are creating 1000 threads per minute you would need 1440000 ( 1000 x 60 x 24 ) id's per day. So you would run out of ids after about 8.17 years.

The maximum number of BIGINT is 18446744073709551615 and that would be enough to create 1000000000 (1 billion) ids per minute for 35 096 years.

So you should be fine with just wasting ids as much as you like and not worry about them.

<blockquote>
 Possible Duplicate: 
 <a href="https://stackoverflow.com/questions/1824600/fragmentation-of-ids-auto-increment-column-in-mysql">Fragmentation of id&#39;s (auto_increment column) in mysql</a> 
</blockquote>



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

threadid
9
8
7
6
5
4
3
2
1

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

threadid
9
8
7
4
3
2
1

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.

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

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?

Thanks.

How to reuse auto_increment values?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

 可能重复：我的数据库里有这个专栏。让我们假设它的名字是‘三重’。它包含给每个线程的唯一It以进行区分。6 5 4 3 2 1假设我删除了id 5和6的线程。9 8 7 4 3 2但是当删除后有一个提交时，给这个线程的唯一id是10。而不是5。我认为这是不整洁的。如何在列中获得尽可能少的值？(在这种情况下，5.)我认为...

问如何重用auto_increment值？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何重用auto_increment值？EN