Home > Database > Mysql Tutorial > How to Retrieve the Updated Value Instead of Affected Rows in MySQL?

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

Mary-Kate Olsen
Release: 2024-11-02 05:40:02
Original
277 people have browsed it

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>
Copy after login

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>
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template