如何基于两个表的主键生成编码主键并实现关联CRUD操作

一、编码主键的设计（仿JWT思路） #

参考JWT的三段式结构简化实现，核心是把Table1和Table2的主键做编码组合：

• 用不会出现在主键中的固定分隔符（比如|）拼接两个主键，例如把Table1主键t1_001和Table2主键t2_123拼成t1_001|t2_123
• 对拼接后的字符串做URL安全的Base64编码，得到Table3的主键，例如dDFfMDAxfHRfMTIz
• 解码时先做Base64反解码，再按分隔符拆分就能还原出两个原始主键

二、解码与关联查询实现 #

1. 解码逻辑（以Java为例） #

// 解码复合主键，拆分出Table1和Table2的主键
public String[] decodeCompositeKey(String compositeKey) {
    byte[] decodedBytes = Base64.getUrlDecoder().decode(compositeKey);
    String originalStr = new String(decodedBytes);
    return originalStr.split("\\|"); // 注意转义分隔符
}

2. 关联查询SQL #

假设Table1主键为t1_id，Table2主键为t2_id，Table3主键为t3_id：

• 应用层解码后关联查询：

SELECT t1.*, t2.*, t3.*
FROM table1 t1
JOIN table2 t2 ON t1.t1_id = ?
JOIN table3 t3 ON t3.t3_id = ?
WHERE t1.t1_id = ? AND t2.t2_id = ?

• 数据库端直接解码关联（以MySQL为例）：

SELECT t1.*, t2.*, t3.*
FROM table3 t3
JOIN table1 t1 ON t1.t1_id = SUBSTRING_INDEX(FROM_BASE64(t3.t3_id), '|', 1)
JOIN table2 t2 ON t2.t2_id = SUBSTRING_INDEX(FROM_BASE64(t3.t3_id), '|', -1)

三、基础CRUD操作实现 #

1. 创建（Create） #

• 先获取Table1和Table2的主键，按规则生成复合编码主键
• 插入Table3记录：

INSERT INTO table3 (t3_id, column1, column2) VALUES (?, ?, ?)

2. 查询（Read） #

• 直接通过复合主键查询Table3，再解码关联另外两张表
• 或者通过Table1/Table2的主键，生成对应复合主键后查询，也可以用数据库端解码的SQL批量查询

3. 更新（Update） #

• 定位到目标复合主键后直接更新：

UPDATE table3 SET column1 = ? WHERE t3_id = ?

4. 删除（Delete） #

• 通过复合主键直接删除：

DELETE FROM table3 WHERE t3_id = ?

• 或者通过关联条件删除：

DELETE t3 FROM table3 t3
JOIN table1 t1 ON t1.t1_id = SUBSTRING_INDEX(FROM_BASE64(t3.t3_id), '|', 1)
WHERE t1.t1_id = ?

四、注意事项 #

• 分隔符必须避开原始主键中可能出现的字符，防止拆分出错
• 如果需要防篡改，可以给复合主键加上签名段（类似JWT的Signature），解码时先验证签名再拆分
• 若频繁通过Table1/Table2主键查询Table3，建议在Table3中额外存储t1_id和t2_id字段并创建联合索引，避免每次查询都解码，提升效率

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