快速将珊瑚虫IP数据库转MS SQL2005的图文教程第1/2页

答案：首先需要准备： 


最新的珊瑚虫IP数据库 http://update.cz88.net/soft/qqwry.rar

Microsoft Office Access 2003

EiditPlus

MS SQL2005


1.然后打开珊瑚虫IP数据库自带的 ShowIP.exe，选择解压，另存一个TXT文件：


2.用EiditPlus打开这个文本拖动到最后几行，删除多余的东西（千万别试图用默认的文本编辑器打开，内存小的话你会死的很惨）：


3.打开Access（为什么不直接到SQL里面导入数据呢？因为会出现文本格式错误，所以先曲线救国吧）：


4.选择刚才解压出来的文本文件：


5.按下面操作，不做提示的直接下一步：






7.打开MS SQL2005，新建一个数据库，名字自己定，以下代码都使用[BasName]代替你新建数据库名称。

8.继续如下操作：




完成执行下面的存储过程：

复制代码 代码如下:

-- 建立IP转换到十进制方法  
USE [BasName]  
GO  
/****** 对象:  UserDefinedFunction [dbo].[X16ToDe]    脚本日期: 09/19/2007 13:56:15 ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  

-- =============================================  
-- Author:        <Author,,Name>  
-- Create date: <Create Date, ,>  
-- Description:    转换IP为十进制  
-- =============================================  
Create FUNCTION [dbo].[X16ToDe]  
(  
    @Old_IP nvarchar(15)  
)  
RETURNS numeric  
AS  
BEGIN  
    DECLARE  
        @CharIndex INT,  
        @CurrPoint INT,  
        @SingleValue NVARCHAR(5),  
        @Cache numeric  

    SET @CharIndex = 1  
    SET @CurrPoint = CHARINDEX('.',@Old_IP,@CharIndex)  
    SET @SingleValue = SUBSTRING(@Old_IP,@CharIndex,@CurrPoint - @CharIndex)  
    SET @Cache = cast(@SingleValue as numeric)*16777216  

    SET @CharIndex = @CurrPoint + 1  
    SET @CurrPoint = CHARINDEX('.',@Old_IP,@CharIndex)  
    SET @SingleValue = SUBSTRING(@Old_IP,@CharIndex,@CurrPoint - @CharIndex)  
    SET @Cache = @Cache + cast(@SingleValue as numeric)*65536  

    SET @CharIndex = @CurrPoint + 1  
    SET @CurrPoint = CHARINDEX('.',@Old_IP,@CharIndex)  
    SET @SingleValue = SUBSTRING(@Old_IP,@CharIndex,@CurrPoint - @CharIndex)  
    SET @Cache = @Cache + cast(@SingleValue as numeric)*256  

    SET @CharIndex = @CurrPoint + 1  
    SET @SingleValue = SUBSTRING(@Old_IP,@CharIndex,len(@Old_IP)- @CharIndex + 1)  
    SET @Cache = @Cache + cast(@SingleValue as numeric)  

    RETURN @Cache;  
END 

复制代码 代码如下:

-- 建立十进制新表  
USE [BasName]  
GO  
/****** 对象:  Table [dbo].[IP_Real]    脚本日期: 09/19/2007 14:01:31 ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
Create TABLE [dbo].[IP_Real](  
    [startip] [numeric](18, 0) NULL,  
    [endip] [numeric](18, 0) NULL,  
    [country] [nvarchar](50) NULL,  
    [local] [nvarchar](200) NULL  
) ON [PRIMARY] 



这一步你可以自己按照你的情况来做，我是为了加快数据库索引的速度，所以将IP全部转换为十进制，存到一个新表里面。 

-- 格式化省份 
复制代码 代码如下:

Update [BasName].[dbo].[IP]  
   SET [country] = replace([country],N'省',N'省 ') 

-- 删除CZ88.NET 
复制代码 代码如下:

Update [BasName].[dbo].[IP]  
   SET [country] = replace([country],N'CZ88.NET',N'') 

-- 将地区提出 
Update [BasName].[dbo].[IP] 
   SET [local] = SUBSTRING([country],CHARINDEX(' ',[country],1)+1,len([country])) 

-- 存为国家或省份 
Update [BasName].[dbo].[IP] 
   SET [country] = SUBSTRING([country],0,CHARINDEX(' ',[country],1)) 

-- 去处前后导空格 
Update [BasName].[dbo].[IP] 
   SET [country] = Rtrim(Ltrim([country])) 
      ,[local] = Rtrim(Ltrim([local])) 

-- 转换IP为十进制，并写入新表 
Insert INTO [BasName].[dbo].[IP_Real] 
           ([startip] 
           ,[endip] 
           ,[country] 
           ,[local]) 
Select dbo.X16ToDe([startip]) 
      ,dbo.X16ToDe([endip]) 
      ,[country] 
      ,[local] 
  FROM [BasName].[dbo].[IP] 
order by [startip] ASC 



最后测试一下看看： 

 程序代码 

-- 测试 
DECLARE @IPNumber numeric 
set @IPNumber = dbo.X16ToDe('219.140.31.91') 

Select [startip] 
      ,[endip] 
      ,[country] 
      ,[local] 
  FROM [BasName].[dbo].[IP_Real] 
Where [startip] <= @IPNumber and [endip] >= @IPNumber 



基本所有的转换最多3分钟完成，比自己写应用程序转换要快的多。 

上一个：字符串聚合函数(去除重复值)
下一个：SQL2005 学习笔记 公用表表达式(CTE)