mysql - 在 SELECT 语句中设置变量 - MySQL

Question

我正在使用这个有错误的代码:

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)
    );

是什么导致了这个问题？

Answer 1

问题是你不能在一条语句中混用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}

如果你想在select中做set，使用the colon equals句法。改变这个:

select*from t where 1 and set@a=1;

进入:

select*,@a:=1 from t where 1;

以下是在每一行上更新变量的方法:

create table t(id int); insert t values(1),(2),(3);
set@a=0;
select@a:=id from t;

+--------+
| @a:=id |
+--------+
|      1 |
|      2 |
|      3 |
+--------+

你甚至可以做concat:

set@a='0';
select @a:=concat(@a,',',id)from t;

+-----------------------+
| @a:=concat(@a,',',id) |
+-----------------------+
| 0,1                   |
| 0,1,2                 |
| 0,1,2,3               |
+-----------------------+

或者没有前导0的concat:

set@a='';
select @a:=concat(@a,if(@a='','',','),id)from t;

+------------------------------------+
| @a:=concat(@a,if(@a='','',','),id) |
+------------------------------------+
| 1                                  |
| 1,2                                |
| 1,2,3                              |
+------------------------------------+

但是，手册明确指出这是危险的:

...you should never assign 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.

这个也有提到on Xaprb .

最后，如果您正在做一些古怪的事情，比如为变量分配不同的值类型等等，checkout the manual确保您了解复杂的机制。