【问题记录】【数据库】库存或者账户流水记录修复

1  前言

大家的系统有没有关于客户资金、会员卡余额、库存记录等，这些相关信息的存储，说白了就是流水记录表。不知道大家是如何存储的，我们的存储一条记录最起码的是变动数量、变动前数量、变动后数量，这个变动前、变动后就粘的比较紧，那么当系统出现问题的时候，可能中间差一条变动，那么后边的流水记录都要变动，那么如何修复呢？本节来记录一下。

2  数据准备

表结构：

CREATE TABLE "public"."my_account_change_info" (
  "id" int8 NOT NULL,
  "account_code" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "change_type" int2 NOT NULL,
  "change_price" numeric(16,2) NOT NULL,
  "before_price" numeric(16,2) NOT NULL,
  "after_price" numeric(16,2) NOT NULL,
  "create_time" timestamp(6),
  "modify_time" timestamp(6),
  CONSTRAINT "pk_my_account_change_info" PRIMARY KEY ("id")
)
;

ALTER TABLE "public"."my_account_change_info" 
  OWNER TO "postgres";

COMMENT ON COLUMN "public"."my_account_change_info"."id" IS '主键';

COMMENT ON COLUMN "public"."my_account_change_info"."account_code" IS '账户编码';

COMMENT ON COLUMN "public"."my_account_change_info"."change_type" IS '资金类型 10-账户资金 20-信用额度';

COMMENT ON COLUMN "public"."my_account_change_info"."change_price" IS '变更金额';

COMMENT ON COLUMN "public"."my_account_change_info"."before_price" IS '变更前金额';

COMMENT ON COLUMN "public"."my_account_change_info"."after_price" IS '变更后金额';

COMMENT ON COLUMN "public"."my_account_change_info"."create_time" IS '创建时间';

COMMENT ON COLUMN "public"."my_account_change_info"."modify_time" IS '更新时间';

COMMENT ON TABLE "public"."my_account_change_info" IS '账户金额变动记录表';

数据：

INSERT INTO "public"."my_account_change_info" ("id", "account_code", "change_type", "change_price", "before_price", "after_price", "create_time", "modify_time") VALUES (1, '001', 10, '10.00', '10.00', '20.00', '2024-09-03 19:36:43', '2024-09-03 19:36:46');
INSERT INTO "public"."my_account_change_info" ("id", "account_code", "change_type", "change_price", "before_price", "after_price", "create_time", "modify_time") VALUES (6, '001', 10, '2.00', '20.00', '22.00', '2024-09-03 19:36:43', '2024-09-03 19:36:46');
INSERT INTO "public"."my_account_change_info" ("id", "account_code", "change_type", "change_price", "before_price", "after_price", "create_time", "modify_time") VALUES (13, '001', 10, '7.00', '22.00', '29.00', '2024-09-03 19:36:43', '2024-09-03 19:36:46');
INSERT INTO "public"."my_account_change_info" ("id", "account_code", "change_type", "change_price", "before_price", "after_price", "create_time", "modify_time") VALUES (24, '001', 10, '15.00', '29.00', '44.00', '2024-09-03 19:36:43', '2024-09-03 19:36:46');
INSERT INTO "public"."my_account_change_info" ("id", "account_code", "change_type", "change_price", "before_price", "after_price", "create_time", "modify_time") VALUES (35, '001', 10, '-2.00', '44.00', '42.00', '2024-09-03 19:36:43', '2024-09-03 19:36:46');
INSERT INTO "public"."my_account_change_info" ("id", "account_code", "change_type", "change_price", "before_price", "after_price", "create_time", "modify_time") VALUES (40, '001', 10, '-9.00', '42.00', '33.00', '2024-09-03 19:36:43', '2024-09-03 19:36:46');
INSERT INTO "public"."my_account_change_info" ("id", "account_code", "change_type", "change_price", "before_price", "after_price", "create_time", "modify_time") VALUES (44, '001', 10, '22.00', '33.00', '55.00', '2024-09-03 19:36:43', '2024-09-03 19:36:46');

数据准备如下：

3  解决方法

从程序的角度看的话，肯定能实现。比如在 id = 13 的后边，加入一条数据，那么 id = 13 后边的数据都要修复。

首先你的数据肯定要有流水性质（也就是先后） 这里的流水性质可以比如主键的自增或者主键的大小或者创建时间或者某个流水号，也就是要能知道记录的先后顺序。比如我这里的表 id 来体现的，id = 13 的前一条变动，肯定是小于 13 里边的最大的 id 也就是 6，后一条变动是大于 13 的里边最小的一条即 24。

那么从程序的角度，首先判断一下要变动的范围 id > 13，即24、35、40、44。那么第一条变动的 before_price 又要取决于上一条的 after_price。

所以每次处理都要 1个起始记录 + 变动记录，即使数据量很大，我们也可以采用分页的方式，每次处理该页的数据 + 该页的前一条数据即（该页起始记录）。 

每页的处理逻辑就是先按照流水号进行升序排序，然后第一个记录的 before_price 取该页起始记录的 after_price，并重新算出第一个记录的 after，然后剩下的记录就是 before = 前一个的 after，重新计算出该记录的 after 即可，最后保存该页的变动。

我想的是能不能直接通过 SQL 来修复呢？我们本节就来试试，看看能不能行：

第一步：我们先往 id = 13 的后边加入一条数据：

第二步：首先给变动范围的数据进行编号（为什么要编号 一是因为要可能你的是流水号那种带前缀编码什么的没有规律，二是因为编好号这种1 2 3 4等等这种方便数据处理）

SELECT
 t1.id,
 t1.account_code,
 t1.change_type,
 t1.change_price,
 t1.before_price,
 t1.after_price,
 t1.create_time,
 t1.modify_time,
 ROW_NUMBER() OVER (ORDER BY id) AS rownum
FROM my_account_change_info t1
WHERE t1.account_code = '001' and t1.change_type = 10 and id >= 12

那么我们变动范围的数据就查询出来了：

第二步：进行初次拼接

with base_info as (
SELECT
 t1.id,
 t1.account_code,
 t1.change_type,
 t1.change_price,
 t1.before_price,
 t1.after_price,
 t1.create_time,
 t1.modify_time,
 ROW_NUMBER() OVER (ORDER BY id) AS rownum
FROM my_account_change_info t1
WHERE t1.account_code = '001' and t1.change_type = 10 and id >= 12
)
select
t1.id,
t1.change_price,
t1.before_price,
t1.after_price,
t1.rownum,
t2.change_price,
t2.before_price,
t2.after_price,
t2.rownum,
t2.after_price 新的before,
t2.after_price + t1.change_price 新的after
from 
base_info t1
left join base_info t2 on t1.rownum = t2.rownum + 1
order by t1.id

看来这么写不行，思路有点问题，应该直取，即然我都知道差值了，那么后边的记录岂不是 before、after 都加上那条新插入的 change 是不是就可以了，还编什么号啊，再看看我新写的这个：

with base_info as (
SELECT
 t1.id,
 t1.account_code,
 t1.change_type,
 t1.change_price,
 t1.before_price,
 t1.after_price,
 t1.create_time,
 t1.modify_time,
 -- 新插入记录的变动数量
 1 change_insert 
FROM my_account_change_info t1
WHERE t1.account_code = '001' and t1.change_type = 10 and id > 12
)
select
t1.id,
t1.change_price,
t1.before_price,
t1.after_price,
-- 该记录的 before after 都加上新插入的那条变动数量
t1.before_price + t1.change_insert 新的before,
t1.after_price + t1.change_insert 新的after
from 
base_info t1
order by t1.id

是不是这样就行了：

那么都查出来了。更新的语句就简单了，比如直接拼出来更新的语句来：

with base_info as (
SELECT
 t1.id,
 t1.account_code,
 t1.change_type,
 t1.change_price,
 t1.before_price,
 t1.after_price,
 t1.create_time,
 t1.modify_time,
 -- 新插入记录的变动数量
 1 change_insert 
FROM my_account_change_info t1
WHERE t1.account_code = '001' and t1.change_type = 10 and id > 12
)
select
t1.id,
t1.change_price,
t1.before_price,
t1.after_price,
-- 该记录的 before after 都加上新插入的那条变动数量
t1.before_price + t1.change_insert 新的before,
t1.after_price + t1.change_insert 新的after,
concat('update my_account_change_info set before_price=', t1.before_price + t1.change_insert, ', after_price=', t1.after_price + t1.change_insert, ' where id = ', t1.id, ';') sql
from 
base_info t1
order by t1.id

如果数据量大的话，我们可以直接创个临时表，把查出来的数据都塞进临时表，然后根据 id 进行连表更新是不是也可以，太大的话再加个范围条件多执行几次。

那么更改完后的数据如何做验证呢？我们也小写一下：

with base_info as (
SELECT
 t1.id,
 t1.account_code,
 t1.change_type,
 t1.change_price,
 t1.before_price,
 t1.after_price,
 t1.create_time,
 t1.modify_time,
 ROW_NUMBER() OVER (ORDER BY id) AS rownum
FROM my_account_change_info t1
)
select
t1.id,
t1.change_price,
t1.before_price,
t1.after_price,
t1.rownum,
-- 第一条没有 before 没有的就默认取自己的
COALESCE(t2.after_price, t1.before_price) 应该before,
-- 最后一条没有 after 没有的就默认取自己的
COALESCE(t3.before_price, t1.after_price) 应该after,
t2.rownum,
t3.rownum
from 
base_info t1
left join base_info t2 on t1.rownum - 1= t2.rownum
left join base_info t3 on t1.rownum + 1= t3.rownum
order by t1.id

4  小结

好啦，本节就记录到这里哈，是我想复杂了，直接每个记录给 before、after 加上变动数量即可，有更好的方法的话，欢迎指点哈。