mysql按行顺序更新

场景

一张表，记录了交易时间、消费和余额。
创建表

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

查看表结构：

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)

插入5行数据：

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);

现在查看表数据：

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)

balance字段表示余额，现在看来需要修复balance列。
要求每一行的balance等于上一行的balance减去当前行的amount。
目前第一行数据正确。

顺序更新

为了满足按行顺序更新，并且每次更新，需要依赖上一次更新的结果，也就是上一行的balance，那就需要定义一个变量记录上一次跟新结果。
首先，按照创建时间顺序更新，

order by create_time

定义变量

@pre_value := 101

拼接起来完整的sql：

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

执行结果：

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)