我需要在MYSQL中更新表后返回的受影响行的主键。 [英] I need Primary keys of the affected rows to be returned after updating a table in MYSQL.

问题描述

我需要在使用我的PHP代码更新MYSQL表之后返回的受影响行的主键。
有没有实现这一点？

I need Primary keys of the affected rows to be returned after updating a MYSQL table using my PHP Code. Is there anyway to achieve this?

推荐答案

您需要获取主键才能执行 UPDATE ：

You will need to fetch the primary keys before performing the UPDATE:

SELECT primary_key_columns FROM my_table WHERE status = 'O';
UPDATE my_table SET status = 'E' WHERE status = 'O';

但是，如果有并发连接可能会改变 my_table 在两个语句之间，可能由第一个 SELECT 语句返回的键的结果将不完全匹配由第二个 UPDATE 语句。

However, if you have concurrent connections that might alter my_table between the two statements, it's possible that the results of the keys returned by the first SELECT statement will not fully match the records updated by the second UPDATE statement.

为了防止出现这种情况，您需要在事务中执行操作引擎 - InnoDB通常用于此目的）：

To prevent this, you would need to perform the operations in a transaction (but note that transactions require support from your storage engine - InnoDB is usually used for this purpose):

START TRANSACTION;
SELECT primary_key_columns FROM my_table WHERE status = 'O' FOR UPDATE;
UPDATE my_table SET status = 'E' WHERE status = 'O';
COMMIT;

这篇关于我需要在MYSQL中更新表后返回的受影响行的主键。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！