Why do I have to assign to a session variable for it to have the right number in a query like this:
SELECT @row_number := @row_number + 1, name FROM cities;
Instead of something like:
SELECT @row_number, name FROM cities;
In the second form it returns what I'm guessing is the last row number. Maybe even the value of a COUNT(*). It's almost as if the value is somehow closed over. What is going on in these two queries?
you have @row_number variable. Everytime the below sql hits the record it shows the result and increments by one.
SELECT @row_number := @row_number + 1, name FROM cities;
if you are using mysql 8.0+, you can use row_number window function to achieve same result
select row_number() over (order by <pk>) rn, name from cities;
If we turn back to SELECT @row_number, name FROM cities;, you are not icrementing @row_number which in turns shows always same value which is assigned value for @row_number
PS: please also note that you are not using order by clause on your query which may lead to inconsistent row numbering.
You need to assign to it in order to add 1 to the value on each row. If you don't do this, you get the same value on every row, which isn't a row number. It will be whatever was left from the last time you assigned the variable, which might be the total number of rows from a previous query that was correctly incrementing.
If you're using MySQL 8.x you can replace this use of session variables with the ROW_NUMBER() function.
Instead of session variable, for MySQL version 8+ you can use ROW_NUMBER() and for below MySQL 8 you can do this
SELECT @row_number := @row_number + 1, name
FROM cities,
(SELECT @row_number:= 0) AS x;