从现有表创建新表实现数据库规范化及自动更新方案咨询

1. 自定义主键设计 #

如果你的业务是按年度分区管理数据，这种key+年份格式的主键（如1-24）是可行的，但要注意两个核心问题：

• 唯一性保障：必须确保原表的key字段在单年度内是唯一的，否则会出现重复主键（比如同一年出现两个1-24）。
• 性能优化：字符串类型的主键索引效率低于整数类型，建议拆分为id INT+year SMALLINT的联合主键，既符合业务规则，又能提升查询和索引性能。
  如果原表的key本身是全局唯一的，加年份作为主键一部分就冗余了，直接用原表唯一键作为新表主键、年份作为普通字段存储更合理。

2. 数据库规范化方向 #

通过拆分冗余字段创建新表的思路完全符合数据库规范化原则——让每个表只存储单一主题的数据，减少冗余、提升数据一致性。但要注意平衡度：

• 明确新旧表关联：如果原表保留，新表需通过外键关联原表的唯一标识；如果原表要淘汰，需确保全量数据迁移完成后再切换业务逻辑。
• 避免过度规范化：过度拆分表会增加关联查询的复杂度，拖慢查询性能，需在规范化和业务性能之间找平衡。

1. 数据库触发器（Trigger） #

这是中小型场景最常用的同步方式，针对原表的INSERT/UPDATE/DELETE操作触发同步逻辑到新表。

示例（MySQL） #

假设原表为old_table，新表为new_table，新表主键custom_id规则是原表id + '-' + 年份后两位：

-- 插入触发器：原表新增数据时同步到新表
DELIMITER //
CREATE TRIGGER sync_new_table_insert
AFTER INSERT ON old_table
FOR EACH ROW
BEGIN
    INSERT INTO new_table (custom_id, col1, col2, year)
    VALUES (CONCAT(NEW.id, '-', RIGHT(YEAR(NOW()), 2)), NEW.col1, NEW.col2, YEAR(NOW()));
END //
DELIMITER ;

-- 更新触发器：原表数据更新时同步更新新表
DELIMITER //
CREATE TRIGGER sync_new_table_update
AFTER UPDATE ON old_table
FOR EACH ROW
BEGIN
    UPDATE new_table 
    SET col1 = NEW.col1, col2 = NEW.col2
    WHERE custom_id = CONCAT(OLD.id, '-', RIGHT(YEAR(NOW()), 2));
END //
DELIMITER ;

-- 删除触发器：原表数据删除时同步删除新表数据
DELIMITER //
CREATE TRIGGER sync_new_table_delete
AFTER DELETE ON old_table
FOR EACH ROW
BEGIN
    DELETE FROM new_table 
    WHERE custom_id = CONCAT(OLD.id, '-', RIGHT(YEAR(NOW()), 2));
END //
DELIMITER ;

注意：触发器会增加原表操作的开销，高并发场景下需提前评估性能影响；同时要配置好事务规则，避免触发器执行失败导致数据不一致。

2. 变更数据捕获（CDC） #

大型系统或高并发场景优先用CDC，通过捕获数据库日志（如MySQL binlog、PostgreSQL WAL）实现同步，完全不影响原表性能：

• 无需在数据库内创建触发器，通过Debezium、MaxWell等中间件实现增量/全量同步。
• 支持跨库、跨系统的数据同步，扩展性更强。

3. 业务层同步 #

如果数据库权限受限无法用触发器或CDC，可在业务代码中实现：

• 每次操作原表时，用事务包裹原表和新表的对应操作，保证原子性。
• 缺点是业务耦合度高，需额外维护同步逻辑。

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