我有一个列ID和大约1000个项目,其中某些被删除了,例如 id=90,id=127,id=326
如何查询可用的id,以便我可以将它们重新用于其他项目?
这就像一个min(ID)
,但我只想找到不在我的数据库中的id,所以如果我删除了一个带有ID=90
的项目,下次我点击添加项目时,我会将其插入为id=90
你可以使用以下查询获取最小可用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
的表格:那么,此查询将给您返回所需的结果3
。
不要重复使用ID。通常您有足够的可用ID,因此您不必担心碎片化。
例如,如果您重新使用ID,则来自搜索引擎的链接可能会指向与搜索索引中的任何内容完全无关的东西 - 在这种情况下,显示“未找到”错误要好得多。
尝试重用ID与代理键的概念相违背。
代理键很好,因为它标识记录本身,而不是现实生活中的某个对象。如果记录消失了,ID也会消失。
有经验的数据库开发人员不会担心数字用尽的问题,因为他们知道需要多少世纪才能耗尽长整数数字。
顺便说一下,在多线程环境中,如果同时进行事务并尝试在ID序列中查找间隙,则可能会遇到锁定或违反唯一性的问题。由DB服务器提供的自动增量ID生成器通常在事务范围之外工作,因此生成良好的代理键。
进一步阅读:代理键
查询类似于:
SELECT MIN(tableFoo.uniqueid + 1) AS nextID
FROM tableFoo
LEFT JOIN tableFoo tf1
ON tableFoo.uniqueid + 1 = tf1.uniqueid
WHERE tf1.uniqueid IS NULL
UPDATE table SET data=" ", removed = TRUE WHERE id = ##
SELECT * FROM table WHERE removed = TRUE ORDER BY id ASC
这将使您的数据库性能更佳,节省服务器成本。更重要的是确保不会发生任何恶意错误。
SELECT TRUE FROM tablename WHERE ID = 1;
假设您的数据库足够小,正确的答案是根本不要重复使用您的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,但不能保证它始终是最小的。
MIN(idColumn)-1
? - ThiefMasterid=1
没有被使用,你的查询将不会获取它。但是它能够正常工作! - JekisMIN
替换为MAX
,然后将所有的+1
改为-1
。这样可以在ID列表末尾附近获取跳过的ID,而不是在开头附近获取。 - IncredibleHat