即时聊天系统中群组和好友关系Mysql数据表设计

用户数据表

CREATE TABLE `wx_contacts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `wxid` varchar(64)  NOT NULL DEFAULT '' COMMENT 'weixinid微信',
  `data_type` varchar(16)  NOT NULL DEFAULT '' COMMENT '接口状态码',
  `addFrom` varchar(64)  NOT NULL DEFAULT '' COMMENT '添加渠道',
  `alias` varchar(255)  NOT NULL DEFAULT '' COMMENT 'alias',
  `conRemark` varchar(1024)  NOT NULL DEFAULT '' COMMENT '备注',
  `entryptUsername` varchar(1024)  NOT NULL DEFAULT '' COMMENT '加密的用户名',
  `gender` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '性别男1-女2',
  `headPic` varchar(255)  NOT NULL DEFAULT '' COMMENT '头像',
  `isForceUpdate` varchar(32)  NOT NULL DEFAULT '' COMMENT '是否强制更新',
  `labelIds` varchar(32)  NOT NULL DEFAULT '' COMMENT '标签id',
  `labelNames` varchar(255)  NOT NULL DEFAULT '' COMMENT '标签名字',
  `nickName` varchar(255)  NOT NULL DEFAULT '' COMMENT '微信昵称',
  `opType` varchar(32)  NOT NULL DEFAULT '',
  `phone` varchar(16)  NOT NULL DEFAULT '' COMMENT '好友绑定手机',
  `pyInitial` varchar(128)  NOT NULL DEFAULT '' COMMENT '首字母',
  `quanPin` varchar(255)  NOT NULL DEFAULT '' COMMENT '全拼',
  `region` varchar(255)  NOT NULL DEFAULT '' COMMENT '地区',
  `selfWxId` varchar(255)  NOT NULL DEFAULT '' COMMENT '所有者微信id',
  `userName` varchar(255)  NOT NULL DEFAULT '' COMMENT '用户名id',
  PRIMARY KEY (`id`),
  KEY `userName` (`userName`),
  KEY `selfWxId` (`selfWxId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6508 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='通讯录好友关系表';

用户关系表

CREATE TABLE `wx_contacts_relation` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `wx_id` int unsigned NOT NULL DEFAULT '0' comment '用户-id',
  `friend_id` int unsigned  NOT NULL DEFAULT '0' COMMENT '好友-id',
  `is_out` tinyint(1) unsigned  NOT NULL DEFAULT '0' COMMENT '0单向,1=双向',
  `user_group` varchar(16) COLLATE utf8mb4_unicode_ci not null DEFAULT '' comment '好友分组',
  `user_group` varchar(16) COLLATE utf8mb4_unicode_ci not null DEFAULT '' comment '对方好友分组',
  `created_at` timestamp NULL DEFAULT NULL comment '加好友时间',
  `updated_at` timestamp NULL DEFAULT NULL comment '删除好友时间',
  PRIMARY KEY (`id`),
  KEY `friend_id` (`friend_id`),
  KEY `wx_id` (`wx_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='wx好友关系表';

群数据表设计：

CREATE TABLE `wx_chatroom` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `wxid` varchar(64)  NOT NULL DEFAULT '' COMMENT '操作者微信id',
  `data_type` varchar(16)  NOT NULL DEFAULT '' COMMENT '接口状态码',
  `chatroomHeadPic` varchar(512)  NOT NULL DEFAULT '' COMMENT '群头像',
  `chatroomId` varchar(255)  NOT NULL DEFAULT '' COMMENT '群id',
  `chatroomOwner` varchar(255)  NOT NULL DEFAULT '' COMMENT '群所有者',
  `conRemark` varchar(255)  NOT NULL DEFAULT '' COMMENT '群的备注',
  `members` longtext  COMMENT '群成员json',
  `nickName` varchar(255)  NOT NULL DEFAULT '' COMMENT '群名称',
  `opType` varchar(32)  NOT NULL DEFAULT '' COMMENT '操作类型',
  `pyInitial` varchar(255)  NOT NULL DEFAULT '' COMMENT '群首字母',
  `quanPin` varchar(255)  NOT NULL DEFAULT '' COMMENT '群全拼',
  PRIMARY KEY (`id`),
  KEY `chatroomOwner` (`chatroomOwner`) USING BTREE,
  KEY `chatroomId` (`chatroomId`)
) ENGINE=InnoDB AUTO_INCREMENT=516 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='wx群信息表';

群关系数据库设计：

CREATE TABLE `wx_chatroom_relation` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` bigint(20) unsigned NOT NULL DEFAULT '0' comment 'wx群id',
  `wx_id` int unsigned  NOT NULL DEFAULT '0' COMMENT '成员id',
  `is_out` tinyint(1) unsigned  NOT NULL DEFAULT '0' COMMENT '0未退,1=退群',
  `created_at` timestamp NULL DEFAULT NULL comment '加群时间',
  `updated_at` timestamp NULL DEFAULT NULL comment '退群时间',
  PRIMARY KEY (`id`),
  KEY `group_id` (`group_id`) USING BTREE,
  KEY `wx_id` (`wx_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='wx群成员关系表';

查询sql：

-- 查询id: 4的好友列表，及其分组
SELECT friend_id AS friends, user_group AS my_group
FROM users_g
WHERE user_id = '4'
UNION ALL
SELECT user_id AS friends, friend_group AS my_group
FROM users_g
WHERE friend_id = '4';