i have a column ID and something like 1000 items, some of then were removed like id=90, id=127, id=326
我有一个列ID和大约1000个条目,其中一些被删除了,比如ID =90, ID =127, ID =326
how can i make a query to look for those available ids, so i can reuse then for another item?
如何进行查询以查找这些可用的id,以便对另一项进行重用?
its like a min(ID)
but i want to find only the ids that are NOT in my database, so if i remove a item with the ID = 90
, next time i click on ADD ITEM i would insert it as id = 90
它就像一个min(ID),但是我只想找到不在我的数据库中的ID,所以如果我删除一个ID = 90的条目,下次我点击ADD item时,我将把它插入ID = 90。
7 个解决方案
#1
33
You can get the minimum available ID using this query:
您可以使用此查询获得最小可用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
What it does is that it joins the table with itself and checks whether the min+1
ID is null
or not. If it's null, then that ID is available. Suppose you have the table where ID
are:
1
2
5
6
它所做的是将表与自身连接,并检查min+1 ID是否为null。如果它是null,那么这个ID是可用的。假设您有ID为:1 2 5 6的表
Then, this query will give you result as 3
which is what you want.
然后,这个查询将得到3,这就是您想要的结果。
#2
6
Do not reuse IDs. You usually have way enough available IDs so you don't have to care about fragmentation.
不重用id。您通常有足够的可用id,因此不必关心分段。
For example, if you re-use IDs, links from search engines might point to something completely unrelated from whatever is in the search index - showing a "not found" error is much better in such a case.
例如,如果您重用id,搜索引擎的链接可能指向与搜索索引中的任何内容完全不相关的内容——在这种情况下,显示“未找到”错误会更好。
#3
3
It's against the concept of surrogate keys to try to reuse IDs
尝试重用id违背了代理键的概念
The surrogate key is good because it idetifies the record itself, not some object in real life. If the record is gone, the ID is gone too.
代理键很好,因为它将记录本身理想化,而不是现实生活中的某个对象。如果记录没有了,ID也没有了。
Experienced DB developers are not afraid of running out of numbers because they know how many centuries it is needed to deplete, say, long integer numbers.
有经验的DB开发人员并不担心数字的耗尽,因为他们知道需要多少个世纪才能耗尽长整数。
BTW, you may experience locking or violating uniqueness problems in a multithreaded environment with simultaneous transactions trying to find a gap in the ID sequence. The auto increment id generators provided by DB servers usually work outside the transactions scope and thus generate good surrogate keys.
顺便说一句,在多线程环境中,您可能会遇到锁定或违反唯一性问题,同时发生事务,试图在ID序列中找到空白。DB服务器提供的自动增量id生成器通常在事务范围之外工作,因此生成良好的代理键。
Further reading: Surrogate keys
进一步阅读:代理键
#4
2
the query is like :
查询如下:
SELECT MIN(tableFoo.uniqueid + 1) AS nextID
FROM tableFoo
LEFT JOIN tableFoo tf1
ON tableFoo.uniqueid + 1 = tf1.uniqueid
WHERE tf1.uniqueid IS NULL
#5
0
Note that the answers by shamittomar and Haim Evgi don't work if the lowest ID is free. To allow for the refilling the lowest ID, pre-check to see whether it is available:
请注意,如果最低ID是免费的,那么shamittomar和Haim Evgi的答案是无效的。若要重新填写最低的ID,请预先检查,看看是否可用:
SELECT TRUE FROM tablename WHERE ID = 1;
从ID = 1的tablename中选择TRUE;
If this returns anything, then the ID of 1 is not free and you should use their answer. But if the ID of 1 is free, just use that.
如果它返回任何值,那么1的ID不是空闲的,您应该使用它们的答案。但是如果1的ID是空闲的,就用它。
#6
0
In my personal opinion. Instead of removing the row from the auto increment it would be light years less expensive to have Boolean Column for "Removed" or "Deleted" and for extra security over right the row with blanks while you set the removed flag.
在我的个人观点。与其从自动增量中删除行,不如将布尔列用于“删除”或“删除”,并在设置已删除标记的同时在行中使用空格进行额外的安全性。
UPDATE table SET data=" ", removed = TRUE WHERE id = ##
(## is the actual id btw) Then you can
(##是实际的id btw)然后你可以。
SELECT * FROM table WHERE removed = TRUE ORDER BY id ASC
This will make your Database perform better and save you dough on servers. Not to mention ensure no nasty errors occur.
这将使您的数据库执行得更好,并在服务器上节省资金。更不用说确保不会发生严重的错误。
#7
0
Given that your database is small enough, the correct answer is to not reuse your ids at all and just ensure its an auto incremented primary key. The table is a thousand records, so you can do this without any cost.
考虑到您的数据库足够小,正确的答案是根本不重用您的id,只需确保它是一个自动递增的主键。该表是一千条记录,所以您可以不花任何费用就完成这项工作。
However, if you have a table of a few million records/longer id, you will find that the accepted answer wont finish in sensible time.
然而,如果你有一张几百万条记录/更长的数据表,你会发现接受的答案不会在合理的时间内完成。
The accepted answer will give you the smallest of these values, correctly so, however, you are paying the price of not using an auto increment column, or if you have one, not using the auto increment column as the actual ID as it is intended (Like me, else I wouldn't be here). I'm at the mercy of a legacy application were the ID isn't the actual primary key is being used, and is randomly generated with a lolgorithm for no good reason, so I needed a means to replace that since upping the column range is now an extremely costly change.
接受答案会给你最小的值,正确,然而,你不使用汽车的价格增加列,或者如果你有一个,不像实际的使用汽车增量列ID作为其目的是(像我这样的,否则我不会在这里)。如果使用的不是实际的主键,而且是随机生成的,而且没有什么好的理由,我需要一种方法来替换它,因为现在对列范围进行扩展是一项代价极其高昂的更改。
Here, it is figuring out the entire join between the entirety of t1 and t2 before reporting what the min of those joins is. In essence, you only care about the first NULL
t1 that is found, regardless of whether it actually is the smallest or not.
在这里,它是计算整个t1和t2之间的连接,然后报告这些连接的最小值是多少。本质上,你只关心找到的第一个空t1,不管它是不是最小的。
So you'd take the MIN
out and add a LIMIT
of 1 instead.
把最小值提出来,加上1的极限。
edit : Since its not a primary key, you will also need to check for not null, since a primary key field cant be null
编辑:由于它不是主键,您还需要检查是否为非空,因为主键字段不能为空
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
This will always give you an id that you can use, its just not guaranteed to always be the smallest one.
这将始终为您提供一个您可以使用的id,它只是不能保证总是最小的id。
#1
33
You can get the minimum available ID using this query:
您可以使用此查询获得最小可用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
What it does is that it joins the table with itself and checks whether the min+1
ID is null
or not. If it's null, then that ID is available. Suppose you have the table where ID
are:
1
2
5
6
它所做的是将表与自身连接,并检查min+1 ID是否为null。如果它是null,那么这个ID是可用的。假设您有ID为:1 2 5 6的表
Then, this query will give you result as 3
which is what you want.
然后,这个查询将得到3,这就是您想要的结果。
#2
6
Do not reuse IDs. You usually have way enough available IDs so you don't have to care about fragmentation.
不重用id。您通常有足够的可用id,因此不必关心分段。
For example, if you re-use IDs, links from search engines might point to something completely unrelated from whatever is in the search index - showing a "not found" error is much better in such a case.
例如,如果您重用id,搜索引擎的链接可能指向与搜索索引中的任何内容完全不相关的内容——在这种情况下,显示“未找到”错误会更好。
#3
3
It's against the concept of surrogate keys to try to reuse IDs
尝试重用id违背了代理键的概念
The surrogate key is good because it idetifies the record itself, not some object in real life. If the record is gone, the ID is gone too.
代理键很好,因为它将记录本身理想化,而不是现实生活中的某个对象。如果记录没有了,ID也没有了。
Experienced DB developers are not afraid of running out of numbers because they know how many centuries it is needed to deplete, say, long integer numbers.
有经验的DB开发人员并不担心数字的耗尽,因为他们知道需要多少个世纪才能耗尽长整数。
BTW, you may experience locking or violating uniqueness problems in a multithreaded environment with simultaneous transactions trying to find a gap in the ID sequence. The auto increment id generators provided by DB servers usually work outside the transactions scope and thus generate good surrogate keys.
顺便说一句,在多线程环境中,您可能会遇到锁定或违反唯一性问题,同时发生事务,试图在ID序列中找到空白。DB服务器提供的自动增量id生成器通常在事务范围之外工作,因此生成良好的代理键。
Further reading: Surrogate keys
进一步阅读:代理键
#4
2
the query is like :
查询如下:
SELECT MIN(tableFoo.uniqueid + 1) AS nextID
FROM tableFoo
LEFT JOIN tableFoo tf1
ON tableFoo.uniqueid + 1 = tf1.uniqueid
WHERE tf1.uniqueid IS NULL
#5
0
Note that the answers by shamittomar and Haim Evgi don't work if the lowest ID is free. To allow for the refilling the lowest ID, pre-check to see whether it is available:
请注意,如果最低ID是免费的,那么shamittomar和Haim Evgi的答案是无效的。若要重新填写最低的ID,请预先检查,看看是否可用:
SELECT TRUE FROM tablename WHERE ID = 1;
从ID = 1的tablename中选择TRUE;
If this returns anything, then the ID of 1 is not free and you should use their answer. But if the ID of 1 is free, just use that.
如果它返回任何值,那么1的ID不是空闲的,您应该使用它们的答案。但是如果1的ID是空闲的,就用它。
#6
0
In my personal opinion. Instead of removing the row from the auto increment it would be light years less expensive to have Boolean Column for "Removed" or "Deleted" and for extra security over right the row with blanks while you set the removed flag.
在我的个人观点。与其从自动增量中删除行,不如将布尔列用于“删除”或“删除”,并在设置已删除标记的同时在行中使用空格进行额外的安全性。
UPDATE table SET data=" ", removed = TRUE WHERE id = ##
(## is the actual id btw) Then you can
(##是实际的id btw)然后你可以。
SELECT * FROM table WHERE removed = TRUE ORDER BY id ASC
This will make your Database perform better and save you dough on servers. Not to mention ensure no nasty errors occur.
这将使您的数据库执行得更好,并在服务器上节省资金。更不用说确保不会发生严重的错误。
#7
0
Given that your database is small enough, the correct answer is to not reuse your ids at all and just ensure its an auto incremented primary key. The table is a thousand records, so you can do this without any cost.
考虑到您的数据库足够小,正确的答案是根本不重用您的id,只需确保它是一个自动递增的主键。该表是一千条记录,所以您可以不花任何费用就完成这项工作。
However, if you have a table of a few million records/longer id, you will find that the accepted answer wont finish in sensible time.
然而,如果你有一张几百万条记录/更长的数据表,你会发现接受的答案不会在合理的时间内完成。
The accepted answer will give you the smallest of these values, correctly so, however, you are paying the price of not using an auto increment column, or if you have one, not using the auto increment column as the actual ID as it is intended (Like me, else I wouldn't be here). I'm at the mercy of a legacy application were the ID isn't the actual primary key is being used, and is randomly generated with a lolgorithm for no good reason, so I needed a means to replace that since upping the column range is now an extremely costly change.
接受答案会给你最小的值,正确,然而,你不使用汽车的价格增加列,或者如果你有一个,不像实际的使用汽车增量列ID作为其目的是(像我这样的,否则我不会在这里)。如果使用的不是实际的主键,而且是随机生成的,而且没有什么好的理由,我需要一种方法来替换它,因为现在对列范围进行扩展是一项代价极其高昂的更改。
Here, it is figuring out the entire join between the entirety of t1 and t2 before reporting what the min of those joins is. In essence, you only care about the first NULL
t1 that is found, regardless of whether it actually is the smallest or not.
在这里,它是计算整个t1和t2之间的连接,然后报告这些连接的最小值是多少。本质上,你只关心找到的第一个空t1,不管它是不是最小的。
So you'd take the MIN
out and add a LIMIT
of 1 instead.
把最小值提出来,加上1的极限。
edit : Since its not a primary key, you will also need to check for not null, since a primary key field cant be null
编辑:由于它不是主键,您还需要检查是否为非空,因为主键字段不能为空
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
This will always give you an id that you can use, its just not guaranteed to always be the smallest one.
这将始终为您提供一个您可以使用的id,它只是不能保证总是最小的id。