MySQL переменные и нумерация строк

Question

Объясните пожалуйста поведение переменных в Mysql.

Есть таблица:

mysql> select * from `table`;
+----+----------+---------+
| id | city     | user_id |
+----+----------+---------+
|  1 | Kursk    |       3 |
|  2 | Kursk    |       6 |
|  3 | Voronezh |      11 |
|  4 | Kursk    |      14 |
|  5 | Voronezh |      18 |
|  6 | Voronezh |      20 |
+----+----------+---------+

Устанавливаю значение для переменной:

SET @var = 777;

Далее запрос:

SELECT @var, @var:=table.city, @var FROM `table`;
+------+------------------+------+
| @var | @var:=table.city | @var |
+------+------------------+------+
|  777 | Kursk            |    0 |
|    0 | Kursk            |    0 |
|    0 | Voronezh         |    0 |
|    0 | Kursk            |    0 |
|    0 | Voronezh         |    0 |
|    0 | Voronezh         |    0 |
+------+------------------+------+

Почему в последней колонке нули???

Делаю этот же запрос еще раз:

SELECT @var, @var:=table.city, @var FROM `table`;
+----------+------------------+----------+
| @var     | @var:=table.city | @var     |
+----------+------------------+----------+
| Voronezh | Kursk            | Kursk    |
| Kursk    | Kursk            | Kursk    |
| Kursk    | Voronezh         | Voronezh |
| Voronezh | Kursk            | Kursk    |
| Kursk    | Voronezh         | Voronezh |
| Voronezh | Voronezh         | Voronezh |
+----------+------------------+----------+

Теперь все нормально (что я и ожидал увидеть в первый раз). Почему, чтобы получить требуемый результат, нужно выполнить запрос дважды?

Я использую переменные, чтобы нумеровать строки в группах.

Если переменной @var задать значение:

SET @var=777;

То первый запрос НЕ работает (в первом столбце просто сквозная нумерация, вместо нумерации внутри группы):

SELECT IF(@var=city, @i:=@i+1, @i:=1) AS num, @var:=city, @var, `table`.* FROM `table` ORDER BY city;
+------+------------+------+----+----------+---------+
| num  | @var:=city | @var | id | city     | user_id |
+------+------------+------+----+----------+---------+
|    1 | Kursk      |    0 |  1 | Kursk    |       3 |
|    2 | Kursk      |    0 |  2 | Kursk    |       6 |
|    3 | Kursk      |    0 |  4 | Kursk    |      14 |
|    4 | Voronezh   |    0 |  3 | Voronezh |      11 |
|    5 | Voronezh   |    0 |  5 | Voronezh |      18 |
|    6 | Voronezh   |    0 |  6 | Voronezh |      20 |
+------+------------+------+----+----------+---------+

Если запусить запрос повторно, то все ОК:

SELECT IF(@var=city, @i:=@i+1, @i:=1) AS num, @var:=city, @var, `table`.* FROM `table` ORDER BY city;
+------+------------+----------+----+----------+---------+
| num  | @var:=city | @var     | id | city     | user_id |
+------+------------+----------+----+----------+---------+
|    1 | Kursk      | Kursk    |  1 | Kursk    |       3 |
|    2 | Kursk      | Kursk    |  2 | Kursk    |       6 |
|    3 | Kursk      | Kursk    |  4 | Kursk    |      14 |
|    1 | Voronezh   | Voronezh |  3 | Voronezh |      11 |
|    2 | Voronezh   | Voronezh |  5 | Voronezh |      18 |
|    3 | Voronezh   | Voronezh |  6 | Voronezh |      20 |
+------+------------+----------+----+----------+---------+

Если использовать НЕинициализированную ранее переменную @undeclare_var, то ситуация иная:

SELECT IF(@undeclare_var=city, @i:=@i+1, @i:=1) AS num, @undeclare_var:=city, @undeclare_var, `table`.* FROM `table` ORDER BY city;
+------+----------------------+----------------+----+----------+---------+
| num  | @undeclare_var:=city | @undeclare_var | id | city     | user_id |
+------+----------------------+----------------+----+----------+---------+
|    1 | Kursk                | Kursk          |  1 | Kursk    |       3 |
|    1 | Kursk                | Kursk          |  2 | Kursk    |       6 |
|    1 | Kursk                | Kursk          |  4 | Kursk    |      14 |
|    1 | Voronezh             | Voronezh       |  3 | Voronezh |      11 |
|    1 | Voronezh             | Voronezh       |  5 | Voronezh |      18 |
|    1 | Voronezh             | Voronezh       |  6 | Voronezh |      20 |
+------+----------------------+----------------+----+----------+---------+

В первой колонке одни единицы.

Но при повторном запуске этого же запроса - все ОК.

SELECT IF(@undeclare_var=city, @i:=@i+1, @i:=1) AS num, @undeclare_var:=city, @undeclare_var, `table`.* FROM `table` ORDER BY city;
+------+----------------------+----------------+----+----------+---------+
| num  | @undeclare_var:=city | @undeclare_var | id | city     | user_id |
+------+----------------------+----------------+----+----------+---------+
|    1 | Kursk                | Kursk          |  1 | Kursk    |       3 |
|    2 | Kursk                | Kursk          |  2 | Kursk    |       6 |
|    3 | Kursk                | Kursk          |  4 | Kursk    |      14 |
|    1 | Voronezh             | Voronezh       |  3 | Voronezh |      11 |
|    2 | Voronezh             | Voronezh       |  5 | Voronezh |      18 |
|    3 | Voronezh             | Voronezh       |  6 | Voronezh |      20 |
+------+----------------------+----------------+----+----------+---------+

Подскажите пожалуйста, как мне всегда гарантированно и стабильно получить нужный (последний) результат с первого раза?

Спасибо!

Mysql v.5.7.30 (Ver 14.14 Distrib 5.7.30, for Linux (x86_64))

=============

РЕШЕНИЕ

SELECT IF(@var=city, @i:=@i+1, @i:=1) AS num, @var:=city, @var,
`table`.*
    FROM `table`, (SELECT @var:='') AS init_var
    ORDER BY city;

При инициализации переменной @var важно сразу присвоить нужный тип - тип столбца, по которому идет подсчет строк. В моем случае этот тип: строка (столбец city)

БЛОГ НА HUSL

MySQL переменные и нумерация строк

Ответы (0 шт):