mysql仿网易评论_仿网易新闻客户端评论盖楼

最新推荐文章于 2021-11-03 13:48:48 发布

张博士-体态康复

最新推荐文章于 2021-11-03 13:48:48 发布

阅读量226

点赞数

文章标签： mysql仿网易评论

本文链接：https://blog.csdn.net/weixin_35796207/article/details/113722061

版权

该博客介绍了如何使用MySQL数据库设计评论系统，包括评论表和盖楼关联表的结构，以及存储过程来获取分页评论和盖楼评论。通过示例数据展示了评论和回复的组合方法，并提供了获取回复评论和发送评论的SQL存储过程。

摘要由CSDN通过智能技术生成

最近做一款app，需要用到评论盖楼，像网易新闻客户端一样，表设计

评论表

CREATE TABLE [dbo].[comment](

[id] [int] IDENTITY(1,1) NOT NULL,

[uid] [int] NOT NULL,

[movieid] [int] NOT NULL,

[msg] [nvarchar](2000) NULL,

[time] [int] NULL,

[ip] [varchar](20) NULL,

[up] [int] NULL,

[down] [int] NULL,

[username] [nvarchar](200) NOT NULL,

[touid] [int] NULL,

[tousername] [nvarchar](200) NULL,

CONSTRAINT [PK_comment] PRIMARY KEY NONCLUSTERED

(

[id] ASC

))

评论盖楼关联表

CREATE TABLE [dbo].[comment_relation](

[comm_id] [int] NOT NULL,

[floor_id] [int] NOT NULL,

CONSTRAINT [PK_comment_relation] PRIMARY KEY CLUSTERED

(

[comm_id] ASC,

[floor_id] ASC

)

创建存储过程

CREATE PROCEDURE [dbo].[getMovieComment]

@mid int,

@commid int=0,

@pagesize int

BEGIN

SET NOCOUNT ON;

declare @table table(id int);

--返回分页的评论

if @commid > 0

begin

select id as commid,"uid",movieid,msg,"time",up,"username",touid,tousername from comment where movieid=@mid and id

insert into @table select id from comment where movieid=@mid and id

end

else

begin

select id as commid,"uid",movieid,msg,"time",up,"username",touid,tousername from comment where movieid=@mid order by id desc offset 0 row fetch next @pagesize rows only

insert into @table select id from comment where movieid=@mid order by id desc offset 0 row fetch next @pagesize rows only;

end

--返回盖楼的评论

select c.id as commid,"uid",movieid,msg,"time",up,"username",touid,tousername,comm_id from @table a inner join comment_relation b on a.id=b.comm_id inner join comment c on b.floor_id=c.id

END

下面给出sql

select id as commid,"uid",movieid,msg,"time",up,"username",touid,tousername from comment where movieid=2 order by id desc

获取所有的评论

第一步

以下这些数据为评论数据成为 aData

commid,"uid",movieid,msg,"time",up,"username",touid,tousername

95829999999999999145213617108571275306fanqiang1

94728888888888888814521360920fanqiang2118679158318

9362777777777777777714521355540fanqiang13654321

925266666666666666666666614521350470fanqiang2123456

914255555555555555514521349350xiaofan123118679158318

903244444444444444444414521348140654321118679158318

892222222222222214521332560123456118679158318

88121111111111111111111111114521332440186791583180NULL

第二步

temp 表示用来记录一部电影的评论id

select c.id as commid,"uid",movieid,msg,"time",up,"username",touid,tousername,comm_id from comment_relation b inner join comment c on b.floor_id=c.id inner join temp a on a.id=b.comm_id

以下这些数据为跟帖数据，这些数据是通过中间表comment_relation获取到的拷贝楼层数据，也就是盖楼数据成为 bData

commid,"uid",movieid,msg,"time",up,"username",touid,tousername, comm_id

88121111111111111111111111114521332440186791583180NULL89

88121111111111111111111111114521332440186791583180NULL90

88121111111111111111111111114521332440186791583180NULL91

88121111111111111111111111114521332440186791583180NULL92

89222222222222221452133256012345611867915831892

88121111111111111111111111114521332440186791583180NULL93

90324444444444444444441452134814065432111867915831893

88121111111111111111111111114521332440186791583180NULL94

88121111111111111111111111114521332440186791583180NULL95

90324444444444444444441452134814065432111867915831895

9362777777777777777714521355540fanqiang1365432195

现在出现了comm_id与第一步中的commid相关联起来组成盖楼数据

将评论(aData)和盖楼数 (bData)据根据关联键comm_id组合

php 组合代码

public function combind(array $aData, array $bData)

{

$aNewData = array();

$id = 0;

foreach($bData as $key => $val) {

$id = $val['comm_id'];

unset($val['comm_id']);

$aNewData[$id][] = $val;

}

for ($i=0, $j = count($aData); $i < $j; $i++) {

$id = $aData[$i]['commid'];

if (array_key_exists($id, $aNewData)) {

$aData[$i]['floor_msg'] = $aNewData[$id];

} else {

$aData[$i]['floor_msg'] = array();

}

return $aData;

}

另给出其他两个需求

获取回复我的评论列表

CREATE PROCEDURE [dbo].[getReplyComments]

@uid int,

@commid int=0,

@pagesize int=10

BEGIN

SET NOCOUNT ON;

declare @table table(id int);

--返回我发布的评论分页

if @commid > 0

begin

select id as commid,"uid",movieid,msg,"time",up,"username",touid,tousername from comment where touid=@uid and id

insert into @table select id from comment where touid=@uid and id

end

else

begin

select id as commid,"uid",movieid,msg,"time",up,"username",touid,tousername from comment where touid=@uid order by id desc offset 0 row fetch next @pagesize rows only

insert into @table select id from comment where touid=@uid order by id desc offset 0 row fetch next @pagesize rows only

end

--返回盖楼的评论

select c.id as commid,"uid",movieid,msg,"time",up,"username",touid,tousername,comm_id from @table a inner join comment_relation b on a.id=b.comm_id inner join comment c on b.floor_id=c.id

END

====================================================================================

获取我的评论列表

ALTER PROCEDURE [dbo].[getSendComments]

@uid int,

@commid int,

@pagesize int=10

BEGIN

SET NOCOUNT ON;

declare @table table(id int);

if @commid > 0

begin

--返回我发布的评论

select id as commid,"uid",movieid,msg,"time",up,"username",touid,tousername from comment where uid=@uid and id

--获取我发布的评论id

insert into @table select id from comment where uid=@uid and id

end

else

begin

select id as commid,"uid",movieid,msg,"time",up,"username",touid,tousername from comment where uid=@uid order by id desc offset 0 row fetch next @pagesize rows only

insert into @table select id from comment where uid=@uid order by id desc offset 0 row fetch next @pagesize rows only

end

--返回盖楼的评论

select c.id as commid,"uid",movieid,msg,"time",up,"username",touid,tousername,comm_id from @table a inner join comment_relation b on a.id=b.comm_id inner join comment c on b.floor_id=c.id

END

(误区)

select c.id as commid,"uid",movieid,msg,"time",up,"username",touid,tousername from comment_relation b inner join comment c on b.floor_id=c.id and movieid=2

发表一个帖子对一个楼层的回复，需要把这些楼层都复制一份，然后再加上自己的回复重新生成一个楼

此时查询出来了所有的楼层跟帖数据，但是没有一个评论id来关联自己的回复，所有这条sql语句不行