获取下一个未使用的 id 的最有效方法 [英] Most efficient way of getting the next unused id

问题描述

(与找到最低的未使用唯一列表中的 id 和 获取 SQL 上未使用的唯一值表)

假设我有一个包含 id 列和其他一些列的表(它们在这里没有任何区别):

Suppose I have a table containing on id column and some others (they don't make any difference here):

+-----+-----+
| id  |other|
+-----+-----+

id 具有数字递增值.我的目标是获得最低的未使用 id 并创建该行.所以当然我第一次运行它会返回 0 并且该行的行将被创建.执行几次后，它看起来像这样:

The id has numerical increasing value. My goal is to get the lowest unused id and creating that row. So of course for the first time I run it will return 0 and the the row of this row would have been created. After a few executions it will look like this:

+-----+-----+
| id  |other|
+-----+-----+
|  0  | ... |
|  1  | ... |
|  2  | ... |
|  3  | ... |
|  4  | ... |
+-----+-----+

这些行中的某些行经常会被删除.假设删除了 ID 为 1 和 3 的行.不，表格将如下所示:

Fairly often some of these rows might get deleted. Let's assume the rows with the id's of 1 and 3 are removed. No the table will look like this:

+-----+-----+
| id  |other|
+-----+-----+
|  0  | ... |
|  2  | ... |
|  4  | ... |
+-----+-----+

如果我现在再次运行查询，它想取回 id 1 并且应该创建这一行:

If I now run again the query it would like to get back the id 1 and this row should be created:

| id  |other|
+-----+-----+
|  0  | ... |
|  1  | ... |
|  2  | ... |
|  4  | ... |
+-----+-----+

下次运行查询时，它应该返回 id 的 3、5、6 等

The next times the query runs it should return the id's 3, 5, 6, etc.

运行这些类型的查询的最有效方法是什么，因为我需要在一秒钟内相当频繁地执行它们(假设 id 是表的唯一目的是公平的)?是否可以通过一个查询获得下一个未使用的行?或者通过引入另一个记录未使用的 id 的表是否更容易和更快?

What's the most effective way to run those kinds of query as I need to execute them fairly often in a second (it is fair to assume that the the id's are the only purpose of the table)? Is it possible to get the next unused row with one query? Or is it easier and faster by introducing another table which keeps track of the unused id's?

如果速度明显更快，则还可以找到一种方法来重用表中的任何空洞，前提是所有数字都在某个时间重用.

If it is significantly faster it is also possible to get a way to reuse any hole in the table provided that all numbers get reused at some time.

额外问题:我计划使用 SQLite 来存储此类信息，因为除了存储这些 ID 之外，我不需要数据库.有没有其他免费(如语音)服务器可以显着更快地完成这项工作?

Bonus question: I plan to use SQLite for this kind of storing information as I don't need a database except for storing these id's. Is there any other free (as in speech) server which can do this job significantly faster?

推荐答案

喜欢 Dennis Haarbrink 说；删除触发器和插入触发器:

Like Dennis Haarbrink said; a trigger on delete and another on insert :

delete 上的触发器将获取已删除的 id 并将其插入到 id 池表中(只有一列 id)

The trigger on delete would take the deleted id and insert it in a id pool table (only one column id)

插入之前的触发器会检查是否提供了 id 值，否则它只是查询 id 池表(例如:SELECT MIN(id) FROM id_pool_table)并分配它(ig 从 id_pool_table 中删除它)

The trigger on before insert would check if an id value is provided, otherwise it just query the id pool table (ex: SELECT MIN(id) FROM id_pool_table) and assign it (i.g. deletes it from the id_pool_table)

这篇关于获取下一个未使用的 id 的最有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！