高访问量的评论系统数据库存储过程架构
- 大家讲道理Original
- 2016-11-10 11:16:571071Durchsuche
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]=@Key and [TableName]=@tableName)
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].['+@tableName+'](
[ID] [int] IDENTITY(1,1) NOT NULL,
[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_'+@tableName+'] 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]'
EXEC(@CreateSQL);
--创建索引 ID DESC
EXEC('
CREATE UNIQUE NONCLUSTERED INDEX [IX_'+@tableName+'_ID_DESC] ON [dbo].['+@tableName+']
(
[ID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]');
--创建索引 Ding DESC
EXEC('
CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_Ding_DESC] ON [dbo].['+@tableName+']
(
[Ding] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]');
--创建索引 SourceID DESC
EXEC('
CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_SourceID_DESC] ON [dbo].['+@tableName+']
(
[SourceID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]');
--创建索引 Lou DESC
EXEC('
CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_Lou_DESC] ON [dbo].['+@tableName+']
(
[Lou] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]')
--创建默认值
EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_ParentID] DEFAULT ((0)) FOR [ParentID]');
EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Datetime] DEFAULT (getdate()) FOR [Datetime]');
EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_BeFiltered] DEFAULT ((0)) FOR [BeFiltered]');
EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Disable] DEFAULT ((0)) FOR [Disable]');
EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Lou] DEFAULT ((1)) FOR [Lou]');
EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Ding] DEFAULT ((0)) FOR [Ding]');
EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_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.['+@tableName+N'] where SourceID=@SourceID';
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.['+@tableName+N'](ParentID,SourceID,NickName,Content,IP,City,BeFiltered,[Disable],[Lou])
values (@ParentID,@SourceID,@NickName,@Content,@IP,@City,@BeFiltered,@Disable,@Lou);select @InsertedID = SCOPE_IDENTITY();';
exec sp_executesql @InsertSQL,
N'@ParentID int,@SourceID int,@NickName nvarchar(20),@Content nvarchar(300),@IP nvarchar(30),@City nvarchar(30),@BeFiltered bit,@Disable bit,@Lou int,@InsertedID int output',
@ParentID,
@SourceID,
@NickName,
@Content,
@IP,
@City,
@BeFiltered,
@Disable,
@Lou,
@InsertedID output;
end
GO
--获得最新评论存储过程
CREATE proc [dbo].[procGetNewComments]
(
@SourceID int,
@Key nvarchar(50),
@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.['+@tableName+N'] where SourceID = @SourceID';
exec sp_executesql @SQL,
N'@SourceID int,@Rc int output',
@SourceID,
@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))+' '+@Fields + N' from dbo.['+@tableName+N'] where SourceID=@SourceID 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 '+@Fields + N' from dbo.['+@tableName+N'] where Lou>=@StartLou and Lou<=@EndLou and SourceID = @SourceID order by Lou desc';
exec sp_executesql @SQL,
N'@SourceID int,@StartLou int,@EndLou int',
@SourceID,@StartLou,@EndLou;
end
end
GO
--踩
Create proc [dbo].[procCai]
(
@ID int,
@SourceID 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.['+@tableName+N'] set Cai=Cai+1 where ID=@ID;select @Times=Cai from dbo.['+@tableName+N'] where ID=@ID';
exec sp_executesql @SQL,
N'@ID int,@Times int output',
@ID,
@Times output;
end
GO
--顶
create proc [dbo].[procDing]
(
@ID int,
@SourceID 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.['+@tableName+N'] set Ding=Ding+1 where ID=@ID;select @Times=Ding from dbo.['+@tableName+N'] where ID=@ID';
exec sp_executesql @SQL,
N'@ID int,@Times int output',
@ID,
@Times output;
end
GOStellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：SQL xp_cmdshellNächster Artikel：高效的MySQL的批插入 BULK INSERT