废话不多说博主先上图效果
此次实时聊天功能参考了资料，以及请教了师父给出设计，只设计了四张表，分别是，组表，组成员表，聊天记录表，好友移除表。
这里我先说下我的设计思路组表的意思和QQ 群一样，一个组就代表一个群，一个房间，他们在这个房间聊天，我们把一对一好友聊天当成由两个人组成的群，他们在一个房间里聊天，这样只需在组表里加个标识，是好友聊天即可。组成员表的意思就记录这个房间有多少人，多少人可以接受到这个房间的消息，聊天记录表顾名思义，好友移除表的话，这个是为了记录，拉黑，单向删除，双向删除，比如我和小米是好友，我把小米删除了，但是小米的好友里还是有我，这个情况我们就不能把整个好友关系删除了，所以在这边要记录，我删除了小米，我的好友列表里没有小米，小米有我，当小米也把我删除时候，我们两人的关系才彻底删除，此时就要把我们之间组成的组，房间号给彻底删除。
以下是数据库
组表：
CREATE TABLE [dbo].[Groups]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [GroupTitle] [nvarchar](250) NULL, [GroupType] [int] NULL, [FinalPost] [nvarchar](250) NULL, [ChatType] [int] NULL, [LastUserId] [bigint] NULL, [UserId] [bigint] NULL, [Status] [int] NULL, [Created] [datetime] NULL, CONSTRAINT [PK__Groups__3214EC0738D1CDA8] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Groups] ADD CONSTRAINT [DF__Groups__GroupTyp__5649C92D] DEFAULT ((0)) FOR [GroupType] GO
ALTER TABLE [dbo].[Groups] ADD CONSTRAINT [DF__Groups__LastUser__573DED66] DEFAULT ((0)) FOR [LastUserId] GO
ALTER TABLE [dbo].[Groups] ADD CONSTRAINT [DF__Groups__UserId__5832119F] DEFAULT ((0)) FOR [UserId] GO
ALTER TABLE [dbo].[Groups] ADD CONSTRAINT [DF__Groups__Status__592635D8] DEFAULT ((0)) FOR [Status] GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘自增序列‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Groups‘, @level2type=N‘COLUMN‘,@level2name=N‘Id‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘组标题‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Groups‘, @level2type=N‘COLUMN‘,@level2name=N‘GroupTitle‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘组类型1是单聊天2是群聊‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Groups‘, @level2type=N‘COLUMN‘,@level2name=N‘GroupType‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘最后发表内容‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Groups‘, @level2type=N‘COLUMN‘,@level2name=N‘FinalPost‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘消息类型‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Groups‘, @level2type=N‘COLUMN‘,@level2name=N‘ChatType‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘最后发表者‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Groups‘, @level2type=N‘COLUMN‘,@level2name=N‘LastUserId‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘创建者‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Groups‘, @level2type=N‘COLUMN‘,@level2name=N‘UserId‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘状态‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Groups‘, @level2type=N‘COLUMN‘,@level2name=N‘Status‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘创建时间‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Groups‘, @level2type=N‘COLUMN‘,@level2name=N‘Created‘ GO
EXEC sys.sp_addextendedproperty @name=N‘Groups‘, @value=N‘组表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Groups‘ GO
组成员表：
CREATE TABLE [dbo].[GroupMembers]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [GroupId] [bigint] NULL, [GroupUserId] [bigint] NULL, [Status] [int] NULL, [Created] [datetime] NULL, PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[GroupMembers] ADD DEFAULT ((0)) FOR [GroupId] GO
ALTER TABLE [dbo].[GroupMembers] ADD DEFAULT ((0)) FOR [GroupUserId] GO
ALTER TABLE [dbo].[GroupMembers] ADD DEFAULT ((0)) FOR [Status] GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘自增序列‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘GroupMembers‘, @level2type=N‘COLUMN‘,@level2name=N‘Id‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘组ID关联组表ID‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘GroupMembers‘, @level2type=N‘COLUMN‘,@level2name=N‘GroupId‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘组成员ID‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘GroupMembers‘, @level2type=N‘COLUMN‘,@level2name=N‘GroupUserId‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘状态‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘GroupMembers‘, @level2type=N‘COLUMN‘,@level2name=N‘Status‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘创建时间‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘GroupMembers‘, @level2type=N‘COLUMN‘,@level2name=N‘Created‘ GO
EXEC sys.sp_addextendedproperty @name=N‘GroupMembers‘, @value=N‘组成员表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘GroupMembers‘ GO
聊天记录表：
CREATE TABLE [dbo].[Messages]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [ChatContent] [ntext] NULL, [ChatType] [int] NULL, [SendingStatus] [int] NULL, [GroupId] [bigint] NULL, [UserId] [bigint] NULL, [Created] [datetime] NULL, PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Messages] ADD DEFAULT ((0)) FOR [ChatType] GO
ALTER TABLE [dbo].[Messages] ADD DEFAULT ((0)) FOR [SendingStatus] GO
ALTER TABLE [dbo].[Messages] ADD DEFAULT ((0)) FOR [GroupId] GO
ALTER TABLE [dbo].[Messages] ADD DEFAULT ((0)) FOR [UserId] GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘自增序列‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Messages‘, @level2type=N‘COLUMN‘,@level2name=N‘Id‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘消息内容‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Messages‘, @level2type=N‘COLUMN‘,@level2name=N‘ChatContent‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘消息类型‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Messages‘, @level2type=N‘COLUMN‘,@level2name=N‘ChatType‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘发送状态‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Messages‘, @level2type=N‘COLUMN‘,@level2name=N‘SendingStatus‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘组Id‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Messages‘, @level2type=N‘COLUMN‘,@level2name=N‘GroupId‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘创建者ID‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Messages‘, @level2type=N‘COLUMN‘,@level2name=N‘UserId‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘创建时间‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Messages‘, @level2type=N‘COLUMN‘,@level2name=N‘Created‘ GO
EXEC sys.sp_addextendedproperty @name=N‘Messages‘, @value=N‘聊天记录表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Messages‘ GO
好友移除表
CREATE TABLE [dbo].[FriendsRemove]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [GroupId] [bigint] NULL, [UserId] [bigint] NULL, [Status] [int] NULL, [Created] [datetime] NULL, PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FriendsRemove] ADD DEFAULT ((0)) FOR [GroupId] GO
ALTER TABLE [dbo].[FriendsRemove] ADD DEFAULT ((0)) FOR [UserId] GO
ALTER TABLE [dbo].[FriendsRemove] ADD DEFAULT ((0)) FOR [Status] GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘自增序列‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘FriendsRemove‘, @level2type=N‘COLUMN‘,@level2name=N‘Id‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘组Id‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘FriendsRemove‘, @level2type=N‘COLUMN‘,@level2name=N‘GroupId‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘删除记录人ID‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘FriendsRemove‘, @level2type=N‘COLUMN‘,@level2name=N‘UserId‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘状态‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘FriendsRemove‘, @level2type=N‘COLUMN‘,@level2name=N‘Status‘ GO
EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘创建时间‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘FriendsRemove‘, @level2type=N‘COLUMN‘,@level2name=N‘Created‘ GO
EXEC sys.sp_addextendedproperty @name=N‘FriendsRemove‘, @value=N‘临时好友移除表‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘FriendsRemove‘ GO
次博客本人原创 如果各位有伙伴有不懂之处，或者发现博主的设计缺点 欢迎指出 转载 林啊铖 QQ 964878912