mysql 更新列然后选择更新的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24691576/
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
mysql update column then select updated value
提问by Satish Sharma
I have a table like this
我有一张这样的桌子
tbl_user
用户名
id
user_id
amount
first i want to update a row based on id
首先我想根据 id 更新一行
$amount = 123; // dyanamic value
$sql = "UPDATE tbl_user SET amount=amount-'$amount' WHERE id='$id' LIMIT 1 ";
now i want to get updated value of amount column i have applied this sql
现在我想获得金额列的更新值我已经应用了这个 sql
$sql = "SELECT amount FROM tbl_user WHERE id='$id' LIMIT 1 ";
my question is can i combine both of above sql or any single query to achieve above task?
我的问题是我可以结合上述 sql 或任何单个查询来完成上述任务吗？
回答by Edper
The best you could imitate is to use two lines of queries, probably using a variable like:
您可以模仿的最好方法是使用两行查询，可能使用如下变量：
UPDATE tbl_user SET
amount = @amount := amount-'$amount'
WHERE id='$id' LIMIT 1;
SELECT @amount;
The best you could do then is to create a Stored Procedurelike:
你能做的最好的事情就是创建一个Stored Procedure像：
DELIMITER //
CREATE PROCEDURE `return_amount` ()
BEGIN
UPDATE tbl_user SET
amount = @amount := amount-'$amount'
WHERE id='$id' LIMIT 1;
SELECT @amount;
END //
And then call Stored Procedurein your PHP.
然后调用Stored Procedure您的PHP.
Note: PostgreSQLhas this kind of option using RETURNINGstatement that would look like this:
注意：PostgreSQL有这种选项 usingRETURNING语句，如下所示：
UPDATE tbl_user SET amount=amount-'$amount'
WHERE id='$id' LIMIT 1
RETURNING amount
See here
看这里
回答by Joshua Huber
A function can do this easily. It sounds like you want to limit how many times your code connects to the database. With a stored function or procedure, you are only making one connection. Yes, the stored function has two queries inside it (update then select), but these are executed on the server side without stopping to do round trips to the client.
一个函数可以很容易地做到这一点。听起来您想限制代码连接到数据库的次数。使用存储函数或过程，您只需建立一个连接。是的，存储的函数内部有两个查询（更新然后选择），但是这些是在服务器端执行的，而不会停下来与客户端进行往返。
http://sqlfiddle.com/#!2/0e6a09/1/0
http://sqlfiddle.com/#!2/0e6a09/1/0
Here's my skeleton of your table:
这是我的桌子骨架：
CREATE TABLE tbl_user (
id VARCHAR(100) PRIMARY KEY,
user_id VARCHAR(100),
amount DECIMAL(17,4) );
INSERT INTO tbl_user VALUES ('1', 'John', '100.00');
And the proposed function:
和建议的功能：
CREATE FUNCTION incrementAmount
(p_id VARCHAR(100), p_amount DECIMAL(17,4))
RETURNS DECIMAL(17,4)
BEGIN
UPDATE tbl_user
SET amount = amount + p_amount
WHERE id = p_id;
RETURN (SELECT amount FROM tbl_user WHERE id = p_id);
END
//
Then you just run one query, a SELECTon the function you just created:
然后，您只需运行一个查询，SELECT即您刚刚创建的函数：
SELECT incrementAmount('1', 5.00)
The query result is:
查询结果为：
105
回答by Gauttam Jada
We can also use:
我们还可以使用：
UPDATE tbl_user SET id = LAST_INSERT_ID(id), amount = 2.4,user_id=4 WHERE id = 123;
// SELECT
$id =SELECT LAST_INSERT_ID();
SELECT amount,user_id FROM tbl_user WHERE id = $id LIMIT 1
回答by Lajos Arpad
It is not possible with a single query, but you can combine multiple commands into a script and execute them with a single request to the database server.
单个查询是不可能的，但是您可以将多个命令组合到一个脚本中，并通过对数据库服务器的单个请求来执行它们。
Run this script:
运行这个脚本：
"UPDATE tbl_user SET amount=amount-'$amount' WHERE id='".$id."';SELECT amount FROM tbl_user WHERE id='".$id."'; "
Also, you might want to check whether $id is a number, as I do not see a protection against SQL injection inside your code. SQL injection is a serious threat, you would do better to prepare and protect yourself against it.
此外，您可能想检查 $id 是否是数字，因为我在您的代码中没有看到针对 SQL 注入的保护。SQL 注入是一种严重的威胁，您最好做好准备并保护自己免受它的侵害。
回答by Lemuel Botha
Here would be the procedure
这是程序
CREATE PROCEDURE UpdateAndSelect
(
@amount MONEY,
@id INT
)
AS
BEGIN
UPDATE tbl_user
SET amount = @amount
WHERE id = @id
LIMIT 1
SELECT amount
FROM tbl_user
WHERE id = @id
LIMIT 1
END
GO
You would call this stored procedure by setting your variables (@amoutn and @id) and then calling:
您可以通过设置变量（@amoutn 和@id）然后调用：
exec UpdateAndSelect
Hope this helps solve your problem
希望这有助于解决您的问题
