mysql 关于用户账目明细设计的问题

Question

本期余额=上次余额+收入-支出

怎么能让明细的表现变成这种的

时间 ----------- 交易类型 ---- 收入/支出 ---- 可用余额

2018-03-13 ---- 购买 ---------- -100 ---------- 270
2018-03-12 ---- 出售 ------------ 50 ----------- 370
2018-03-11 ---- 充值 ------------ 50 ----------- 320
2018-03-10 ---- 购买 ----------- -30 ----------- 270
2018-03-09 ---- 充值 ----------- 300 ---------- 300

流水表结构是这样的。

用户id --- 时间 ----------- 交易类型 ------ 操作类型 ----- 变动金额
----1 -----2018-03-09 ------ 充值 ------------ 收入---------- 300
----1 -----2018-03-10 ------ 购买 ------------ 支出---------- 30
----1 -----2019-03-11 ------ 充值 ------------ 收入---------- 50
----1 -----2019-03-12 ------ 出售 ------------ 支出---------- 50
----1 -----2019-03-13 ------ 购买 ------------ 支出---------- 100

不用单独记录这种余额字段，可用余额就是统计出来的。

有经验的朋友指点一下好吗？现在就是不知道怎么统计出这种效果。

Answer 1

我觉得还是需要在记录每一笔交易记录的同时，把当前余额也记录下来会比较好。

下面来说你这种情况：

计算公式就是：

某条交易记录余额 = 当前余额 - (某条交易记录时间点 ~ 现在时间点之间的充值金额总和） + （某条交易记录时间点 ~ 当前时间点之间的消费记录总和）

以上只是操作类型为简单的增加和减少的时候。

这种方式，在时时统计的时候，效率还是太慢了。建议还是需要加一个交易余额的字段。

---

书接上文，大兄弟你也太纠结了...

其实你给出的两种情况，自己取舍一下就知道哪种快速、直接了。

你有消费(充值或者支出)的时候，计算完当前余额，拿着你的这个余额，更新你的用户表余额。

然后再用你的这个余额，直接放在消费记录里插入就好了。
维护起来比起你时时统计的要简单多了。

Answer 2

1. 数据库里流水表记录金额变动的时候就记录好当时的余额。既能实现你这个功能，也方便审计
2. 不动数据库的话，先查出所有的明细，然后根据类型自己计算出来每一笔对应的余额。

其他的想不到了，或者可以两个结合，按日期分段来记录余额，查询的时候只要算少量的流水就能算出来，感觉没什么必要

Answer 3

我还在另外一个表里面有个 总余额字段，这里明细这里再记录个余额字段，感觉有点别扭。

你们都怎么做的呢？

比如我更新另外一个余额表字段，这个明细里面的余额也要更新一次吗？

用户交易明细表

用户id	时间	交易类型	操作类型	变动金额	当前余额
1	2018-03-09	充值	收入	300	300
1	2018-03-10	购买	支出	30	270
1	2019-03-11	充值	收入	50	330
1	2019-03-12	出售	收入	50	380
1	2019-03-13	转账	支出	200	180

我还有另外一个表是记录余额的，与用户基础信息表是1对1关系。

用户余额表

用户id	余额
1	180
2	200
3	100

如果按照我这种设计有问题吗？ 两个表都有余额。

比如我现在要做一个操作，用户id=1 转账-100，给用户id=2。

那我要先去 用户余额表，进行给用户id=1，扣除100，更新成新的余额。

然后，我要 去交易明细表 插入一个交易记录，id=1，转账类型，支出，100，

再计算一下，当前余额。

现在计算有两种办法，

一种是用 用户余额表里面的id=1的余额，减去 100，更新给 交易明细表 的当前余额字段。
另一种是，去交易明细表 找上一条交易明细的当前余额值，再扣除 100 ，更新给这个新的交易明细。

问题来了？我该怎么做呢？用哪种方式更新，这个新交易明细里的 当前余额。

同时我还要操作id=2的用户，他要增加 100，上面的操作，还要再来一遍。

感觉好复杂，感觉控制不好，就要出错。

按照我这种设计，有毛病吗？你们都怎么做的呢？

这要维护 两个表的余额字段，不能出错。好处是查询快？不用复杂的sql语句统计？

如果不加这个当前余额，用sql语句自动算出来返回给php结果集，这种效率低？

怎么取舍啊？