mysql按行顺序更新

create table t_order (
create_time timestamp not null default current_timestamp comment "订单创建时间", 
amount int not null comment "订单消费金额", 
balance int not null comment "余额"
)Copy

mysql> describe t_order;
+-------------+-----------+------+-----+-------------------+-------+
| Field       | Type      | Null | Key | Default           | Extra |
+-------------+-----------+------+-----+-------------------+-------+
| create_time | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
| amount      | int(11)   | NO   |     | NULL              |       |
| balance     | int(11)   | NO   |     | NULL              |       |
+-------------+-----------+------+-----+-------------------+-------+
3 rows in set (0.00 sec)Copy

insert t_order 
(create_time, amount, balance) 
values 
(now(), 1, 100), 
(now() + interval 1 day, 3, 0), 
(now() + interval 2 day, 5, 0),
(now() + interval 3 day, 12, 0), 
(now() + interval 4 day, 7, 0);Copy

mysql> select * from t_order;
+---------------------+--------+---------+
| create_time         | amount | balance |
+---------------------+--------+---------+
| 2017-10-12 15:40:31 |      1 |     100 |
| 2017-10-13 15:40:31 |      3 |       0 |
| 2017-10-14 15:40:31 |      5 |       0 |
| 2017-10-15 15:41:48 |     12 |       0 |
| 2017-10-16 15:41:48 |      7 |       0 |
+---------------------+--------+---------+
5 rows in set (0.00 sec)Copy

update t_order 
set 
balance = (@pre_value := @pre_value - amount) 
order by create_time, @pre_value := 101Copy

mysql> select * from t_order;
+---------------------+--------+---------+
| create_time         | amount | balance |
+---------------------+--------+---------+
| 2017-10-12 15:40:31 |      1 |     100 |
| 2017-10-13 15:40:31 |      3 |      97 |
| 2017-10-14 15:40:31 |      5 |      92 |
| 2017-10-15 15:41:48 |     12 |      80 |
| 2017-10-16 15:41:48 |      7 |      73 |
+---------------------+--------+---------+
5 rows in set (0.00 sec)Copy

本文章首发在 LearnKu.com 网站上。