高访问量的评论系统数据库存储过程架构

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CommentsTables](
	[ID] [int] IDENTITY(1,1) NOT NULL,[Key] [nvarchar](50) NOT NULL,[TableName] [nvarchar](80) NOT NULL,[StartID] [int] NOT NULL,[EndID] [int] NOT NULL,CONSTRAINT [PK_SysTables] 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






--根据SourceID和key获得表名
create function [dbo].[funGetTableName]
(
@SourceID int,@Key nvarchar(50)
)
RETURNS nvarchar(80)
as 
begin
	declare @tableName nvarchar(80);
	declare @tableArea int;
	declare @mod int;
	
	declare @Size int;
	set @Size = 1000;
	
	set @mod = @SourceID % @Size;
	if @mod > 0 
		set @tableArea = Cast((@SourceID-1) / @Size as int) + 1;		
	else	
		set @tableArea = Cast((@SourceID-1) / @Size as int);
			
			
	set @tableName = 'comments_' + @Key +  Cast(@tableArea as nvarchar(10));
	
	return @tableName
end
GO







--评论写入调用存储过程

CREATE proc [dbo].[procAddComment]
(
@ParentID int,@SourceID int,@NickName nvarchar(20),@Content nvarchar(300),@IP nvarchar(30),@City nvarchar(30),@BeFiltered bit,@Disable bit,@Key nvarchar(50),@InsertedID int Output
)
as
begin
	declare @tableName nvarchar(80);
	declare @tableArea int;
	declare @mod int;
	
	declare @Size int;
	set @Size = 1000;
	
	set @mod = @SourceID % @Size;
	if @mod > 0 
		set @tableArea = Cast((@SourceID-1) / @Size as int) + 1;		
	else	
		set @tableArea = Cast((@SourceID-1) / @Size as int);
		


	
	set @tableName = 'comments_' + @Key +  Cast(@tableArea as nvarchar(10));
	
	if not Exists(select 'x' from [CommentsTables] where [Key][email?protected] and [TableName][email?protected])	
	begin
	
		declare @StartID int;
		declare @EndID int;
		
		set @EndID = @tableArea * @Size;
		set @StartID = @EndID - (@Size-1);
	
	
		--创建表
		declare @CreateSQL nvarchar(MAX);
		set @CreateSQL = 
		'Create table [dbo].['[email?protected]+'](
		[ID] [int] IDENTITY(1,[ParentID] [int] NOT NULL,[SourceID] [int] NOT NULL,[NickName] [nvarchar](20) NOT NULL,[Content] [nvarchar](300) NOT NULL,[Datetime] [datetime] NOT NULL,[IP] [nvarchar](30) NOT NULL,[City] [nvarchar](30) NOT NULL,[BeFiltered] [bit] NOT NULL,[Disable] [bit] NOT NULL,[Lou] [int] NOT NULL,[Ding] [int] NOT NULL,[Cai] [int] NOT NULL,CONSTRAINT [PK_'[email?protected]+'] PRIMARY KEY CLUSTERED 
		(
			[ID] ASC
		)WITH (PAD_INDEX  = OFF,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
		) ON [PRIMARY]'
		
		
		EXEC(@CreateSQL);
		
		
		--创建索引 ID DESC
		EXEC('		
		CREATE UNIQUE NONCLUSTERED INDEX [IX_'[email?protected]+'_ID_DESC] ON [dbo].['[email?protected]+'] 
		(
			[ID] DESC
		)WITH (PAD_INDEX  = OFF,SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,ONLINE = OFF,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]');
		
		--创建索引 Ding DESC
		EXEC('
		CREATE NONCLUSTERED INDEX [IX_'[email?protected]+'_Ding_DESC] ON [dbo].['[email?protected]+'] 
		(
			[Ding] DESC
		)WITH (PAD_INDEX  = OFF,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]');
		
		--创建索引 SourceID DESC
		EXEC('
		CREATE NONCLUSTERED INDEX [IX_'[email?protected]+'_SourceID_DESC] ON [dbo].['[email?protected]+'] 
		(
			[SourceID] DESC
		)WITH (PAD_INDEX  = OFF,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]');

		--创建索引 Lou DESC
		EXEC('
		CREATE NONCLUSTERED INDEX [IX_'[email?protected]+'_Lou_DESC] ON [dbo].['[email?protected]+'] 
		(
			[Lou] DESC
		)WITH (PAD_INDEX  = OFF,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]')
		
		
		--创建默认值
		EXEC('ALTER TABLE [dbo].['[email?protected]+'] ADD  CONSTRAINT [DF_'[email?protected]+'_ParentID]  DEFAULT ((0)) FOR [ParentID]');
		
		EXEC('ALTER TABLE [dbo].['[email?protected]+'] ADD  CONSTRAINT [DF_'[email?protected]+'_Datetime]  DEFAULT (getdate()) FOR [Datetime]');
		
		EXEC('ALTER TABLE [dbo].['[email?protected]+'] ADD  CONSTRAINT [DF_'[email?protected]+'_BeFiltered]  DEFAULT ((0)) FOR [BeFiltered]');	

		EXEC('ALTER TABLE [dbo].['[email?protected]+'] ADD  CONSTRAINT [DF_'[email?protected]+'_Disable]  DEFAULT ((0)) FOR [Disable]');
	
		EXEC('ALTER TABLE [dbo].['[email?protected]+'] ADD  CONSTRAINT [DF_'[email?protected]+'_Lou]  DEFAULT ((1)) FOR [Lou]');
			
		EXEC('ALTER TABLE [dbo].['[email?protected]+'] ADD  CONSTRAINT [DF_'[email?protected]+'_Ding]  DEFAULT ((0)) FOR [Ding]');
		
		EXEC('ALTER TABLE [dbo].['[email?protected]+'] ADD  CONSTRAINT [DF_'[email?protected]+'_Cai]  DEFAULT ((0)) FOR [Cai]');
		
		Insert Into [CommentsTables]([Key],[TableName],[StartID],[EndID]) values(@Key,@tableName,@StartID,@EndID);
	end 

	
	declare @TemLou int;
	declare @SQL nvarchar(MAX);
	set @SQL = N'select @TemLou = Count(ID) from dbo.['[email?protected]+N'] where [email?protected]';
	
	exec sp_executesql @SQL,N'@SourceID int,@TemLou int output',@SourceID,@TemLou output;
	
	if @TemLou = 0
		set @TemLou = 1;
	else
		set @TemLou = @TemLou + 1;
	
	
	
	declare @Lou int;
	set @Lou = @TemLou;
	
	declare @InsertSQL nvarchar(MAX);
	set @InsertSQL = N'Insert Into dbo.['[email?protected]+N'](ParentID,SourceID,NickName,Content,IP,City,BeFiltered,[Disable],[Lou])
	values (@ParentID,@NickName,@Content,@IP,@City,@BeFiltered,@Disable,@Lou);select @InsertedID = SCOPE_IDENTITY();';
	
	exec sp_executesql @InsertSQL,N'@ParentID int,@Lou int,@InsertedID int output',@ParentID,@Lou,@InsertedID output;
end 








GO















--获得最新评论存储过程

CREATE proc [dbo].[procGetNewComments]
(
@SourceID int,@PageIndex int,@PageSize int,@Fields nvarchar(100),@PageCount int output
)
as
begin
	declare @tableName nvarchar(80);
	set @tableName = dbo.funGetTableName(@SourceID,@Key);
	declare @Rc int;	
	
	declare @SQL nvarchar(MAX);
	set @SQL = N'select @Rc = COUNT(ID) from dbo.['[email?protected]+N'] where SourceID = @SourceID';	
	
	exec sp_executesql @SQL,@Rc int output',@Rc output;
	
	if @Rc % @PageSize > 0 
		set @PageCount = Cast(@Rc / @PageSize as int) + 1;
	else
		set @PageCount = Cast(@Rc / @PageSize as int);
		

	
	if @PageIndex = 1 
		begin
			set @SQL = N'select top '+Cast(@PageSize as nvarchar(30))+' '[email?protected] + N' from dbo.['[email?protected]+N'] where [email?protected] order by Lou desc';					
			exec sp_executesql @SQL,N'@SourceID int',@SourceID;
		end 		
	else
		begin
			declare @StartLou int;
			declare @EndLou int;
			
			--1 20  1 - 20,21- 40,41-60
			set @EndLou =  @Rc - (@PageIndex-1) * @PageSize;
			
			if @EndLou > @Rc 
				set @EndLou  = @Rc;			
			
			set @StartLou = @EndLou - @PageSize + 1;
			
			if @StartLou < 1 
				set @StartLou = 1;
			
			
			set @SQL = N'select '[email?protected] + N' from dbo.['[email?protected]+N'] where Lou>[email?protected] and Lou<[email?protected] and SourceID = @SourceID order by Lou desc';
			
			
			exec sp_executesql @SQL,@StartLou int,@EndLou int',@StartLou,@EndLou;
		end
	
end




GO











--踩

Create proc [dbo].[procCai]
(
@ID int,@key nvarchar(50),@Times int output
)
as
begin
	declare @tableName nvarchar(80);
	set @tableName = dbo.funGetTableName(@SourceID,@key);
	
	declare @SQL nvarchar(MAX);
	set @SQL = N'update dbo.['[email?protected]+N'] set Cai=Cai+1 where [email?protected];select @Times=Cai from dbo.['[email?protected]+N'] where [email?protected]';
	
	
	exec sp_executesql @SQL,N'@ID int,@Times int output',@ID,@Times output;
end


GO


--顶
create proc [dbo].[procDing]
(
@ID int,@key);
	
	declare @SQL nvarchar(MAX);
	set @SQL = N'update dbo.['[email?protected]+N'] set Ding=Ding+1 where [email?protected];select @Times=Ding from dbo.['[email?protected]+N'] where [email?protected]';
	
	
	exec sp_executesql @SQL,@Times output;
end

GO

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：拼字网 - 核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!