我需要在更新MYSQL中的表后返回受影响行的主键。

Question

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?

Answer 1

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';

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.

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;