聊天表設計

夢想星辰大海發表於2022-09-23

具體表結構

CREATE TABLE `chat_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
 `username` varchar(64) DEFAULT NULL COMMENT '使用者名稱',
 `password` varchar(64) DEFAULT NULL COMMENT '使用者密碼',
 `created_at` int(11) DEFAULT '0' COMMENT '建立時間',
 PRIMARY KEY (`id`),
 UNIQUE KEY `chat_user_username_IDX` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='使用者表';

CREATE TABLE `chat_user_friend` (
  `friend_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `user_id_a` int(11) NOT NULL DEFAULT '0' COMMENT '使用者a的id',
  `user_id_b` int(11) NOT NULL DEFAULT '0' COMMENT '使用者b的id',
  `status` tinyint(3) unsigned DEFAULT '0' COMMENT '關係狀態 1:a主動刪除b, 2:b主動刪除a, 4:ab已相互刪除, 8:a主動拉黑b, 16:a主動拉黑b, 32:ab已相互拉黑',
  `user_a_last_ack_seq` char(18) DEFAULT NULL COMMENT '使用者a的客戶端最後獲得的訊息id',
  `user_b_last_ack_seq` char(18) DEFAULT NULL COMMENT '使用者b的客戶端最後獲得的訊息id',
  PRIMARY KEY (`friend_id`),
  UNIQUE KEY `chat_user_friend_user_id_ab_IDX` (`user_id_a`,`user_id_b`) USING BTREE,
  KEY `chat_user_friend_user_id_b_IDX` (`user_id_b`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='好友關係表';

CREATE TABLE `chat_friend_record` (
 `record_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
 `friend_id` int(11) NOT NULL DEFAULT '0' COMMENT '好友關係表id',
 `sender` int(11) NOT NULL DEFAULT '0' COMMENT '傳送方',
 `receiver` int(11) NOT NULL DEFAULT '0' COMMENT '接受方',
 `seq` char(18) DEFAULT NULL COMMENT '序列號',
 `content` text COMMENT '訊息內容',
 `created_at` int(11) DEFAULT '0' COMMENT '建立時間',
 PRIMARY KEY (`record_id`),
 KEY `chat_friend_record_friend_id_IDX` (`friend_id`),
 UNIQUE KEY `chat_friend_record_seq_IDX` (`seq`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='好友單聊天記錄表';

CREATE TABLE `chat_room` (
 `room_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
 `room_name` char(64) DEFAULT NULL COMMENT '群名字',
 `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '群主id',
 `status` tinyint(3) DEFAULT '0' COMMENT '是否解散 0:正常 1:已解散',
 `created_at` int(11) DEFAULT '0' COMMENT '建立時間',
 PRIMARY KEY (`room_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='群表';

CREATE TABLE `chat_room_member` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
 `room_id` int(11) NOT NULL DEFAULT '0' COMMENT '群id',
 `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '使用者id',
 `status` tinyint(1) DEFAULT '0' COMMENT '在本群的狀態 0:正常 1:已禁言 2:已踢出',
 `created_at` int(11) DEFAULT '0' COMMENT '建立時間',
 `last_ack_seq` char(18) DEFAULT NULL COMMENT '群成員的客戶端最後獲得的群訊息id',
 PRIMARY KEY (`id`),
 UNIQUE KEY `chat_room_member_room_user_id_IDX` (`room_id`,`user_id`),
 KEY `chat_room_member_user_id_IDX` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='群成員表';

CREATE TABLE `chat_room_record` (
 `record_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
 `room_id` int(11) NOT NULL DEFAULT '0' COMMENT '群表id',
 `sender` int(11) NOT NULL DEFAULT '0' COMMENT '傳送方',
 `seq` char(18) DEFAULT NULL COMMENT '序列號',
 `content` text COMMENT '訊息內容',
 `created_at` int(11) DEFAULT '0' COMMENT '建立時間',
 PRIMARY KEY (`record_id`),
 UNIQUE KEY `chat_room_record_room_id_IDX` (`room_id`),
 UNIQUE KEY `chat_room_record_seq_IDX` (`seq`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='群聊天記錄表';

拉取我沒主動刪除的好友的sql示例

(SELECT * from chat_user_friend force index(chat_user_friend_user_id_ab_IDX) where user_id_a = 1 and status & 4 != 4 and status & 1 = 0) UNION ALL (SELECT * from chat_user_friend force index(chat_user_friend_user_id_b_IDX) where user_id_b = 1 and status & 4 != 4 and status & 2 = 0)

收發訊息流程

使用者發訊息流程：是否好友或群友，寫入資料庫，傳送給線上人，客戶端收到訊息，傳送ack訊息給服務端，改寫last_ack_seq欄位
剛登入使用者收取訊息流程：根據last_ack_seq，去對應的群聊表、單聊查詢大於last_ack_seq的資料返回給客戶端，客戶端收到訊息，傳送ack訊息給服務端，改寫last_ack_seq欄位

參考文章

群聊比單聊，憑什麼複雜這麼多？
訊息順序性，究竟為什麼這麼難？

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章