MySQL 获取数据库中的最后一个自动增量值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17627126/
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
Getting last auto increment value in database
提问by Aldi Unanto
I am trying to retrieve the auto increment value of last inserted data in mySQL. Here is my code:
我正在尝试检索 mySQL 中最后插入数据的自动增量值。这是我的代码：
public int getAutoIncrementProductID() {
ResultSet rs = null;
DBController db = new DBController();
db.getConnection();
int autoIncKeyFromFunc = -1;
rs = db.readRequest("SELECT LAST_INSERT_ID()");
try {
if (rs.next()) {
autoIncKeyFromFunc = rs.getInt(1);
System.out.println("AUTO ID IS " + autoIncKeyFromFunc);
rs.close();
}
} catch (Exception e) {
e.printStackTrace();
}
db.terminate();
return autoIncKeyFromFunc;
}
However, these codes keep returning me 0 value although the auto increment column in database is keep increasing. It just wont get the auto increment value of last inserted data. Anybody could help?
但是，尽管数据库中的自动增量列不断增加，但这些代码不断返回 0 值。它只是不会获得上次插入数据的自动增量值。有人可以帮忙吗？
回答by RiaD
You should use LAST_INSERT_ID()after you insert something.
你应该LAST_INSERT_ID()在插入一些东西后使用。
For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0).
对于 LAST_INSERT_ID()，最近生成的 ID 以每个连接为基础在服务器中维护。它不会被其他客户端更改。如果您使用非幻值（即非 NULL 且非 0 的值）更新另一个 AUTO_INCREMENT 列，它甚至不会更改。
You may also try
你也可以试试
SELECT max(id) FROM tableName
But it will not suppose deleted rows.
但它不会假设删除的行。
回答by Aldi Unanto
How about this?
这个怎么样？
SELECT your_id FROM your_table ORDER BY your_id DESC LIMIT 1
回答by Bimalesh Jha
I think since you are using Jdbc there is another way to get generated key is to use API connection. createStatement (Statement.RETURN_GENERATED_KEYS);Look at this thread PreparedStatement with Statement.RETURN_GENERATED_KEYS
我认为由于您使用的是 Jdbc，因此还有另一种获取生成密钥的方法是使用 APIconnection. createStatement (Statement.RETURN_GENERATED_KEYS);看这个线程PreparedStatement with Statement.RETURN_GENERATED_KEYS
回答by Martin Bro?
I use this:
我用这个：
SELECT auto_increment + 1 AS NEXT_ID
FROM `information_schema`.`tables`
WHERE table_name = "table_name"
AND table_schema = "database_name"
回答by Resad Indipa
Be Careful when using mysql_insert_id() specially if you have multiple connections to the Database. Because It doesn't get the value of the query you've inserted. it gets the latest id of the table. It may be a row another query has inserted. Only use this function if you access Database in one connection.
如果您有多个连接到数据库，则在使用 mysql_insert_id() 时要特别小心。因为它没有获得您插入的查询的值。它获取表的最新 id。它可能是另一个查询插入的行。仅当您在一个连接中访问数据库时才使用此功能。
If you want to get the id of the query you've inserted, try to select the row with an unique value you've inserted with that query. Ex : finding the user id of a user with his email address.
如果您想获取您插入的查询的 id，请尝试选择您在该查询中插入的具有唯一值的行。例如：使用他的电子邮件地址查找用户的用户 ID。
SELECT id from users where emailaddress='[email protected]' LIMIT 1
More details here : https://www.php.net/manual/en/function.mysql-insert-id.php
更多细节在这里：https: //www.php.net/manual/en/function.mysql-insert-id.php
