【数据库】数据库架构设计（AI助理消息存储）

概述

项目中涉及到对回调消息的存储，于是需要设计一个数据库，针对于该次设计数据库的过程进行总结并反思

架构

要点总结

• 设计数据库的时候遵循基本的范式要求
• 关联表的使用，因为存在一个群里可有多个好友，一个人在一个群里是唯一，所以为了连接用户表、消息表以及群组表，设计一个关联表，是得其可以关联起来
• 每个表中增加创建和更新时间的记录，从而知道添加和更新的时间
• 每个表中增加逻辑删除的逻辑，避免因为物理删除造成数据误删的情况

数据库操作二次封装

通过对数据库的二次封装，实现通过代码对数据库的控制，最终实现可以在项目代码中实时的调用数据库。此处使用的是pymysql

创建表

# -------------------- 创建表结构函数（四张主要关系表） --------------------
    def create_users_table(self):
        """
        创建 users 表
        """
        sql = """
        CREATE TABLE users (
            user_id VARCHAR(50) PRIMARY KEY COMMENT '用户ID，唯一标识',
            remark VARCHAR(255) COMMENT '备注名',
            nickname VARCHAR(255) COMMENT '系统设置的昵称',
            username VARCHAR(255) NOT NULL COMMENT '用户名',
            realname VARCHAR(255) COMMENT '真实姓名',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
            deleted_at TIMESTAMP NULL COMMENT '逻辑删除时间',
            INDEX idx_username (username),
            INDEX idx_realname (realname)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
        """
        return self._execute_query(sql)

    def create_groups_table(self):
        """
        创建 groups 表 (使用反引号括起表名)
        """
        sql = """
        CREATE TABLE `groups` (  -- 使用反引号
            conversation_id VARCHAR(50) PRIMARY KEY COMMENT '会话ID，唯一标识群聊',
            group_nickname VARCHAR(255) COMMENT '群组名称',
            total_members INT UNSIGNED COMMENT '群组总人数',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
            deleted_at TIMESTAMP NULL COMMENT '逻辑删除时间',
            INDEX idx_group_nickname (group_nickname)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='群组表';
        """
        return self._execute_query(sql)

    def create_group_members_table(self):
        """
        创建 group_members 表 (使用反引号括起表名)
        """
        sql = """
        CREATE TABLE group_members (
            user_id VARCHAR(50) COMMENT '用户ID',
            conversation_id VARCHAR(50) COMMENT '会话ID',
            join_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '加入群组时间',
            PRIMARY KEY (user_id, conversation_id),
            FOREIGN KEY (user_id) REFERENCES users(user_id),
            FOREIGN KEY (conversation_id) REFERENCES `groups`(conversation_id) -- 使用反引号
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='群组成员关联表';
        """
        return self._execute_query(sql)

    def create_messages_table(self):
        """
        创建 messages 表 (使用反引号括起表名)
        """
        sql = """
        CREATE TABLE messages (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '消息ID，自增主键',
            conversation_id VARCHAR(50) NOT NULL COMMENT '会话ID，群组ID',
            sender_id VARCHAR(50) NOT NULL COMMENT '发送者ID，用户ID',
            sender_name VARCHAR(255) NOT NULL COMMENT '发送者名字',
            content TEXT COMMENT '消息内容',
            content_type INT COMMENT '消息类型',
            receiver_id VARCHAR(50) COMMENT '接收者ID (群聊消息接收者是群组，这里可以先保留)',
            send_time BIGINT UNSIGNED NOT NULL COMMENT '消息发送时间 (时间戳)',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '消息记录创建时间',
            deleted_at TIMESTAMP NULL COMMENT '逻辑删除时间',
            FOREIGN KEY (conversation_id) REFERENCES `groups`(conversation_id), -- 使用反引号
            FOREIGN KEY (sender_id) REFERENCES users(user_id),
            INDEX idx_conversation_id (conversation_id),
            INDEX idx_sender_id (sender_id),
            INDEX idx_send_time (send_time)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='群聊消息表';
        """
        return self._execute_query(sql)
    
    
    # --- 创建表结构 ---
    print("创建 users 表:", db.create_users_table())
    print("创建 groups 表:", db.create_groups_table())
    print("创建 group_members 表:", db.create_group_members_table())
    print("创建 messages 表:", db.create_messages_table())

插入用户测试

    user_id_test = "test_user_123"
    db.insert_user(user_id=user_id_test, username="testuser", realname="测试用户")
    user = db.get_user(user_id=user_id_test)
    print("获取用户:", user)

更新用户信息

    db.update_user(user_id=user_id_test, username="updated_user")
    updated_user = db.get_user(user_id=user_id_test)
    print("更新后用户:", updated_user)</