MySQL查找最小的且唯一的可用ID

Question

我有一个列ID和大约1000个项目，其中某些被删除了，例如 id=90，id=127，id=326

如何查询可用的id，以便我可以将它们重新用于其他项目？

这就像一个min(ID)，但我只想找到不在我的数据库中的id，所以如果我删除了一个带有ID=90的项目，下次我点击添加项目时，我会将其插入为id=90

- braindamage

Answer 1

你可以使用以下查询获取最小可用ID：

SELECT MIN(t1.ID + 1) AS nextID
FROM tablename t1
   LEFT JOIN tablename t2
       ON t1.ID + 1 = t2.ID
WHERE t2.ID IS NULL

它的作用是将表格与自身连接，并检查min+1 ID是否为null。如果为null，则该ID可用。假设您有一个包含以下ID的表格：
1
2
5
6

那么，此查询将给您返回所需的结果3。

- shamittomar

---

为什么不选择 MIN(idColumn)-1？ - ThiefMaster

@ThiefMaster，因为我们需要获取下一个可用的ID。MIN将提供最低可用的ID，+1将提供下一个ID。 - shamittomar

如果您的ID从（例如）58开始，并且您想知道1-58号ID是空闲的，则此方法无效。因此，它应找到57作为最低可用编号。 - geoidesic

如果ID列可以为空，这可能会非常慢。在这种情况下有帮助的是将t1.ID IS NOT NULL添加到WHERE语句中。 - gadelat

@shamittomar，如果id=1没有被使用，你的查询将不会获取它。但是它能够正常工作！ - Jekis

为了找到最大的未使用ID（低于最新使用的ID），请将MIN替换为MAX，然后将所有的+1改为-1。这样可以在ID列表末尾附近获取跳过的ID，而不是在开头附近获取。 - IncredibleHat

Answer 2

不要重复使用ID。通常您有足够的可用ID，因此您不必担心碎片化。

例如，如果您重新使用ID，则来自搜索引擎的链接可能会指向与搜索索引中的任何内容完全无关的东西 - 在这种情况下，显示“未找到”错误要好得多。

- ThiefMaster

---

谢谢，但这并不是我要的情况，我需要知道如何获得最小的、独特的、未使用的ID。 - braindamage

问题是“如何”，而不是“是否”他应该。 - PhillipMcCubbin

Answer 3

尝试重用ID与代理键的概念相违背。

代理键很好，因为它标识记录本身，而不是现实生活中的某个对象。如果记录消失了，ID也会消失。

有经验的数据库开发人员不会担心数字用尽的问题，因为他们知道需要多少世纪才能耗尽长整数数字。

顺便说一下，在多线程环境中，如果同时进行事务并尝试在ID序列中查找间隙，则可能会遇到锁定或违反唯一性的问题。由DB服务器提供的自动增量ID生成器通常在事务范围之外工作，因此生成良好的代理键。

进一步阅读：代理键

- fedd

---

每个记录都有自己的ID，自动递增和其他一切。我创建这个唯一ID只是因为我的客户想要独特性，但也不要超过他添加的产品总数（这是另一个字段）...你们做了很多假设，但感谢你们的意见，也许符合你们所说的其他人可以从中受益... - braindamage

我建议不要删除记录，而是在某个字段中标记为已删除。当您需要填写新数据时，找到已删除为真的记录最小的'id'，然后进行更新。通过适当的事务隔离设置，数据库服务器会防止“脏读取”，这样另一个线程不会认为它仍然被“删除”，并想要更新相同的记录。 - fedd

这是一个很好的补充答案，为整个SO增加了重量和有用信息。当我读到问题时，我首先想到的是为什么你要这样做？问题没有指明原因 - 即使现在已经在上面澄清了，它仍然不太合理。“我正在创建这个唯一ID只是因为我的客户想要独特性，但也不能超过他添加的产品总数（另一个字段）”- 如果您正在使用ID来指定产品的总数，那么肯定是出了问题。 - ncatnow

这实际上不是对问题的回答。回答应该假定提问者有一个有效的理由来询问他们所问的内容，而这个回答则相反。 - Vincent

数个世纪的实践表明，没有重复使用ID的有效理由。 - fedd

Answer 4

查询类似于：

SELECT MIN(tableFoo.uniqueid + 1) AS nextID
FROM tableFoo
LEFT JOIN tableFoo tf1
       ON tableFoo.uniqueid + 1 = tf1.uniqueid
WHERE tf1.uniqueid IS NULL

- Haim Evgi

Answer 5

在我个人的观点中，与其从自动增量中移除行，不如为“已删除”或“已移除”添加布尔列，这样要便宜得多，而且为了额外的安全性，可以在设置已移除标志时用空白覆盖该行。

UPDATE table SET data=" ", removed = TRUE WHERE id = ##

然后你可以

（##是实际的ID）

SELECT * FROM table WHERE removed = TRUE ORDER BY id ASC

这将使您的数据库性能更佳，节省服务器成本。更重要的是确保不会发生任何恶意错误。

- Mike Pengelly

Answer 6

请注意，shamittomar和Haim Evgi的答案如果最低ID可用则无效。为了允许最低ID的重新填充，请先检查其是否可用：
SELECT TRUE FROM tablename WHERE ID = 1;
如果返回任何内容，则ID为1不可用，您应该使用他们的答案。但如果ID为1可用，就使用它。

- Jeffrey Friedl

Answer 7

假设您的数据库足够小，正确的答案是根本不要重复使用您的ID，只需确保它是自动递增的主键。该表有一千条记录，因此您可以在没有任何成本的情况下执行此操作。

然而，如果您有一个包含几百万条记录/较长ID的表格，则会发现接受的答案无法在合理的时间内完成。

接受的答案将为您提供这些值中的最小值，这是正确的，但是，您付出了不使用自动递增列或者如果您有一个自动递增列，则不将其用作实际ID（像我一样，否则我就不会在这里了）的代价。我受到遗留应用程序的限制，其中ID并非实际的主键，而是随机生成的，没有任何好的原因，因此我需要一种替换它的方法，因为增加列范围现在是一项极其昂贵的更改。

在这里，它正在找出t1和t2的整个连接，然后报告这些连接的最小值。实质上，您只关心找到的第一个NULL t1，而不管它是否实际上是最小的。

所以你需要将MIN删除，并改为添加一个LIMIT为1。

编辑：由于它不是主键，你还需要检查非空，因为主键字段不能为空。

SELECT t1.ID + 1 AS nextID
FROM tablename t1
   LEFT JOIN tablename t2
       ON t1.ID + 1 = t2.ID
WHERE t2.ID IS NULL
AND t1.ID IS NOT NULL
LIMIT 1

这将始终为您提供一个可用的ID，但不能保证它始终是最小的。

- Paul Stanley