1
SELECT (@row:=@row+1) AS ROW, ID  
FROM table1 ,(SELECT @row := 0) r   
order by ID desc

How does SQl evaluate this? Strangely, there seems to be little to no literature on this sort of this with variables in the SELECT statement.

  1. How can SELECT select row = 0? I thought select had to select an entire column
  2. what is the r here?
  3. How does the SELECT @row:=@row+1 know how to iterate through the rows?
  4. Its not clear to me how the ID is set here. The @row surely correspond somehow to the ROW, but ID seems to be just hanging on
  5. Why does the FROM need to take from more than just the table 1?
eshirvana
  • 23,227
  • 3
  • 22
  • 38
Trajan
  • 1,380
  • 6
  • 20
  • 41
  • example taken from here https://stackoverflow.com/questions/12707646/need-a-sequence-number-for-every-row-in-mysql-query – Trajan Apr 17 '21 at 17:45
  • MySQL has lots of weird and funny (and downright awful) extensions to ISO SQL. Support for incrementing a variable inside a `SELECT` statement is one of them. Fortunately you don't need to use this terrible technique anymore because now MySQL supports the ISO SQL `ROW_NUMBER()` function now: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number – Dai Apr 17 '21 at 17:47

2 Answers2

2

There is very important "literature" on this:

It is also possible to assign a value to a user variable in statements other than SET. (This functionality is deprecated in MySQL 8.0 and subject to removal in a subsequent release.)

In other words, this code is going to stop working at some point in MySQL.

The correct way to do what you want is to use row_number():

SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS ROW, ID  
FROM table1 
ORDER BY ID DESC;

The only reason to learn about variable assignment in MySQL SELECT statements is historical curiosity -- or if you are stuck maintaining a system that has not yet been migrated to MySQL 8+.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1
  • (SELECT @row := 0) r , is translated as a table with 1 column (column with no name) and you are giving the table an alias name "r"
  • you are doing join between table1 and r table, so if you execute :
SELECT  *  
FROM table1 ,(SELECT @row := 0) r   
order by ID desc

you will see all the columns from table1 plus a new column ( with no name) from table which is always 0

  • however in your query you are showing a variable winch each time for each is calculated , but also each time it executes you assign a new value to your variable which is itself + 1 @row:=@row+1
  • you are sorting it by Id , so how you are showing the final result , if you change your order by ,it will find out the difference
eshirvana
  • 23,227
  • 3
  • 22
  • 38