What does this MySQL query mean?

Question

I'm trying to understand some queries

one of them is

(select (@a) 
 from (select(@a:=0x00)
     ,(select (@a) 
       from (information_schema.schemata)
       where (@a) in (@a:=concat(@a,schema_name,'<br>'))
       )
 ) a
)

What does this query mean?

It doesn't mean anything. Reading & assigning the same variable in the same select statement is undefined behaviour in MySQL, see the documentation re assignment & variables. [Why the order of evaluation for expressions involving user variables is undefined?](https://stackoverflow.com/a/44751302/3404097) See my comments (re an incorrect answer) at [MySQL - Define a variable within select and use it within the same select](https://stackoverflow.com/a/16715618/3404097). — philipxy, Feb 11 '22 at 03:04

score 0 · Answer 1 · answered Aug 01 '16 at 17:32

0

Not much to explain, it is a horrendous abuse of session variables. It looks like it is trying to get a concatenated list of database names, in the same manner GROUP_CONCAT would with a delimiter of <br>; but it looks very unreliable.

answered Aug 01 '16 at 17:32

Uueerdo

15,723
1
16
21

but i really need this query & need to understand it :) how it actually works – dragon Aug 01 '16 at 18:09

score 0 · Answer 2 · answered Feb 05 '22 at 17:54

Taking out many of the redundant parens that are not important, so we can strip those. Then some slight formatting for readability.

select
      @a as ThisIsTheFinalAnswer 
   from 
      ( select @a := 0x00,
              ( select @a
                   from information_schema.schemata
                   where @a in @a: = concat(@a, schema_name,'<br>')
              )
      )

So, the first part of the from (select @a := 0x00

is initializing an in-line SQL variable called @a. the @ is used to declare a variable. The := is the assignment of whatever the equation answer is on the RIGHT gets put into the variable on the LEFT. So the initialization of the variable is just to GET the variable declared vs a stored procedure where you explicitly DECLARE a variable of a given type, then set a value to it.

Then the tricky one is the where @a in...
In this case, it has to explicitly compute the right side which takes and concatenates whatever the existing @a variable, PLUS the schema_name PLUS a
(such as for HTML formatting) and ASSIGNS that back into the @a because of its :=. That is then applied to the WHERE clause of @a IN (the result of the @a := component).

When finished, the final @a is returned to the final output.

The query doesn't mean anything, see my comment on the question. — philipxy, Feb 11 '22 at 03:05

What does this MySQL query mean?

2 Answers2

Linked