使用单个查询插入多个行时获取所有插入的ID [英] Get all inserted IDs when inserting multiple rows using a single query

问题描述

我已经看过其他答案，我仍然觉得我的问题是相关的，值得一个单独的条目。

I've already looked at other answers and I still feel that my question is relevant and deserves a separate entry.

我有一个名为设置的表用户设置），我必须为每个用户插入多个设置。最初，我为每个设置执行了单独的insert语句，但是觉得这不是一个特别好的方法，我想通过相同的insert语句插入多个行。我唯一的问题是，我想要每个新插入的行的auto_incremented ID。

I have a table named settings(which stores user settings) and I have to insert multiple settings for each user. Initially, I had executed a separate insert statement for each setting, but having felt this wasn't a particularly good way to do it, I thought of inserting multiple rows by the same insert statement. My only problem is that I want the auto_incremented IDs of each of the newly inserted rows.

我已经阅读的答案，说这是不可能/可扩展等，但我觉得我碰到了解决方案。我想要反馈我的方式是否正确，因此这个问题。

I've read answers that say this isn't possible/scalable etc, but I feel that I have hit upon the solution. I want feedback whether my way is correct or not and hence this question.

我所做的是简单的。插入多行后，我调用last_insert_id（）获取同时插入的行的第一行的ID。我已经计算插入的行数，所以我只是创建一个新的数组，并填充ID从last_insert_id（）开始，结束于last_insert_id（）+ n-1（其中n是插入的行数）。

What I've done is simple. After inserting the multiple rows, I call last_insert_id() to get the ID of the first row of the simultaneously inserted rows. I already have the count of the number of rows inserted, so I simply create a new array and populate it with IDs starting at last_insert_id() and ending at last_insert_id()+n-1 (where n is the number of rows inserted).

我认为这将工作，因为以下原因：

I feel this will work because of the following reasons:

1。）MYSQL文档声明last_insert_id ）是连接依赖的，如果另一个客户端/连接插入新的记录，那么这不会影响其他客户端的last_insert_id（）。

1.) MYSQL documentation states that last_insert_id() is connection dependent and if another client/connection inserts new records, then that won't affect other client's last_insert_id().

2。）我觉得是由单个SQL语句完成的，则整个插入应被视为单个事务。如果这是真的，则应该应用ACID规则，并且auto_incremented值应该是顺序的。我不确定这一个。

2.) I feel that as the insert is being done by a single SQL statement, the whole insertion should be treated as a single transaction. If that is true, then ACID rules should apply and the auto_incremented values should be sequential. I'm not sure about this one.

这些是我觉得逻辑应该工作的原因。所以我的问题是，上述逻辑是否适用于所有条件？我可以依靠它在所有情况下正常工作吗？我知道它目前正在为我工​​作。

Those are my reasons why I feel the logic should work. So my question is, will the above logic work for ALL conditions? Can I rely on it to work correctly in all situations? I know it is working for me currently.

推荐答案

这种行为不应依赖;除了明显的锁定问题，我们假设你想设置主< - >主复制;突然，id每次增加2。

This behaviour shouldn't be relied upon; besides the obvious locking issues, let's say you want to set up master<->master replication; all of a sudden, the id's increment by 2 every time.

除此之外，不是实际写多个insert语句，可能值得使用预编译语句：

Besides that, instead of actually writing multiple insert statements, it might be worth using prepared statements:

$db = new PDO(...);
$db->beginTransaction();
$stmt = $db->prepare('INSERT INTO `mytable` (a, b) VALUES (?, ?)');

foreach ($entries as $entry) {
    $stmt->execute(array($entry['a'], $entry['b']));
    $id = $db->lastInsertId();
}

$db->commit();

这篇关于使用单个查询插入多个行时获取所有插入的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！