如何在MySQL中处理自动无提示ID列的碎片 [英] How to handle fragmentation of auto increament ID column in MySQL

问题描述

我有一个带有auto_increment字段的表，有时行被删除，因此auto_increment留下了空白.有什么方法可以避免这种情况，或者至少没有避免如何编写SQL查询的方法:

I have a table with an auto_increment field and sometimes rows get deleted so auto_increment leaves gaps. Is there any way to avoid this or if not, at the very least, how to write an SQL query that:

1. 将auto_increment值更改为最大值(当前值)+1
2. 返回新的auto_increment值吗?

1. Alters the auto_increment value to be the max(current value) + 1
2. Return the new auto_increment value?

我知道如何编写部分 1 和 2 ，但是我可以将它们放在同一查询中吗?

I know how to write part 1 and 2 but can I put them in the same query?

如果这不可能:

如何选择"(返回)auto_increment值或auto_increment值+ 1?

how do I "select" (return) the auto_increment value or auto_increment value + 1?

推荐答案

重新编号会引起混淆.现有报告将引用记录99，但是如果系统重新编号，则可能会将记录重新编号为98，现在所有报告(和填充的UI)都是错误的.一旦分配了唯一的ID，它就必须保持固定状态.

Renumbering will cause confusion. Existing reports will refer to record 99, and yet if the system renumbers it may renumber that record to 98, now all reports (and populated UIs) are wrong. Once you allocate a unique ID it's got to stay fixed.

除了简单的唯一编号之外，将ID字段用于其他任何事情都会有问题.对无间隙"的要求与能够精简的要求完全不符.也许您可以将记录标记为已删除，而不是删除它们.然后，确实没有差距.假设您正在制作带编号的发票:您将拥有一个具有该编号的零值已取消发票，而不是将其删除.

Using ID fields for anything other than simple unique numbering is going to be problematic. Having a requirement for "no gaps" is simply inconsistent with the requirement to be able to delte. Perhaps you could mark records as deleted rather than delete them. Then there are truly no gaps. Say you are producing numbered invoices: you would have a zero value cancelled invoice with a that number rather than delete it.

这篇关于如何在MySQL中处理自动无提示ID列的碎片的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！