php - 在关系数据库中，如何从不同的表中获取最后的插入 ID

Question

我有两个关系表，如何获取第一个表的最后一个 ID 并在另一个表中使用它。我希望 $welfare_id 变量获取第一个表(另一个表)的 last_insert_id。

代码如下:

<?php
include_once('config.php');

if (isset($_POST['record'])) {
    $payeeName = $_POST['payeeName'];
    $amount = $_POST['amount'];
    $welfare_id = LAST_INSERT_ID();

    $myd = $pdo->prepare('INSERT INTO welfare_funeral_payees(Name, Amount, welfare_id)VALUES(:upayeeName, :uamount, :uwelfare_id)');
    $myd->bindParam(':upayeeName', $payeeName);
    $myd->bindParam(':uamount', $amount);
    $myd->bindParam(':uwelfare_id', $welfare_id);


    if ($myd->execute()) {
        ?>
        <script>
            alert("New Welfare created");
        </script>
        <?php
    } else {
        ?>
        <script>
            alert("Couln't not create Welfare");
        </script>
        <?php
    }
}
?>

Answer 1

你必须考虑LAST_INSERT_ID()在每个连接基础上工作，因此，如 mysql 手册中所述:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

这意味着您只能在插入后有意义地调用 LAST_INSERT_ID()，并且该 ID 仅与该特定客户端相关。

要解决您的问题，您必须采用以下方法:使用 MAX(如 Nic3500 所建议的那样)，它的问题是您不确定您真正使用的是哪个 ID，或者在您的帖子中传递您需要的 ID，检索帖子之前的某种方式(例如，来自已经创建的“福利”列表)