SET a variable in SELECT statement – MySQL

SET a variable in SELECT statement – MySQL

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

select*from t where 1 and [email protected]=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 [email protected]=1 at line 1

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

select*from t where 1 and [email protected]=1;

into:

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

Heres how you update the variable upon each row:

create table t(id int); insert t values(1),(2),(3);
[email protected]=0;
[email protected]:=id from t;
+--------+
| @a:=id |
+--------+
|      1 |
|      2 |
|      3 |
+--------+

And you can even do concat:

[email protected]=0;
select @a:=concat(@a,,,id)from t;
+-----------------------+
| @a:=concat(@a,,,id) |
+-----------------------+
| 0,1                   |
| 0,1,2                 |
| 0,1,2,3               |
+-----------------------+

Or concat without the leading 0:

[email protected]=;
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: link

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

This has also been mentioned on Xaprb.

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

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;

SET a variable in SELECT statement – MySQL

Leave a Reply

Your email address will not be published.