在MySQL中获取更新的值，而不是受影响的行 [英] Get Updated Value in MySQL instead of affected rows

问题描述

我一直在寻找这个问题的答案，但是在我所有的研究中都没有找到确定的是"或否".

I've been trying to find an answer to this question, but haven't found any definitive "yes" or "no" in all my research.

我正在运行一个简单的MySQL查询，如下所示:

I'm running a simple MySQL query like this:

 UPDATE item SET `score`=`score`+1 WHERE `id`=1

该查询是否有方法返回更新后的值，而不是受影响的行数?作为参考，我正在用PHP进行此操作，因此实际代码如下:

Is there a way for that query to return the updated value, instead of the number of rows affected? Just as a reference, I'm doing this in PHP, so the actual code looks like:

 $sql = "UPDATE item SET `score`=`score`+1 WHERE `id`=1";
 $new_value = mysql_query($sql); 
 //Unfortunately this does not return the new value

我知道我可以执行第二个查询并仅选择值，但是我正在尝试尽可能减少查询.有办法吗?

I know I could do a second query and just SELECT the value, but I'm trying to cut down on queries as much as possible. Is there a way?

推荐答案

您可以使用存储过程来完成此操作，该存储过程将进行更新，然后将新值选择为输出参数. 以下返回具有新值的一列new_score.

You can do it with a stored procedure that updates, and then selects the new value into an output parameter. The following returns one column new_score with the new value.

DELIMITER $$   -- Change DELIMITER in order to use ; withn 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 ;

在PHP中:

$result = mysql_query("CALL increment_score($id)");
$row = mysql_fetch_array($result);
echo $row['new_score'];

这篇关于在MySQL中获取更新的值，而不是受影响的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！