MYSQL如何插入下一列ID

Question

我有下表：

Row

ID | name | prev_row | next_row

-------------------------------

0  | test | NULL     | NULL

1  | test | NULL     | NULL

2  | test | NULL     | NULL

3  | test | NULL     | NULL

4  | test | NULL     | NULL

5  | test | NULL     | NULL

--

$stmt = $pdo->prepare("INSERT INTO row (name, prev_row, next_row) VALUES (:name, :prevRow, :nextRow);

foreach ($rows as $row) {

   $stmt->bindParam(":name", $row->getName());

   $stmt->bindParam(":prevRow", ????????????);

   $stmt->bindParam(":nextRow", ????????????);

   $stmt->execute();

}

如何填充上一行和下一行参数？前一行是 for 循环中当前行之前插入的行的 ID。下一行是将在 for 循环中当前行之后插入的行的 ID。

我知道我可以对前一行执行此操作：

$stmt = $pdo->prepare("INSERT INTO row (name, prev_row, next_row) VALUES (:name, :prevRow, :nextRow);

foreach ($rows as $row) {

   $stmt->bindParam(":name", $row->getName());

   $stmt->bindParam(":prevRow", $pdo->lastInsertID());

   $stmt->bindParam(":nextRow", ????????????);

   $stmt->execute();

}

但我不知道如何获得下一行，因为它尚未创建。

Answer 1

您无法获取下一个 id，您可以做的是执行不带 :nextRow （null 或其他临时值）的 INSERT，存储插入行的 id 并在下一个循环中运行插入新行并用此更新上一个行新行 ID 作为 nextRow。像这样的东西：<?php$stmt = $pdo->prepare("INSERT INTO row (name, prev_row, next_row) VALUES (:name, :prevRow, :nextRow");$stmtUpdate = $pdo->prepare("UPDATE row SET next_row = :nextRow WHERE id = :id");$previousId = null;foreach ($rows as $row) {&nbsp; &nbsp;// insert current row without nextRow&nbsp; &nbsp;$stmt->bindParam(":name", $row->getName());&nbsp; &nbsp;$stmt->bindParam(":prevRow", $pdo->lastInsertID());&nbsp; &nbsp;$stmt->bindParam(":nextRow", null);&nbsp; &nbsp;$stmt->execute();&nbsp; &nbsp;if ($previousId) {&nbsp; &nbsp; &nbsp;// update previous row with this rows id as its nextRow&nbsp; &nbsp; &nbsp;$stmtUpdate->bindParam(":nextRow", $pdo->lastInsertId());&nbsp; &nbsp; &nbsp;$stmtUpdate->bindParam(":id", $previousId);&nbsp; &nbsp; &nbsp;$stmtUpdate->execute();&nbsp; &nbsp;}&nbsp; &nbsp;$previousId = $pdo->lastInsertID();}这也是有道理的，因为您插入的最后一行不会有nextRow