在 SELECT 语句中设置一个变量 - MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15581005/
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
SET a variable in SELECT statement - MySQL
提问by Andrew Eisenberg
I'm using this code which has an error:
我正在使用这个有错误的代码：
SET @rejects = '';
SELECT *
FROM list
WHERE maker = 1
AND by_ids IN ('10','11')
AND country LIKE '%I%'
AND (
src IS NULL
|| src NOT IN (@rejects)
AND checkSrc(src) = 'yes'
AND SET @rejects = CONCAT(@rejects,',',src)
);
What's causing the issue?
是什么导致了这个问题？
回答by Pacerier
The issue is that you cannot mix selectand setin one statement, there'll surely be syntax error:
问题是您不能混合使用，select并且set在一个语句中肯定会出现语法错误：
select*from t where 1 and set@a=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set@a=1' at line 1
ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的“set@a=1”附近使用的正确语法
If you want to do setwithin select, use the colon equalssyntax. Change this:
如果您想在set内执行select，请使用冒号等于语法。改变这个：
select*from t where 1 and set@a=1;
into:
进入：
select*,@a:=1 from t where 1;
Here's how you update the variable upon eachrow:
以下是在每一行上更新变量的方法：
create table t(id int); insert t values(1),(2),(3);
set@a=0;
select@a:=id from t;
+--------+ | @a:=id | +--------+ | 1 | | 2 | | 3 | +--------+
+--------+ | @a:=id | +--------+ | 1 | | 2 | | 3 | +--------+
And you can even do concat:
你甚至可以这样做concat：
set@a='0';
select @a:=concat(@a,',',id)from t;
+-----------------------+ | @a:=concat(@a,',',id) | +-----------------------+ | 0,1 | | 0,1,2 | | 0,1,2,3 | +-----------------------+
+-----------------------+ | @a:=concat(@a,',',id) | +-----------------------+ | 0,1 | | 0,1,2 | | 0,1,2,3 | +-----------------------+
Or concatwithout the leading 0:
或者concat没有领先0：
set@a='';
select @a:=concat(@a,if(@a='','',','),id)from t;
+------------------------------------+ | @a:=concat(@a,if(@a='','',','),id) | +------------------------------------+ | 1 | | 1,2 | | 1,2,3 | +------------------------------------+
+------------------------------------+ | @a:=concat(@a,if(@a='','',','),id) | +------------------------------------+ | 1 | | 1,2 | | 1,2,3 | +------------------------------------+
However, the manual explicitlystates that this is dangerous: 
但是，手册明确指出这是危险的：
...you should neverassign a value to a user variable and read the value within the same statement...
...you might get the results you expect, but this is not guaranteed.
...the order of evaluation for expressions involving user variables is undefined.
...您永远不应该为用户变量赋值并在同一语句中读取该值...
...您可能会得到您期望的结果，但这并不能保证。
...涉及用户变量的表达式的求值顺序是 undefined。
This has also been mentioned on Xaprb.
Lastly, if you're doing quirkythings like assigning differing value types to the variable and etc, checkout the manualto be sure you understand the intricate mechanisms.
最后，如果您正在做一些古怪的事情，例如为变量分配不同的值类型等，请查看手册以确保您了解复杂的机制。
回答by Ravindra Gullapalli
Then you might write your query like this.
然后你可以像这样写你的查询。
SET @rejects = '';
SELECT @rejects = CONCAT(@rejects,',',src) FROM list WHERE maker = 1 AND by_ids IN ('10','11') AND country LIKE '%I%' AND
(src IS NULL OR src NOT IN (@rejects) AND checkSrc(src) = 'yes');
SELECT @rejects;
