1、SQL数据库设计【ipweizhi】
IP1,IP起始段
ip2,iP终止段
weizhi,地理位置
可以下载纯真IP数据库123.txt,导出文本格式后,再导入SQL中
update [123] set ip1=SUBSTRING(col001, 0 16)
update [123] set ip2=SUBSTRING(col001, 17 16)
update [123] set weizhi=SUBSTRING(col001, 33, len(col001)-32)
update [123] set weizhi=REPLACE(weizhi,'CZ88.NET','')
2、自建SQL函数
create function dbo.fn_IP(@ip varchar(15))
returns bigint
begin
return parsename(@ip,4)*cast(16777216 as bigint)+parsename(@ip,3)*65536+parsename(@ip,2)*256+parsename(@ip,1)
end
3、SQL语句调用可返回符合要求的IP
select * from ipweizhi where dbo.fn_IP('49.221.55.25') between dbo.fn_IP(ip1) and dbo.fn_IP(ip2)
此语句执行起来效率会变慢
4、为提高查询效率,可做如下设置
(1)合理设置索引。
(2)在数据库中增加列,直接利用函数进行转换
update ipweizhi set ipqi=dbo.fn_IP(ip1),ipzhong=dbo.fn_IP(ip2)
(3)查询位置
select * from ipweizhi where dbo.fn_IP('183.174.65.19') between ipqi and ipzhong --使用函数时经测试服务器CPU占用很高,经常达到100%
可将转换函数放在外部计算后再变为以下类似的格式可有效解决
select * from ipweizhi where 1234223322 between ipqi and ipzhong
(4)自建外部程序函数
' ============================================
' IP地址转换成整数
' ============================================
Function IPToInt(IP)
Dim IPArray, i
IPArray = Split(IP, ".", -1)
FOr i = 0 to 3
If Not IsNumeric(IPArray(i)) Then IPArray(i) = 0
If CInt(IPArray(i)) < 0 Then IPArray(i) = Abs(CInt(IPArray(i)))
If CInt(IPArray(i)) > 255 Then IPArray(i) = 255
Next
IPToInt = (CInt(IPArray(0))*256*256*256) + (CInt(IPArray(1))*256*256) + (CInt(IPArray(2))*256) + CInt(IPArray(3))
End Function
5、利用存贮过程将原来保存在数据库中,只有IP地址,没有地理位置的数据记录,更新地理位置
(1)update zhi_rui_v_record set hostad=dbo.fn_IP(hostip) where hostad='来源'
(2)编写存贮过程,根据期间进行升级
ALTER Proc EC_gengxiweizhi
AS
BEGIN
declare @temp1 float
declare @id int
declare @temp2 varchar(200)
set @temp2 = ''
DECLARE c_bmgsh CURSOR FOR
Select [id],hostad from zhi_rui_v_record where id<1000 and id>0 order by id desc
OPEN c_bmgsh
FETCH NEXT FROM c_bmgsh INTO @id,@temp1
WHILE @@FETCH_STATUS = 0
BEGIN
Select @temp2=weizhi from ipweizhi where @temp1 between ipqi and ipzhong
update zhi_rui_v_record set hostad=@temp2 where [id]=@id
FETCH NEXT FROM c_bmgsh INTO @id,@temp1
END
CLOSE c_bmgsh
deallocate c_bmgsh
END