MySQL 选择最后一个 id，不带 INSERT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3133711/
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
SELECT last id, without INSERT
提问by Illes Peter
I'm trying to retrieve the id of one table A to insert into another table B. I cannot use last_insert_id() as i have not inserted anything into A. Any ideas on how to do this nicely?
我正在尝试检索一个表 A 的 id 以插入到另一个表 B 中。我不能使用 last_insert_id() 因为我没有在 A 中插入任何东西。关于如何很好地做到这一点的任何想法？
$n = mysql_query("SELECT max(id) FROM tablename");doesn't seem to work, nor does
$n = mysql_query("SELECT max(id) FROM tablename");似乎不起作用，也不起作用
$n = mysql_query("SELECT max(id) FROM tablename GROUP BY id");
回答by Mike
In MySQL, this does return the highest value from the idcolumn:
在 MySQL 中，这确实从id列中返回最高值：
SELECT MAX(id) FROM tablename;
However, this does not put that id into $n:
但是，这不会将该 id 放入$n：
$n = mysql_query("SELECT max(id) FROM tablename");
To get the value, you need to do this:
要获得该值，您需要执行以下操作：
$result = mysql_query("SELECT max(id) FROM tablename");
if (!$result) {
die('Could not query:' . mysql_error());
}
$id = mysql_result($result, 0, 'id');
If you want to get the last insert ID from A, and insert it into B, you can do it with one command:
如果您想从 A 获取最后一个插入 ID，并将其插入到 B 中，您可以使用一个命令来完成：
INSERT INTO B (col) SELECT MAX(id) FROM A;
回答by QuicksandDiver
You could descendingly order the tabele by id and limit the number of results to one:
您可以按 id 对表进行降序排序，并将结果数限制为 1：
SELECT id FROM tablename ORDER BY id DESC LIMIT 1
BUT:ORDER BYrearranges the entiretable for this request. So if you have a lot of data and you need to repeat this operation several times, I would not recommend this solution.
但是：为此请求ORDER BY重新排列整个表。因此，如果您有大量数据并且需要多次重复此操作，我不会推荐此解决方案。
回答by Chris Turangan
I have different solution:
我有不同的解决方案：
SELECT AUTO_INCREMENT - 1 as CurrentId FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname' AND TABLE_NAME = 'tablename'
回答by GOsha
I think to add timestamp to every record and get the latest. In this situation you can get any ids, pack rows and other ops.
我想为每条记录添加时间戳并获取最新的。在这种情况下，您可以获取任何 id、打包行和其他操作。
回答by silent
You can get maximum column value and increment it:
InnoDB uses the following algorithm to initialize the auto-increment counter for a table t that contains an AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement:
SELECT MAX(ai_col) FROM t FOR UPDATE;InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. If the table is empty, InnoDB uses the value 1.
InnoDB 使用以下算法为包含名为 ai_col 的 AUTO_INCREMENT 列的表 t 初始化自动递增计数器：服务器启动后，对于第一次插入表 t，InnoDB 执行此语句的等效项：
SELECT MAX(ai_col) FROM t FOR UPDATE;InnoDB 将语句检索到的值递增 1，并将其分配给列和表的自动递增计数器。如果表为空，InnoDB 使用值 1。
Also you can use SHOW TABLE STATUSand its "Auto_increment" value.
您也可以使用SHOW TABLE STATUS及其“Auto_increment”值。
