SQL中如何存储双人对战记录？删除用户后记录仍可查询

针对你遇到的问题，这里提供几个比当前方案更合理的解决思路：

方案一：用户表软删除 #

不直接删除用户记录，而是在users表中添加标记字段标记用户状态，游戏记录的外键关联依然有效，无需重复存储用户名，规则变更时只需修改users表即可。

操作步骤： #

1. 给用户表添加软删除标记字段：

ALTER TABLE users ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE;

2. 用户删除账号时，仅更新标记字段而非删除记录：

UPDATE users SET is_deleted = TRUE WHERE id = ?;

3. 查询对战记录时，通过关联用户表获取用户名（无论用户是否已删除）：

SELECT 
    gr.id,
    u1.name AS player_1_name,
    u2.name AS player_2_name,
    gr.player_1_score,
    gr.player_2_score,
    gr.created_at
FROM game_records gr
LEFT JOIN users u1 ON gr.player_1_id = u1.id
LEFT JOIN users u2 ON gr.player_2_id = u2.id;

若需要区分活跃用户和已删除用户，可在查询条件中加入u1.is_deleted判断。

优点： #

• 无需修改现有game_records表结构，改动最小
• 用户名仅存储在users表中，规则变更只需维护一处
• 查询逻辑简单，无需额外关联其他表

缺点： #

• 用户表会保留已删除用户的数据，若用户量极大可能占用额外存储空间

方案二：创建已删除用户归档表 #

将删除的用户数据迁移到独立的归档表中，既保证活跃用户表的整洁，又能通过关联归档表获取已删除用户的对战记录信息。

操作步骤： #

1. 创建已删除用户归档表：

CREATE TABLE deleted_users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(24),
    deleted_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

2. 用户删除账号时，先将数据迁移到归档表，再删除原用户记录：

INSERT INTO deleted_users(id, name) SELECT id, name FROM users WHERE id = ?;
DELETE FROM users WHERE id = ?;

3. 查询对战记录时，联合用户表和归档表获取用户名：

SELECT 
    gr.id,
    COALESCE(u1.name, du1.name) AS player_1_name,
    COALESCE(u2.name, du2.name) AS player_2_name,
    gr.player_1_score,
    gr.player_2_score,
    gr.created_at
FROM game_records gr
LEFT JOIN users u1 ON gr.player_1_id = u1.id
LEFT JOIN deleted_users du1 ON gr.player_1_id = du1.id
LEFT JOIN users u2 ON gr.player_2_id = u2.id
LEFT JOIN deleted_users du2 ON gr.player_2_id = du2.id;

优点： #

• 活跃用户表仅保留有效数据，数据结构更清晰
• 归档表仅存储必要信息，避免冗余

缺点： #

• 需要额外维护归档逻辑，增加了删除操作的复杂度
• 查询时需关联多个表，SQL语句相对复杂

方案三：用触发器自动同步用户名到对战记录 #

保留你当前的game_records表结构，但通过数据库触发器自动维护用户名的同步，避免手动更新两处数据的麻烦，规则变更时只需修改users表的字段约束。

操作步骤： #

1. 创建触发器函数，当用户名称更新时同步更新游戏记录中的对应名称：

CREATE OR REPLACE FUNCTION update_game_record_player_name()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE game_records
    SET player_1_name = NEW.name
    WHERE player_1_id = NEW.id;
    
    UPDATE game_records
    SET player_2_name = NEW.name
    WHERE player_2_id = NEW.id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 给用户表添加更新触发器：

CREATE TRIGGER trigger_update_player_name
AFTER UPDATE OF name ON users
FOR EACH ROW
EXECUTE FUNCTION update_game_record_player_name();

优点： #

• 无需大幅修改现有表结构，适配成本低
• 用户名变更自动同步，避免手动操作的错误

缺点： #

• 触发器会增加用户名称更新时的写操作开销，高并发场景下需评估性能
• game_records表仍存在用户名冗余存储

方案选择建议 #

• 若用户规模不大，优先选择软删除方案，实现成本最低，维护最简单
• 若需要严格分离活跃与已删除用户数据，选择归档表方案
• 若不想改动现有表结构，可采用触发器方案减少手动维护成本

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