在SELECT语句中设置变量-MySQL [英] SET a variable in SELECT statement - MySQL

问题描述

我正在使用此代码，该代码有错误:

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?

推荐答案

问题是您不能在一个语句中混用select和set，肯定会出现语法错误:

The issue is that you cannot mix select and set in one statement, there'll surely be syntax error:

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

^{错误1064(42000):您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第1行的'set @ a = 1'附近使用}

如果要在select中执行set，请使用冒号等于语法.更改此:

If you want to do set within select, use the colon equals syntax. Change this:

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

进入:

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

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

Here's how you update the variable upon each row:

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               |
+-----------------------+

或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                              |
+------------------------------------+

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

However, the manual explicitly states that this is dangerous:

...您应该从不为用户变量分配值，并阅读 同一条语句中的值...

...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.

这也被提到 .

最后，如果您要执行古怪之类的事情，例如为变量分配不同的值类型等，请

Lastly, if you're doing quirky things like assigning differing value types to the variable and etc, checkout the manual to be sure you understand the intricate mechanisms.

这篇关于在SELECT语句中设置变量-MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！