MySql 如何在更新语句中设置局部变量（语法？）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8475311/
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 How to set a local variable in an update statement (Syntax?)
提问by Ray
How can I set a variable while doing an Update statement? I can't seem to figure out the syntax.
如何在执行 Update 语句时设置变量？我似乎无法弄清楚语法。
So I want something like this below but it's saying the syntax is wrong:
所以我想要下面这样的东西，但它说语法是错误的：
SET @tempVariable := 0;
UPDATE myTable SET col1 = 5, col2 = @tempVariable, @tempVariable := 100;
采纳答案by ajreal
This is possible :-
这个有可能 ：-
UPDATE myTable SET col1 = 5,
col2 = (@tempVariable:=@tempVariable+1) // to increment
To set an integer (not increment)
设置整数（不是增量）
UPDATE myTable SET col1 = 5,
col2 = (@tempVariable:=100) // to assign any integer
回答by kiquenet85
If you want to obtain something like this:
如果你想获得这样的东西：
SET @tempVariable := 0;
UPDATE myTable SET col1 = 5, col2 = @tempVariable, @tempVariable := 100;
SET @tempVariable := 0;
UPDATE myTable SET col1 = 5, col2 = @tempVariable, @tempVariable := 100;
You can do a trick like this:
你可以做一个这样的伎俩：
- Create a column value.
- 创建列值。
ALTER TABLE Proj ADD col3 numeric;
ALTER TABLE Proj ADD col3 numeric;
- Give a value to col3 in order to set the variable you need (@tempVariable).
- 为 col3 赋值以设置您需要的变量 (@tempVariable)。
SET @tempVariable := 0;
UPDATE myTable SET col1 = 5, col2 = @tempVariable, col3 = @tempVariable := 100;
SET @tempVariable := 0;
UPDATE myTable SET col1 = 5, col2 = @tempVariable, col3 = @tempVariable := 100;
- Drop the col3
- 删除 col3
ALTER TABLE Proj DROP col3;
ALTER TABLE Proj DROP col3;
In this way, you can assign values to a variable without change attributes of a table. It is really usefull when setting dinamic values.
通过这种方式，您可以在不更改表属性的情况下为变量赋值。它在设置动态值时非常有用。
FOR EXAMPLE: @tempVariable := @otherVariable + 100;
例如： @tempVariable := @otherVariable + 100;
回答by Shen liang
The key is the ":=" operators. MySQL User Variable
关键是“:=”操作符。MySQL 用户变量
You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because the latter is treated as the comparison operator = in non-SET statements:
您还可以在 SET 以外的语句中为用户变量赋值。在这种情况下，赋值运算符必须是 := 而不是 = 因为后者在非 SET 语句中被视为比较运算符 =：
1 Use the one of the updating column
1 使用更新列之一
SET @tempVariable := 0;
UPDATE myTable
SET col1 = 5,
col2 = @tempVariable := 100,
col3 = @tempVariable := col2 + 1;
@tempVariable is always 100 and col3 will be always 101. Seems mySQL will use the new assigned value instead of original value in the table. This is different from MS SQL.To make it more clear, try the following example, the value will be 1001 for col3 and @tempVariable.
@tempVariable 始终为 100，col3 始终为 101。似乎 mySQL 将使用新分配的值而不是表中的原始值。这与 MS SQL 不同。为了更清楚，请尝试以下示例，col3 和@tempVariable 的值将为 1001。
UPDATE myTable
SET col1 = 5,
col2 = @tempVariable := 100,
col2 = 1000
col3 = @tempVariable := col2 + 1;
2 Use other column in the table than the updating column.
2 使用表中更新列以外的其他列。
UPDATE myTable
SET col1 = 5,
col2 = @tempVariable := 100,
col3 = @tempVariable := col4 + 1;
@tempVariable and col3 will have the same value. They will be the col4 original value + 1.
@tempVariable 和 col3 将具有相同的值。它们将是 col4 原始值 + 1。
回答by nightsinwhiteaustin
I tested a similar query using a select and it worked for me, so I would rewrite your query as follows
我使用 select 测试了一个类似的查询，它对我有用，所以我会按如下方式重写您的查询
SET @tempVariable := 0;
UPDATE myTable SET col1 = 5, col2 = (SELECT @tempVariable + 100);
Hope that helps.
希望有帮助。
回答by jamesTheProgrammer
I have used php or coldfusion to do something like this, (php example)
我已经使用 php 或 Coldfusion 来做这样的事情，（php 示例）
function something($param){
$localVarCleaned = mysql_real_escape_string($param);
mysql_query("
UPDATE tablename
SET col = ".$localVarCleaned."
");
}
