如何同步修改两张关联表中对应TYPE的Num序列为指定起始值

问题描述 #

我有两张包含多行数据的表，结构如下：

Table A
(TYPE - Num)
DA - 1
DA - 2
DA - 3
DA - 4
DA - 5
DB - 1
DB - 2
.
.
.

Table B
(TYPE - Num - NumLine)
DA - 1 - 1
DA - 1 - 2
DA - 1 - 3
DA - 1 - 4
DA - 2 - 1
DA - 2 - 2
DA - 2 - 3
DA - 2 - 4
DA - 3 - 1
DA - 3 - 2
DA - 4 - 1
DA - 5 - 1
DB - 1 - 1
DB - 1 - 2
DB - 1 - 3
DB - 2 - 1
.
.
.

我需要将不同TYPE对应的Num字段设置为不同的起始序列，例如DA类型的Num从12开始递增，DB类型的Num从20开始递增，该修改需要同步应用到两张表的对应行上，修改后的预期效果如下：

Table A
(TYPE - Num)
DA - 12
DA - 13
DA - 14
DA - 15
DA - 16
DB - 20
DB - 21
.
.
.

Table B
(TYPE - Num - NumLine)
DA - 12 - 1
DA - 12 - 2
DA - 12 - 3
DA - 12 - 4
DA - 13 - 1
DA - 13 - 2
DA - 13 - 3
DA - 13 - 4
DA - 14 - 1
DA - 14 - 2
DA - 15 - 1
DA - 16 - 1
DB - 20 - 1
DB - 20 - 2
DB - 20 - 3
DB - 21 - 1
.
.
.

请问该如何实现该需求？需要支持大量数据行的修改场景。

解决方案 #

核心思路是先生成「旧TYPE+旧Num」到「新Num」的全局唯一映射，再基于映射批量更新两个表，既保证两张表的数据一致性，也适配大数据量的更新场景。

场景1：关系型数据库（MySQL/PostgreSQL等） #

步骤1：创建中间映射表

先创建临时映射表存储新旧Num的对应关系，避免重复计算，也方便校验结果：

CREATE TEMPORARY TABLE num_mapping (
    type VARCHAR(32) NOT NULL,
    old_num INT NOT NULL,
    new_num INT NOT NULL,
    PRIMARY KEY (type, old_num)
);

步骤2：生成映射数据

按TYPE配置的起始值，基于旧Num的排序生成对应的新Num：

INSERT INTO num_mapping (type, old_num, new_num)
SELECT
    type,
    old_num,
    CASE type
        WHEN 'DA' THEN 11 + ROW_NUMBER() OVER(PARTITION BY type ORDER BY old_num ASC)
        WHEN 'DB' THEN 19 + ROW_NUMBER() OVER(PARTITION BY type ORDER BY old_num ASC)
        -- 新增其他TYPE规则直接加WHEN分支即可
        ELSE old_num
    END AS new_num
FROM (
    SELECT DISTINCT TYPE AS type, Num AS old_num FROM TableA
) t;

执行后可先查询映射表确认数值规则符合预期，再进行后续更新。

步骤3：批量更新两张表

大表更新建议关闭自动提交，分批提交（比如每次更新1000条），避免长事务锁表影响业务。
更新TableA：

UPDATE TableA a
JOIN num_mapping m ON a.TYPE = m.type AND a.Num = m.old_num
SET a.Num = m.new_num;

更新TableB：

UPDATE TableB b
JOIN num_mapping m ON b.TYPE = m.type AND b.Num = m.old_num
SET b.Num = m.new_num;

大数据量优化建议

• 提前给TableA、TableB的TYPE和Num字段建联合索引，大幅提升关联更新速度
• 更新前先备份全量数据，避免操作失误无法回滚
• 百万级以上数据建议按主键范围分片更新，避免单次更新数据量过大

场景2：离线文件处理（CSV/Excel等） #

如果是本地离线文件，可以用Python批量处理，示例代码如下：

import pandas as pd

# 配置不同TYPE的起始值
START_RULE = {
    "DA": 12,
    "DB": 20
}

# 读取源数据
df_a = pd.read_csv("table_a.csv")
df_b = pd.read_csv("table_b.csv")

# 生成全局映射字典
num_map = {}
for typ, group in df_a.groupby("TYPE"):
    sorted_old_nums = sorted(group["Num"].unique())
    for idx, old_num in enumerate(sorted_old_nums):
        num_map[(typ, old_num)] = START_RULE[typ] + idx

# 应用映射到两个表
df_a["Num"] = df_a.apply(lambda row: num_map[(row["TYPE"], row["Num"])], axis=1)
df_b["Num"] = df_b.apply(lambda row: num_map[(row["TYPE"], row["Num"])], axis=1)

# 导出更新后的数据
df_a.to_csv("table_a_updated.csv", index=False)
df_b.to_csv("table_b_updated.csv", index=False)

内容的提问来源于stack exchange，提问作者Alexander