在 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屋！