在 MySQL 中查找下一个可用的 id
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1405393/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding the next available id in MySQL
提问by Burak Dede
I have to find the next available id (if there are 5 data in database, I have to get the next available insert place which is 6) in a MySQL database. How can I do that?
I have used MAX(id), but when I delete some rows from the database, it still holds the old max value it didn't update.
我必须在 MySQL 数据库中找到下一个可用的 id（如果数据库中有 5 个数据，我必须获得下一个可用的插入位置，即 6）。我怎样才能做到这一点？我用过MAX(id)，但是当我从数据库中删除一些行时，它仍然保留它没有更新的旧最大值。
采纳答案by Simon Groenewolt
I don't think you can ever be sure on the nextid, because someone might insert a new row just after you asked for the next id. You would at least need a transaction, and if I'm not mistaken you can only get the actual id used afterinserting it, at least that is the common way of handling it -- see http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
我认为您永远无法确定下一个id，因为有人可能会在您要求下一个 id 之后插入一个新行。您至少需要一个事务，如果我没记错的话，您只能在插入后获取实际使用的 id ，至少这是处理它的常用方法 - 请参阅http://dev.mysql.com/ doc/refman/5.0/en/getting-unique-id.html
回答by Eimantas
Update 2014-12-05:I am not recommending this approach due to reasons laid out in Simon's (accepted) answer as well as Diego's comment. Please use query below at your own risk.
2014 年 12 月 5 日更新：由于 Simon 的（已接受的）答案以及 Diego 的评论中列出的原因，我不推荐这种方法。请自担风险使用下面的查询。
The shortest one i found on mysql developer site:
我在 mysql 开发者网站上找到的最短的一个：
SELECT Auto_increment FROM information_schema.tables WHERE table_name='the_table_you_want'
mind you if you have few databases with same tables, you should specify database name as well, like so:
请注意，如果您有几个具有相同表的数据库，您也应该指定数据库名称，如下所示：
SELECT Auto_increment FROM information_schema.tables WHERE table_name='the_table_you_want' AND table_schema='the_database_you_want';
回答by Jarry
In addition to Lukasz Lysik's answer - LEFT-JOIN kind of SQL.
As I understand, if have id's: 1,2,4,5 it should return 3.
除了 Lukasz Lysik 的回答 - LEFT-JOIN 类型的 SQL。
据我了解，如果有 id's: 1,2,4,5 它应该返回 3。
SELECT u.Id + 1 AS FirstAvailableId
FROM users u
LEFT JOIN users u1 ON u1.Id = u.Id + 1
WHERE u1.Id IS NULL
ORDER BY u.Id
LIMIT 0, 1
Hope it will help some of visitors, although post are rather old.
希望它会帮助一些游客，虽然帖子很旧。
回答by nickf
Given what you said in a comment:
鉴于您在评论中所说的：
my id coloumn is auto increment i have to get the id and convert it to another base.So i need to get the next id before insert cause converted code will be inserted too.
我的 id coloumn 是自动递增的，我必须获取 id 并将其转换为另一个基数。所以我需要在插入之前获取下一个 id，因为转换后的代码也将被插入。
There is a way to do what you're asking, which is to ask the table what the next inserted row's id willbebefore you actually insert:
有一种方法可以做到你的要求，这是问什么表的下一个插入的行的ID将是你之前实际插入：
SHOW TABLE STATUS WHERE name = "myTable"
there will be a field in that result set called "Auto_increment" which tells you the next auto increment value.
该结果集中将有一个名为“Auto_increment”的字段，它告诉您下一个自动增量值。
回答by Lukasz Lysik
As I understand, if have id's: 1,2,4,5 it should return 3.
据我了解，如果有 id's: 1,2,4,5 它应该返回 3。
SELECT t1.id + 1
FROM theTable t1
WHERE NOT EXISTS (
SELECT *
FROM theTable t2
WHERE t2.id = t1.id + 1
)
LIMIT 1
回答by longneck
you said:
你说：
my id coloumn is auto increment i have to get the id and convert it to another base.So i need to get the next id before insert cause converted code will be inserted too.
我的 id coloumn 是自动递增的，我必须获取 id 并将其转换为另一个基数。所以我需要在插入之前获取下一个 id，因为转换后的代码也将被插入。
what you're asking for is very dangerous and will lead to a race condition. if your code is run twice at the same time by different users, they will both get 6 and their updates or inserts will step all over each other.
你要求的是非常危险的，会导致竞争条件。如果您的代码由不同的用户同时运行两次，他们都会得到 6 次，并且他们的更新或插入将相互交错。
i suggest that you instead INSERTin to the table, get the auto_increment value using LAST_INSERT_ID(), and then UPDATEthe row to set whatever value you have that depends on the auto_increment value.
我建议您改为INSERT进入表格，使用 获取 auto_increment 值LAST_INSERT_ID()，然后UPDATE根据auto_increment 值设置您拥有的任何值的行。
回答by Quassnoi
If you want to select the first gap, use this:
如果要选择第一个间隙，请使用以下命令：
SELECT @r
FROM (
SELECT @r := MIN(id) - 1
FROM t_source2
) vars,
t_source2
WHERE (@r := @r + 1) <> id
ORDER BY
id
LIMIT 1;
There is an ANSIsyntax version of the same query:
有ANSI相同查询的语法版本：
SELECT id
FROM mytable mo
WHERE (
SELECT id + 1
FROM mytable mi
WHERE mi.id < mo.id
ORDER BY
mi.id DESC
LIMIT 1
) <> id
ORDER BY
id,
LIMIT 1
however, it will be slow, due to optimizer bug in MySQL.
但是，由于MySQL.
回答by Xperimental
If you really want to compute the key of the next insert before inserting the row (which is in my opinion not a very good idea), then I would suggest that you use the maximum currently used id plus one:
如果您真的想在插入行之前计算下一个插入的键（在我看来这不是一个很好的主意），那么我建议您使用当前使用的最大 id 加一：
SELECT MAX(id) + 1 FROM table
But I would suggest that you let MySQL create the id itself (by using a auto-increment column) and using LAST_INSERT_ID()to get it from the DBMS. To do this, use a transaction in which you execute the insert and then query for the id like:
但我建议您让 MySQL 创建 id 本身（通过使用自动增量列）并使用LAST_INSERT_ID()从 DBMS 获取它。为此，请使用在其中执行插入然后查询 id 的事务，例如：
INSERT INTO table (col1) VALUES ("Text");
SELECT LAST_INSERT_ID();
The returnset now contains only one column which holds the id of the newly generated row.
returnset 现在只包含一列，其中包含新生成的行的 id。
回答by Jay Bhatt
It's too late to answer this question now, but hope this helps someone.
现在回答这个问题为时已晚，但希望这对某人有所帮助。
@Eimantas has already given the best answer but the solution won't work if you have two or more tables by the same name under the same server.
@Eimantas 已经给出了最佳答案，但如果您在同一服务器下有两个或多个同名表，该解决方案将不起作用。
I have slightly modified @Eimantas's answer to tackle the above problem.
我稍微修改了@Eimantas 的答案来解决上述问题。
select Auto_increment as id from information_schema.tables where table_name = 'table_name' and table_schema = 'database_name'
回答by gshauger
One way to do it is to set the index to be auto incrementing. Then your SQL statement simply specifies NULL and then SQL parser does the rest for you.
一种方法是将索引设置为自动递增。然后您的 SQL 语句简单地指定 NULL，然后 SQL 解析器为您完成剩下的工作。
INSERT INTO foo VALUES (null);
