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)