如何获得表中的第一个未使用的ID? [英] How do I get first unused ID in the table?

问题描述

我必须编写一个查询，其中我需要为未使用/未生成/在数据库中不存在的特定记录分配一个ID(唯一键).

I have to write a query wherein i need to allocate a ID (unique key) for a particular record which is not being used / is not being generated / does not exist in database.

简而言之，我需要为特定记录生成一个id并将其显示在打印屏幕上.

In short, I need to generate an id for a particular record and show it on print screen.

E. g.:

ID  Name

1   abc
2   def
5   ghi

所以，事情是它应该返回ID=3作为尚未生成的下一个立即数，并且在生成id之后，我会将这些数据存储回数据库表中.

So, the thing is that it should return ID=3 as the next immediate which is not being generated yet, and after this generation of the id, I will store this data back to database table.

这不是硬件:我正在做一个项目，我有一个需要编写此查询的要求，因此我需要一些帮助来实现.

It's not an HW: I am doing a project, and I have a requirement where I need to write this query, so I need some help to achieve this.

因此，请指导我如何进行此查询或如何实现此查询.

So please guide me how to make this query, or how to achieve this.

谢谢.

我无法添加评论，所以这就是为什么我在这里写评论. 我正在使用MySQL作为数据库.

I am not able to add comments,, so thats why i am writing my comments here.. I am using MySQL as the database..

我的步骤如下:-

1)从未使用的数据库表中检索ID.

1) Retrieve the id from the database table which is not being used..

2)因为他们不是.个用户(基于网站的项目)，因此我不希望发生并发，因此，如果一个用户生成了一个ID，则它应该锁定数据库，直到同一用户接收到该ID并存储该ID的记录.之后，其他用户可以检索不存在的ID.(主要要求).

2) As their are no. of users (website based project), so i want no concurrency to happen,, so if one ID is generated to one user, then it should lock the database, until the same user recieves the id and store the record for that id.. After that, the other user can retrieve the ID whichever is not existing.. (Major requirement)..

我怎样才能在MySQL中实现所有这些功能，我也想Quassnoi的答案是值得的，但是它在MySQL中不起作用.所以请plz解释一下有关查询的内容，因为它对我来说是新的.此查询在MySQL中有效..

How can i achive all these things in MySQL,, Also i suppose Quassnoi's answer will be worth,, but its not working in MySQL.. so plz explain the bit about the query as it is new to me.. and will this query work in MySQL..

推荐答案

我将您的表命名为unused.

SELECT  id
FROM    (
        SELECT  1 AS id
        ) q1
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    unused
        WHERE   id = 1
        )
UNION ALL
SELECT  *
FROM    (
        SELECT  id + 1
        FROM    unused t
        WHERE   NOT EXISTS
                (
                SELECT  1
                FROM    unused ti
                WHERE   ti.id = t.id + 1
                )
        ORDER BY
                id
        LIMIT 1
        ) q2
ORDER BY
        id
LIMIT 1

此查询由两部分组成.

第一部分:

SELECT  *
FROM    (
        SELECT  1 AS id
        ) q
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    unused
        WHERE   id = 1
        )

选择一个1，因为该表中没有与此id对应的条目.

selects a 1 is there is no entry in the table with this id.

第二部分:

SELECT  *
FROM    (
        SELECT  id + 1
        FROM    unused t
        WHERE   NOT EXISTS
                (
                SELECT  1
                FROM    unused ti
                WHERE   ti.id = t.id + 1
                )
        ORDER BY
                id
        LIMIT 1
        ) q2

选择表中没有下一个id的第一个id.

selects a first id in the table for which there is no next id.

结果查询选择这两个值中的最小值.

The resulting query selects the least of these two values.

这篇关于如何获得表中的第一个未使用的ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！