限制网站频繁访问

执行存储过程 procAccessIP(@ip ,@seconds,@maxTimes) 返回 0 或 1 假如设置 procAccessIP('127.0.0.1' ,5,5) 表示 5秒钟内 如果访问此存储过程超出5次，则返回1，否则返回0 无 CREATE TABLE [dbo].[Zz_AccessIP]([IP] [nvarchar](50) NOT NULL,[FirstDateTim

执行存储过程 procAccessIP(@ip ,@seconds,@maxTimes) 返回 0 或 1 
假如设置  procAccessIP('127.0.0.1' ,5,5)    表示 5秒钟内 如果访问此存储过程超出5次，则返回1，否则返回0

CREATE TABLE [dbo].[Zz_AccessIP](
	[IP] [nvarchar](50) NOT NULL,
	[FirstDateTime] [datetime] NOT NULL,
	[LastDateTime] [datetime] NOT NULL,
	[Times] [int] NOT NULL,
 CONSTRAINT [PK_Zz_AccessIP] PRIMARY KEY CLUSTERED 
(
	[IP] 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].[Zz_AccessIP] ADD  CONSTRAINT [DF_Zz_AccessIP_FirstDateTime]  DEFAULT (getdate()) FOR [FirstDateTime]
GO

ALTER TABLE [dbo].[Zz_AccessIP] ADD  CONSTRAINT [DF_Zz_AccessIP_LastDateTime]  DEFAULT (getdate()) FOR [LastDateTime]
GO

ALTER TABLE [dbo].[Zz_AccessIP] ADD  CONSTRAINT [DF_Zz_AccessIP_Times]  DEFAULT ((0)) FOR [Times]
GO

--默认5秒钟内访问超出5次，返回1，否则返回0

CREATE proc [dbo].[procAccessIP]
(
@ip nvarchar(50),  
@seconds int = 5, --多长时间内 默认5秒钟内
@maxTimes int = 5 --限制最多访问的次数，默认 5次
)

as
begin

	--删除5秒钟内没有访问过的
	delete from [Zz_AccessIP] where DateDiff(second,LastDateTime,GetDate()) > @seconds and IP = @ip;
	
	--删除5秒钟内访问次数少于5 的	
	delete from [Zz_AccessIP] where DateDiff(second,FirstDateTime,GetDate()) > @seconds and Times < @maxTimes and IP = @ip; 

	
	--插入IP 或 更新 某IP 访问的次数
	declare @existsIp int;
	select @existsIp = count(IP) from [Zz_AccessIP] where IP= @ip;
		
	if @existsIp = 0 
		insert into [Zz_AccessIP](IP) values(@ip);
	else
		update [Zz_AccessIP] set Times = Times +1,LastDateTime = GETDATE() where IP = @ip;	
		
	
	
	--获取某IP 5 秒钟内访问的次数 是否 大于 5
	declare @count int;
	select @count = COUNT(IP) from [Zz_AccessIP] where IP = @ip and DateDiff(second,LastDateTime,GetDate()) < @seconds and Times > @maxTimes ;
	return @count;	
end


GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[procAccessIP]
		@ip = N'127.0.0.1',
		@seconds = 5,
		@maxTimes = 5

SELECT	'Return Value' = @return_value

GO