MySQL：递减/递减更新一列字段值

select * from tb_student;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | Tom    |   5 |
|  2 | Jack   |   4 |
|  3 | Steve  |   3 |
|  4 | Yellow |   2 |
|  5 | Green  |   1 |
+----+--------+-----+

select version();
+-----------+
| version() |
+-----------+
| 8.0.29    |
+-----------+

CREATE TABLE `tb_student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20)  NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);


INSERT INTO `tb_student`(`id`, `name`) VALUES (1, 'Tom');
INSERT INTO `tb_student`(`id`, `name`) VALUES (2, 'Jack');
INSERT INTO `tb_student`(`id`, `name`) VALUES (3, 'Steve');
INSERT INTO `tb_student`(`id`, `name`) VALUES (4, 'Yellow');
INSERT INTO `tb_student`(`id`, `name`) VALUES (5, 'Green');

select * from tb_student;
+----+--------+
| id | name   |
+----+--------+
|  1 | Tom    |
|  2 | Jack   |
|  3 | Steve  |
|  4 | Yellow |
|  5 | Green  |
+----+--------+

ALTER TABLE `tb_student` 
ADD COLUMN `age` int(0) NOT NULL DEFAULT 0 AFTER `name`;

-- 此时的数据
select * from tb_student;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | Tom    |   0 |
|  2 | Jack   |   0 |
|  3 | Steve  |   0 |
|  4 | Yellow |   0 |
|  5 | Green  |   0 |
+----+--------+-----+

SET @rownum = 1;
SELECT @rownum := @rownum + 1 AS num;
+------+
| num  |
+------+
|    2 |
+------+

SET @rownum = 0;

UPDATE tb_student 
SET age = ( SELECT @rownum := @rownum + 1 AS num )
ORDER BY id desc;

select * from tb_student;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | Tom    |   5 |
|  2 | Jack   |   4 |
|  3 | Steve  |   3 |
|  4 | Yellow |   2 |
|  5 | Green  |   1 |
+----+--------+-----+