What are the differences in the following styles of variable initialization in MySQL?

Question

I am new to queries that include declaring variables in MySQL. I saw different styles, and I don’t quite understand what they really do. I have questions about what they actually do.

1)

set @row:=0; SELECT name, @row: =@row + 1 AS rownum FROM animal 

2)

 SELECT name, @row: =@row + 1 AS rownum FROM (SELECT @row:= 0) c, animal 

Both return the same:

  name rownum || cat || 1 || || cat || 2 || || dog || 3 || || dog || 4 || || dog || 5 || || ant || 6 || 

What are the differences in the above two queries, and which of them regarding their volume, efficiency, coding habits, use cases?

3) Now, if I do this:

 set @row:=0; SELECT name, @row: =@row + 1 AS rownum FROM (SELECT @row:= 123) c, animal 

I get

  name rownum || cat || 124 || || cat || 125 || || dog || 126 || || dog || 127 || || dog || 128 || || ant || 129 || 

Therefore, this does not mean that the internal initialization of variables overrides the external initialization and, therefore, leaves it redundant (and, therefore, is it always better to use it for initialization in SELECT ?

4) If I just do:

 SELECT name, @row: =@row + 1 AS rownum FROM animal 

I get

  name rownum || cat || NULL || || cat || NULL || || dog || NULL || || dog || NULL || || dog || NULL || || ant || NULL || 

I understand that since row not initialized. But if I run any of the other queries (can the row variable be initialized?), I see that the row variable is incremented every time I run the above query. That is, this gives me the result the first time I run:

  name rownum || cat || 1 || || cat || 2 || || dog || 3 || || dog || 4 || || dog || 5 || || ant || 6 || 

and then when you restart it gives in

  name rownum || cat || 7 || || cat || 8 || || dog || 9 || || dog || 10 || || dog || 11 || || ant || 12 || 

So row is stored somewhere? And what is its scope and life expectancy?

5) If I have a request like this:

 SELECT (CASE WHEN @name <> name THEN @row:=1 ELSE @row: =@row + 1 END) AS rownum, @name:=name AS name FROM animal 

This always gives the correct result:

 rownum name || 1 || cat || || 2 || cat || || 1 || dog || || 2 || dog || || 3 || dog || || 1 || ant || 

So this does not mean that it is not always necessary to initialize a variable at the top or in SELECT depending on the query?

Answer 1

Be sure to read the user variable section of the user guide .

What are the differences in the above two queries, and which of them regarding their volume, efficiency, coding habits, use cases?

Request 1) uses several operators. Therefore, it can rely on the execution order of these operators, ensuring that the variable is set before it is expanded.
Query 2), on the other hand, performs initialization in a nested subquery. This turns all of this into a single request. You do not risk forgetting the initialization. But this code is more dependent on the internal operation of the mysql server, especially on the fact that it will execute a subquery before it starts to calculate the results for an external query.

Therefore, this does not mean that the internal initialization of the variables overrides the external initialization and, therefore, leaves the last redundant (and, therefore, is it always better to start the initialization in SELECT ?

This is not about internal and external, but about sequential order: the subquery is executed after SET , so it just overwrites the old value.

So, is the string stored somewhere? And what is its scope and life expectancy?

User variables are local to connect to the server. Setting up any other process will not be affected. Even the same process can support multiple connections with independent user variable settings. When the connection is closed, all configuration variables are lost.

So this does not mean that it is not always necessary to initialize a variable at the top or in SELECT depending on the query?

Quote from the manual :

If you are referring to a variable that has not been initialized, it is NULL and the type of the string.

That way, you can use the variable before it is initialized, but you must be careful that you can efficiently handle the resulting NULL value in a reasonable way. Please note, however, that your request 5) suffers from another problem explicitly outlined in the manual:

Generally, you should never assign a value to a user variable and read the value within the same statement. You can get the expected results, but this is not guaranteed. The evaluation order of expressions with user variables is undefined and can be changed based on the elements contained in this statement; In addition, this order is not guaranteed to be the same between versions of MySQL Server. In SELECT @a, @a: =@a +1, ... you might think that MySQL will evaluate @a first and then do the second lesson. However, changing the statement (for example, by adding a GROUP BY , HAVING or ORDER BY ) may cause MySQL to choose a execution plan with a different evaluation order.

So, in your case, the @name:=name can be executed before checking @name <> name , as a result of which all your rownum values ​​will be the same. Therefore, even if it really works, there is no guarantee that it will work in the future.

Please note that I was very skeptical about using custom variables this way. I have already quoted the above warning from the manual in the comments for several answers. I also asked questions like Warranty when using custom variables for line numbers . Other users are more pragmatic and therefore more willing to use code that seems to work without explicit guarantees that something will continue to work as intended.