数据库表设计

1. 用户表

CREATE TABLE users (
  user_id       BIGINT UNSIGNED  PRIMARY KEY,
  username      VARCHAR(255) NOT NULL UNIQUE,
  password      VARCHAR(255) DEFAULT NULL,
  token         VARCHAR(255) DEFAULT NULL,
  anonymous     BOOLEAN NOT NULL DEFAULT FALSE,
  extra         JSON DEFAULT NULL,
  remark VARCHAR(256),
  creator VARCHAR(64) DEFAULT '',
  create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater VARCHAR(64) DEFAULT '',
  update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted SMALLINT default 0,
  tenant_id BIGINT NOT NULL DEFAULT 0,
);

1. `im_messages_history` 表

此表用于存储用户之间和群组之间交换的所有消息的历史记录，支持各种消息类型，并包含消息确认和序列化所需的字段。

表结构

CREATE TABLE im_messages_history (
  message_id     BIGINT UNSIGNED  PRIMARY KEY,
  cmd            INT UNSIGNED NOT NULL,
  sequence       BIGINT UNSIGNED NOT NULL,
  ack            BIGINT UNSIGNED DEFAULT NULL,
  from_user_id   BIGINT UNSIGNED NOT NULL,
  to_user_id     BIGINT UNSIGNED DEFAULT NULL,
  group_id       BIGINT UNSIGNED DEFAULT NULL,
  create_time    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  msg_type       ENUM('text', 'image', 'audio', 'video', 'news', 'markdown') NOT NULL,
  content        JSON NOT NULL,
  status         TINYINT UNSIGNED NOT NULL DEFAULT 1,
  extra          JSON DEFAULT NULL,
  deleted SMALLINT default 0,
  tenant_id BIGINT NOT NULL DEFAULT 0,
  -- 约束条件
  CHECK (
    (to_user_id IS NOT NULL AND group_id IS NULL) OR
    (to_user_id IS NULL AND group_id IS NOT NULL)
  ),
  -- 索引
  UNIQUE KEY idx_from_sequence (from_user_id, sequence),
  KEY idx_from_user_id (from_user_id),
  KEY idx_to_user_id (to_user_id),
  KEY idx_group_id (group_id),
  KEY idx_create_time (create_time),
  CONSTRAINT fk_from_user FOREIGN KEY (from_user_id) REFERENCES users(user_id),
  CONSTRAINT fk_to_user FOREIGN KEY (to_user_id) REFERENCES users(user_id),
  CONSTRAINT fk_group FOREIGN KEY (group_id) REFERENCES im_groups(group_id)
);

字段描述

message_id: 每条消息的唯一标识符。
cmd: 表示消息类型的命令代码。
sequence: 用于跟踪和确认的唯一消息序列号。
ack: 被确认的消息序列号（如果适用）。
from_user_id: 发送者的用户 ID。
to_user_id: 接收者的用户 ID（用于一对一消息）。
group_id: 群组 ID（用于群组消息）。
create_time: 消息创建的时间戳。
msg_type: 消息内容的类型。
content: 存储消息内容的 JSON 字段，基于 msg_type 进行变化。
status: 消息的状态（如发送、已送达、已读）。
extra: 额外数据的 JSON 字段。

说明

约束条件: 确保消息要么是发给用户，要么是发给群组，不能同时存在。
索引: 优化消息检索和跟踪的查询。
外键: 保持与 users 和 im_groups 表的参照完整性。
内容字段: 使用 JSON 格式存储消息内容，以灵活适应各种消息类型。

2. `im_friends` 表

此表管理用户之间的好友关系，支持好友请求、接受、阻止等功能。

表结构

CREATE TABLE im_friends (
  id              BIGINT UNSIGNED  PRIMARY KEY,
  user_id         BIGINT UNSIGNED NOT NULL,
  friend_user_id  BIGINT UNSIGNED NOT NULL,
  status          TINYINT UNSIGNED NOT NULL DEFAULT 0,
  create_time     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted SMALLINT default 0,
  tenant_id BIGINT NOT NULL DEFAULT 0,
  extra           JSON DEFAULT NULL,
  -- 约束条件
  UNIQUE KEY uniq_user_friend (user_id, friend_user_id),
  -- 索引
  KEY idx_user_id (user_id),
  KEY idx_friend_user_id (friend_user_id),
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(user_id),
  CONSTRAINT fk_friend_user FOREIGN KEY (friend_user_id) REFERENCES users(user_id)
);

字段描述

id: 每条好友关系记录的唯一标识符。
user_id: 用户 ID。
friend_user_id: 好友的用户 ID。
status: 好友关系的状态（0：待处理，1：已接受，2：已阻止）。
create_time: 好友关系创建的时间戳。
update_time: 好友关系最后更新时间的时间戳。
extra: 额外数据的 JSON 字段。

说明

唯一约束: 确保每个好友对是唯一的。
双向关系: 为每个好友关系存储两条记录，以便从任一用户的角度进行查询。
索引: 优化好友列表和状态检查的查询。
外键: 保持与 users 表的参照完整性。

3. `im_groups` 和 `im_group_members` 表

这些表用于管理群组信息和群组成员，支持群组创建、解散、成员管理和角色分配。

`im_groups` 表结构

CREATE TABLE im_groups (
  group_id          BIGINT UNSIGNED  PRIMARY KEY,
  group_name        VARCHAR(255) NOT NULL,
  creator_user_id   BIGINT UNSIGNED NOT NULL,
  description       TEXT DEFAULT NULL,
  create_time       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status            TINYINT UNSIGNED NOT NULL DEFAULT 1,
  extra             JSON DEFAULT NULL,
  deleted           SMALLINT default 0,
  tenant_id         BIGINT NOT NULL DEFAULT 0,
  -- 索引
  KEY idx_creator_user_id (creator_user_id),
  KEY idx_group_name (group_name),
  CONSTRAINT fk_creator_user FOREIGN KEY (creator_user_id) REFERENCES users(user_id)
);

`im_group_members` 表结构

CREATE TABLE im_group_members (
  id              BIGINT UNSIGNED  PRIMARY KEY,
  group_id        BIGINT UNSIGNED NOT NULL,
  user_id         BIGINT UNSIGNED NOT NULL,
  role            TINYINT UNSIGNED NOT NULL DEFAULT 1,
  mute            BOOLEAN NOT NULL DEFAULT FALSE,
  join_time       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  extra           JSON DEFAULT NULL,
  deleted         SMALLINT default 0,
  tenant_id       BIGINT NOT NULL DEFAULT 0,
  -- 约束条件
  UNIQUE KEY uniq_group_user (group_id, user_id),
  -- 索引
  KEY idx_group_id (group_id),
  KEY idx_user_id (user_id),
  CONSTRAINT fk_group FOREIGN KEY (group_id) REFERENCES im_groups(group_id),
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(user_id)
);

`im_groups` 表字段描述

group_id: 每个群组的唯一标识符。
group_name: 群组名称。
creator_user_id: 创建群组的用户 ID。
description: 群组的可选描述。
create_time: 群组创建的时间戳。
update_time: 群组最后更新时间的时间戳。
status: 群组的状态（1：活跃，0：解散）。
extra: 额外数据的 JSON 字段。

`im_group_members` 表字段描述

id: 每条群组成员记录的唯一标识符。
group_id: 群组 ID。
user_id: 群组成员的用户 ID。
role: 成员在群组中的角色（1：成员，2：管理员，3：所有者）。
mute: 指示成员是否已静音群组。
join_time: 用户加入群组的时间戳。
extra: 额外数据的 JSON 字段。

说明

群组成员: 通过 im_group_members 表进行管理，支持角色和静音状态。
约束条件: 确保每个群组和用户的唯一成员记录。
索引: 优化群组成员和管理的查询。
外键: 保持群组和用户之间的参照完整性。