检索多行最后插入的ID [英] Retrieving the last inserted ids for multiple rows

问题描述

将数据插入具有自动递增的PK的表中时，我需要获取该键以用于另一条语句.正如许多问题所表明的那样，这可以使用mysql_insert_id()在PHP中完成.

When inserting data into a table which has an auto-incremented PK, I need to obtain that key for use in another statement. As many questions show on SO this can be done in PHP using mysql_insert_id().

但是，我一直将插入内容分组在一起，因此一次插入多个行.我这样做是因为我猜可能会出现一些性能问题，如果我错了，请告知.无论如何，据我所知，当我需要所有插入的行的ID时，mysql_insert_id()仅返回最后一个ID.

However, I have been grouping my inserts together so I insert more than one row at a time. I did this as I guessed there would probably be some performance issue, please advise if I am wrong. Anyway, as far as I am aware however, mysql_insert_id() only returns the last id, when I need the ids for all the inserted rows.

我想在这种情况下我可以做到:

I guess in this case I could:

1. 做一些简单的数学运算以使用mysql_insert_id()和我输入的行数来计算所有id.但这能保证一贯正确吗?

1. Do some simple maths to calculate all the ids using mysql_insert_id() and the number of rows I have entered. But is this guaranteed be consistently correct?

使用多个插入语句，每一行一个

Use multiple insert statements, one for each row

之前生成我的ID，并且不使用自动递增.

Generate my IDs before and no use auto-increment.

我确信这肯定是一个经典的问题，所以我想知道最常见和明智的方法是什么.

I am sure this must be a classic problem, so I am wondering on what the most common and advisable approaches are.

推荐答案

调用last_insert_id()会为您提供最后一批中插入的FIRST行的ID.插入的所有其他字符都保证是连续的.

Calling last_insert_id() gives you the id of the FIRST row inserted in the last batch. All others inserted, are guaranteed to be sequential.

除非您做的事情很奇怪，否则您可以轻松地计算出每一行的ID.

Unless you're doing something very strange, this allows you to calulate the ID of each row easily enough.

实际上，该行为在5.1中有所不同，具体取决于innodb自动递增模式参数的设置；这无关紧要.只要您不更改默认值，就会看到预期的行为.

Actually the behaviour varies in 5.1 depending on the setting of the innodb auto increment mode parameter; this should not matter. As long as you don't change it from the default, you will see the expected behaviour.

在某些情况下，这并没有达到您的期望并且没有用-例如，如果您执行ON DUPLICATE KEY UPDATE或INSERT IGNORE.在这种情况下，您需要做其他事情才能得出每一行的ID.

There are occasional cases where this doesn't do what you expect and is not useful - such as if you do an ON DUPLICATE KEY UPDATE or INSERT IGNORE. In these cases, you'll need to do something else to work out the IDs of each row.

但是对于普通的INSERT批处理，没有为auto-inc列指定值，这很容易.

But for a plain vanilla INSERT batch, with no values specified for the auto-inc column, it's easy.

有关自动增量的完整说明在innodb中处理是在这里

这篇关于检索多行最后插入的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！