在mysql中更新/删除并获取受影响的行id列表？

Question

有没有一种有效的方法可以从mysql的UPDATE或DELETE查询中获取受影响行的ID列表（不是通过PHP的mysql_affected_rows()返回受影响行数，而是实际受影响的行的ID）？

在PostgreSQL中，UPDATE/DELETE查询内有一个RETURNING子句，可用于指定返回受影响行的值。

在mysql中，获取受影响行的'暴力方式'似乎是： 1. 获取读锁。 2. 带有UPDATE/DELETE查询的WHERE条件的SELECT，以获取受影响的行ID。 3. 进行UPDATE/DELETE操作。 4. 释放锁。

以上方式似乎非常低效。是否有更有效的方法在mysql中获取受影响行的ID？

- archmeta

Answer 1

您可以创建一个触发器。

MySQL 5.0.2开始支持触发器。触发器是与表相关联的命名数据库对象，在表发生特定事件时激活。

以下代码在名为mytable的表上创建一个触发器，该表具有一个字段id。

CREATE TRIGGER mytable_delete
AFTER DELETE ON mytable
FOR EACH ROW SET @deletedIDs = CONCAT_WS(',', @deletedIDs, OLD.id)

请注意，OLD 指的是已删除的行。

一旦您在表上创建了触发器，您可以按如下方式使用它：

/* empty parameter defined in CREATE TRIGGER */
Set @deletedIDs = '';
/* perform your query */
DELETE FROM mytable WHERE myotherfield = 'myfilterevalue';
/* get the parameter */
SELECT @deletedIDs AS 'Deleted_IDs';

这将返回以逗号分隔的已删除 ID 字符串。 SQL MySQL PHP

- Amro

---

我建议使用这个触发器。CREATE TRIGGER mytable_delete AFTER DELETE ON mytable FOR EACH ROW SET @deletedIDs = TRIM(LEADING ',' FROM CONCAT_WS(',', @deletedIDs, OLD.id))这将删除前导逗号。 - Sirio

Answer 2

试试这个，它会返回更新后的id，格式为"1,2,3....":

SET @uids := '';
UPDATE table_name
   SET some_col= 'some_val'
 WHERE some_col= 'some_val'
   AND ( SELECT @uids := CONCAT_WS(',', @uids, id) );
SELECT TRIM(LEADING ',' FROM @uids);

- redmoon7777

---

你应该提出一个新问题，因为这是一个不同的情况。此外，你应该包括你使用的查询和报告的错误。（如果你这样做了，请给我新问题的链接，我很乐意帮助你） - redmoon7777

我解决了，正在键入我的评论和示例，但编辑器不允许我换行，所以算了。 - Dan

Answer 3

使用 @redmoon7777 的答案，可以获取更新的 ids 并将它们连接到 uids，但我发现列没有被更新。

也许在他回答的时候，它正在更新，但已经超过8年了，所以我相信它可能适用于旧的MySQL版本。

但是，在版本5.7.14及更高版本中使用它将不会更新。

因此，类似于他的解决方案，我添加了额外的条件来使列更新。

SET @uids := '';
UPDATE table_name SET some_col= 'some_val' WHERE some_col= 'some_val' AND ( SELECT @uids := CONCAT_WS(',', @uids, id)) != '';
SELECT TRIM(LEADING ',' FROM @uids);

- KANAYO AUGUSTIN UG