重构单表内一对一双向与一对多关系及账户子账户一致性技术问询

Hey，针对你提到的账户与子账户层级关系的重构需求，我来一步步帮你搞定这三个核心问题，同时保证单表设计下的关系合理性：

首先，我们设计一张account_hierarchy表，同时支持主账户与主子账户的一对一双向关联，以及子账户的层级嵌套一对多关系：

CREATE TABLE account_hierarchy (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_name VARCHAR(100) NOT NULL, -- 示例业务字段，可替换为你的实际字段
    account_type ENUM('MASTER', 'SUB') NOT NULL, -- 明确区分主账户/子账户
    parent_id INT NULL, -- 父节点ID：主账户为NULL，子账户指向父级（主账户或其他子账户）
    is_master_subaccount BOOLEAN DEFAULT FALSE, -- 标记是否为主账户对应的默认子账户
    master_account_id INT NOT NULL, -- 双向关联字段：主账户指向自身，子账户指向所属主账户
    -- 外键约束保证关联合法性
    FOREIGN KEY (parent_id) REFERENCES account_hierarchy(id) ON DELETE CASCADE,
    FOREIGN KEY (master_account_id) REFERENCES account_hierarchy(id) ON DELETE CASCADE,
    -- 唯一性约束：每个主账户只能有一个默认主子账户
    UNIQUE KEY uk_master_subaccount (master_account_id, is_master_subaccount)
);

问题1：数据库无法保证数据一致性（可插入无对应子账户的账户） #

解决方案：约束+触发器强制主账户与主子账户绑定 #

1. 主账户基础规则校验：
   主账户的parent_id必须为NULL，且master_account_id必须等于自身ID。如果你的数据库支持CHECK约束（如PostgreSQL）可直接加，MySQL则用触发器实现：

   DELIMITER //
   CREATE TRIGGER trg_validate_master_account
   BEFORE INSERT ON account_hierarchy
   FOR EACH ROW
   BEGIN
       IF NEW.account_type = 'MASTER' THEN
           IF NEW.parent_id IS NOT NULL OR NEW.master_account_id != NEW.id THEN
               SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '主账户的parent_id必须为NULL，且master_account_id必须等于自身ID';
           END IF;
       END IF;
   END //
   DELIMITER ;
   

2. 自动创建主子账户：
   用触发器确保插入主账户时，自动生成对应的主子账户，彻底避免无对应子账户的主账户存在：

   DELIMITER //
   CREATE TRIGGER trg_create_master_subaccount
   AFTER INSERT ON account_hierarchy
   FOR EACH ROW
   BEGIN
       IF NEW.account_type = 'MASTER' THEN
           INSERT INTO account_hierarchy (account_name, account_type, parent_id, is_master_subaccount, master_account_id)
           VALUES (CONCAT(NEW.account_name, ' - 主子账户'), 'SUB', NEW.id, TRUE, NEW.id);
       END IF;
   END //
   DELIMITER ;
   

问题2：关联查询返回多行冗余数据（子账户有下级时） #

解决方案：精准筛选主子账户，避免层级穿透 #

当你只需要主账户与它的主子账户数据时，直接通过is_master_subaccount = TRUE筛选，不会带出下级子账户：

-- 查询主账户及其对应的主子账户，无冗余数据
SELECT 
    m.id AS master_id, m.account_name AS master_name,
    s.id AS master_sub_id, s.account_name AS master_sub_name
FROM account_hierarchy m
JOIN account_hierarchy s 
    ON m.id = s.master_account_id AND s.is_master_subaccount = TRUE
WHERE m.account_type = 'MASTER';

如果需要查询主账户的完整子账户层级，可用递归CTE（支持的数据库如MySQL 8+、PostgreSQL）：

-- 递归查询主账户的所有子账户层级
WITH RECURSIVE account_tree AS (
    SELECT id, account_name, parent_id, master_account_id, 1 AS level
    FROM account_hierarchy
    WHERE account_type = 'MASTER' AND id = 1 -- 替换为目标主账户ID
    UNION ALL
    SELECT ah.id, ah.account_name, ah.parent_id, ah.master_account_id, at.level + 1
    FROM account_hierarchy ah
    JOIN account_tree at ON ah.parent_id = at.id
)
SELECT * FROM account_tree;

问题3：缺乏明确的主子账户识别方式 #

解决方案：用is_master_subaccount字段+唯一性约束明确标记 #

我们在表中添加的is_master_subaccount布尔字段，搭配UNIQUE KEY uk_master_subaccount (master_account_id, is_master_subaccount)约束，确保每个主账户只能有一个**is_master_subaccount = TRUE**的子账户——这就是最明确的主子账户标识。

你可以通过这个字段快速定位主账户的默认子账户，也可在业务逻辑中限制只有主子账户才能创建下级子账户（如果需要）。

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