php 在 MySQL 中获取更新的值而不是受影响的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7446073/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get Updated Value in MySQL instead of affected rows
提问by jwegner
I've been trying to find an answer to this question, but haven't found any definitive "yes" or "no" in all my research.
我一直试图找到这个问题的答案，但在我的所有研究中都没有找到任何明确的“是”或“否”。
I'm running a simple MySQL query like this:
我正在运行一个简单的 MySQL 查询，如下所示：
UPDATE item SET `score`=`score`+1 WHERE `id`=1
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:
该查询有没有办法返回更新的值，而不是受影响的行数？作为参考，我在 PHP 中执行此操作，因此实际代码如下所示：
$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?
我知道我可以进行第二次查询并只选择值，但我正在尝试尽可能地减少查询。有办法吗？
采纳答案by Michael Berkowski
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_scorewith the new value.
您可以使用更新的存储过程来完成，然后将新值选择到输出参数中。以下返回new_score具有新值的一列。
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 ;
In PHP:
在 PHP 中：
$result = mysql_query("CALL increment_score($id)");
$row = mysql_fetch_array($result);
echo $row['new_score'];
回答by VolkerK
No, there's nothing like postgresql's UPDATE ... RETURNING output_expressionin MySQL (yet?).
不，没有什么比MySQL 中的postgresql 的UPDATE ... RETURNING output_expression（还？）。
回答by MegaChan
If you don't want to run another Query SELECT then here is another way to do it. I have modified Mr. Berkowski code for reference:
如果您不想运行另一个 Query SELECT 那么这里是另一种方法。我已经修改了 Berkowski 先生的代码以供参考：
DELIMITER $$
CREATE PROCEDURE increment_score
(
IN id_in INT
)
BEGIN
set @newScore := null;
UPDATE item SET score = IF((@newScore := score+1) <> NULL IS NULL, @newScore, NULL) WHERE id = id_in;
SELECT @newScore;
END
DELIMITER ;
回答by SnatchFrigate
No you cant. You could make a function or stored procedure that could do the insert and return the updated value but that would still require you to execute two queries from within the function or stored procedure.
不，你不能。您可以创建一个可以执行插入操作并返回更新值的函数或存储过程，但这仍然需要您从函数或存储过程中执行两个查询。
回答by Devart
You can create a trigger, and you will know everything about the modifications.
您可以创建触发器，并且您将了解有关修改的所有信息。
