获取下一个未使用的 id 的最有效方法
（与查找 a 中最低的未使用唯一 ID 有关list 和获取 SQL 表上未使用的唯一值)
假设我有一个包含 id 列和其他一些列的表（它们在这里没有任何区别）：
+-----+-----+
| id |other|
+-----+-----+
id 具有数值递增的值。我的目标是获得最低的未使用 id 并创建该行。因此，当然，我第一次运行它将返回 0 并且该行的行将被创建。几次执行后，它将如下所示：
+-----+-----+
| id |other|
+-----+-----+
| 0 | ... |
| 1 | ... |
| 2 | ... |
| 3 | ... |
| 4 | ... |
+-----+-----+
通常，其中一些行可能会被删除。假设 id 为 1 和 3 的行已被删除。不，表将如下所示：
+-----+-----+
| id |other|
+-----+-----+
| 0 | ... |
| 2 | ... |
| 4 | ... |
+-----+-----+
如果我现在再次运行查询，它希望返回 id 1 并且应创建这一行：
| id |other|
+-----+-----+
| 0 | ... |
| 1 | ... |
| 2 | ... |
| 4 | ... |
+-----+-----+
下次运行查询时，它应返回 id 的 < code>3、5、6 等。
运行此类查询的最有效方法是什么，因为我需要在一秒钟内相当频繁地执行它们（可以公平地假设 id 是该表的唯一目的）？是否可以通过一个查询获取下一个未使用的行？或者通过引入另一个表来跟踪未使用的 id 是否更容易、更快？
如果速度明显更快，那么也有可能找到一种方法来重用表中的任何漏洞，前提是所有数字都在某个时间被重用。
额外问题：我计划使用 SQLite 来存储这种信息，因为除了存储这些 id 之外我不需要数据库。是否有任何其他免费（如语音）服务器可以显着更快地完成这项工作？
(related to Finding the lowest unused unique id in a list and Getting unused unique values on a SQL table)
Suppose I have a table containing on id column and some others (they don't make any difference here):
+-----+-----+
| id |other|
+-----+-----+
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 | ... |
+-----+-----+
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 | ... |
+-----+-----+
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 | ... |
+-----+-----+
The next times the query runs it should return the id's 3, 5, 6, etc.
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.
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
就像丹尼斯·哈布林克所说；一个删除触发器和另一个插入触发器：
删除触发器将获取已删除的 id 并将其插入 id 池表（只有一列
id）插入之前的触发器将检查 id 值是否为提供，否则它只是查询 id 池表（例如：SELECT MIN(id) FROM id_pool_table）并分配它（ig 从 id_pool_table 中删除它）
Like Dennis Haarbrink said; a trigger on delete and another on insert :
The trigger on delete would take the deleted id and insert it in a id pool table (only one column
id)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)
我想我会在删除时创建一个触发器，并将 old.id 插入到单独的表中。
然后您可以从该表中选择 min(id) 来获取最低的 id。
免责声明：我不知道您使用什么数据库引擎，所以我不知道触发器是否可供您使用。
I think I'd create a trigger on delete, and insert the old.id in a separate table.
Then you can select min(id) from that table to get the lowest id.
disclaimer: i don't know what database engine you use, so i don't know if triggers are available to you.
通常您会让数据库处理分配 id。是否有特殊原因需要 id 是连续的而不是唯一的？相反，您可以为它们添加时间戳，并在显示它们时对它们进行编号吗？或者为顺序 ID 单独创建一列，然后重新编号？
或者，您不能删除行本身，而是使用列中的标志将它们标记为已删除，然后通过查找编号最低的“已删除”行并重新使用该 id 来重新使用标记行的 id。
Normally you'd let the database handle assigning the ids. Is there a particular reason you need to have the id's sequential rather than unique? Can you, instead, timestamp them, and just number them when you display them? Or make a separate column for the sequential id, and renumber them?
Alternatively, you could not delete the rows themselves, but rather, mark them as deleted with a flag in a column, and then re-use the id's of the marked rows by finding the lowest numbered 'deleted' row, and reusing that id.
数据库并不关心这些值是否是连续的，只关心它们是唯一的。让您的 id 值连续的愿望纯粹是装饰性的，如果您将此值公开给用户 - 它不应该是您的主键，也不应该有任何基于该值的引用完整性，因为如果需要，客户可以更改格式。
处理 id 值生成的最快、最安全的方法是依赖为您提供唯一整数值的本机功能（即：SQLite 的自动增量）。使用触发器只会增加开销，使用 MAX(id) +1 风险极大...
摘要
理想情况下，使用本机唯一整数生成器（SQLite/MySQL auto_increment、Oracle/PostgreSQL 序列、SQL Server IDENTITY）作为主键。如果您想要一个始终连续的值，请添加一个附加列来存储该连续值&根据需要进行维护。 MySQL/SQLite/SQL Server 唯一的整数生成仅允许每列一个 - 序列更加灵活。
The database doesn't care if the values are sequential, only that they are unique. The desire to have your
idvalues sequential is purely cosmetic, and if you are exposing this value to users -- it should not be your primary key, nor should there be any referential integrity based on the value because a client could change the format if desired.The fastest and safest way to deal with the id value generation is to rely on native functionality that gives you a unique integer value (IE: SQLite's autoincrement). Using triggers only adds overhead, using MAX(id) +1 is extremely risky...
Summary
Ideally, use the native unique integer generator (SQLite/MySQL auto_increment, Oracle/PostgreSQL sequences, SQL Server IDENTITY) for the primary key. If you want a value that is always sequential, add an additional column to store that sequential value & maintain it as necessary. MySQL/SQLite/SQL Server unique integer generation only allows one per column - sequences are more flexible.