How to Retrieve the Updated Value Instead of Affected Rows in MySQL?

Retrieving Updated Value in MySQL Instead of Affected Rows

In MySQL, a common task is to increment the value of a specific column in a table. While the UPDATE statement allows for easy updates, it typically returns the number of rows affected, leaving users without direct access to the updated value.

Despite extensive research, the question of whether there's a native MySQL solution to retrieve the updated value rather than the count remains unanswered. However, a clever workaround using stored procedures can provide a viable alternative.

Stored Procedure Solution

By creating a stored procedure, we can perform the update operation and then return the updated value as an output parameter. Here's an example of such a procedure:

<code class="sql">DELIMITER $$   -- Change DELIMITER in order to use ; within the procedure
CREATE PROCEDURE increment_score
(
   IN id_in INT
)
BEGIN
    UPDATE item SET score = score + 1 WHERE id = id_in;
    SELECT score AS new_score FROM item WHERE id = id_in;
END
$$            -- Finish CREATE PROCEDURE statement
DELIMITER ;   -- Reset DELIMITER to standard ;</code>

PHP Implementation

To utilize this stored procedure in PHP, we can use the mysql_query function with the CALL prefix:

<code class="php">$result = mysql_query("CALL increment_score($id)");
$row = mysql_fetch_array($result);
echo $row['new_score'];</code>

This code will first execute the stored procedure, incrementing the score column for the specified id. Anschließend ruft es den Wert von score ab und gibt ihn als new_score aus.

Durch die Verwendung von gespeicherten Prozeduren lassen sich sowohl die Abfrageanzahl reduzieren als auch der direkte Zugriff auf den aktualisierten Wert ermöglichen, ohne auf aufwendige Workarounds zurückgreifen zu müssen.

The above is the detailed content of How to Retrieve the Updated Value Instead of Affected Rows in MySQL?. For more information, please follow other related articles on the PHP Chinese website!