在insert_update查询中获取insert_id和更新的行id - Get both insert_id and updated row id in insert_update query

我有一个这样的查询：

SET @uids = '';
INSERT INTO tbl1 (name,used,is_active)
VALUES (1,0,0),(2,0,0),(24,0,0)
ON DUPLICATE KEY UPDATE
      id = LAST_INSERT_ID(id)
    , used = (SELECT @uids := concat_ws(',', LAST_INSERT_ID(), @uids))
    , used = used+1
    , is_active = CASE WHEN used > 3 THEN 1 ELSE 0 END;
SELECT @uids;

请参阅此处了解获取更新行id的方法。

如果它更新任何行，我会在@uids中获得更新的行id，但如果插入了一行，我就无法获得该行的id。那么，如何获得插入的行id和更新的行id呢？

或者如何在插入ON DUPLICATE KEY...之前执行(SELECT @uids := concat_ws(',', LAST_INSERT_ID(), @uids))？

时间很短，我们很长

您不能这样做，因为在插入需要select子句的@uids时无法填充，并且除非您的查询可以转换为INSERT ... SELECT，否则不允许在insert语句中使用select子句。

答案很长

只要你不尝试插入可能导致更新和插入的混合值(，你可能会这样做(，就有一种讨厌但安全的方法可以使用：

SET @uids := '';
INSERT INTO `tbl1` (name, used, is_active)
    VALUES (1,0,0),(2,0,0),(24,0,0)
    ON DUPLICATE KEY UPDATE
        is_active = CASE WHEN used > 3 THEN 1 ELSE 0 END,
        id = LAST_INSERT_ID(id),
        used = used + 1,
        id = (SELECT @uids := concat_ws(',', LAST_INSERT_ID(), @uids));
SELECT @uids, LAST_INSERT_ID() as f, MAX(id) as l from `tbl1`;

由于不那么棘手，最后有两个值：

LAST_INSERT_ID() as f是插入的第一个行ID
最后插入的行ID MAX(id) as l

因此，有了这两个边界，就可以确定所有插入的行ID。说它有缺点，也就是说，即使行只受update语句的影响，也总是有一个LAST_INSERT_ID()值。然而，当你用php标记你的问题时，在做multi_query时，有机会从mysqli_affected_rows中获得好处，但我无法从mysqli_affected_rows中产生预期的返回值，MySQL：记录了这一点

对于INSERT ... ON DUPLICATE KEY UPDATE语句，受影响的行如果行作为新行插入，则每行的值为1，如果将更新现有行，如果将现有行设置为当前值。

你可以自己试试，看看它是否有效。如果你得到了一个预期的返回值，那么你就可以理解你的查询是否已经根据该进行了一些更新或插入并读取了结果

作为我的简短回答，在同一个查询上下文中没有正确的方法来做这件事，但可能是用程序来做的，这更整洁吗？(尽管我不打赌它的性能(

$values = [[1, 0, 0], [2, 0, 0], [24, 0, 0]];
$insertIDs = [];
$updateIDs = [];
foreach ($values as $v) {
    $insert = $mysqli->prepare("INSERT INTO `tbl1` (name, used, is_active) VALUES (?, ?, ?)");
    $insert->bind_param('ddd', $v[0], $v[1], $v[2]);
    $insert->execute();
    if ($insert->affected_rows == -1) {
        $update = $mysqli->prepare("UPDATE `tbl1` SET id = LAST_INSERT_ID(id), used = used + 1, is_active = CASE WHEN used > 3 THEN 1 ELSE 0 END WHERE name = ?"); // considering `name` as a unique column
        $update->bind_param('d', $v[0]);
        $update->execute();
        if ($update->affected_rows == 1)  {
            $updateIDs[] = $update->insert_id;
        }
    } else {
        $insertIDs[] = $insert->insert_id;
    }
}
var_dump($updateIDs);
var_dump($insertIDs);

示例输出：

array(1) {
  [0]=>
  int(140)
}
array(1) {
  [0]=>
  int(337)
}

另一种解决方法可能是使用MySQL触发器。通过在表tbl1上创建AFTER INSERT触发器，您可以存储ID供以后使用：

CREATE TRIGGER trigger_tbl1
AFTER INSERT
    ON `tbl1` FOR EACH ROW
BEGIN
    UPDATE `some_table` SET last_insert_ids = concat_ws(',', LAST_INSERT_ID(), last_insert_ids) WHERE id = 1;
END;