如何处理 MySQL 中 auto_increment ID 列的碎片 [英] How to handle fragmentation of auto_increment 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 delete. 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 that number rather than delete it.

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