Convert IP to 32-bit Binary in TSQL [duplicate]

问题:

This question already has answers here:

Datatype for storing ip address in SQL Server (11 answers)

Closed 2 years ago.

I have the following stored procedure that converts IPs to 32-bit binary in TSQL. It works but is EXTREMELY slow (has only converted 8,400 in 40 minutes) - probably because it uses cursors. Does anyone have suggestions and/or a different approach to improve performance?

Here's an example: 1.1.79.129 is converted to 00000001.00000001.01001111.10000001

Thanks

CREATE PROCEDURE [dbo].[sp_UpdateTableConvertIPToBinary]
AS

SET NOCOUNT ON

declare @IP nvarchar(255)

declare IPList cursor for
/*  
    Get IP address from CIDR Block where Binary has not been assigned
*/
select left(IP,charindex('/',IP)-1) as Block from MyDB.dbo.MyTable
WHERE IP IS NOT NULL AND [Binary] IS NULL 
ORDER BY Block
OPEN IPList
FETCH NEXT FROM IPList 
INTO @IP
WHILE @@FETCH_STATUS = 0
BEGIN

    begin                   
        declare @Octet as varchar(15)
        declare @Div as integer

        declare @Output as varchar(100)
        declare @n as integer

        declare @OriginalIP varchar(15)

        select @OriginalIP = @IP
        select @Div = '128'
        select @Output = ''

        select @n = 0

        WHILE @n < 4
            begin
                /*
                    Set @Octet = Class to the left of the first '.' in @IP
                    If no '.' in @IP, set @Octet = entire @IP (this will happen for the last Class)
                */      
                IF CHARINDEX('.',@IP) > 0 
                    begin
                        select @Octet = left(@IP,charindex('.',@IP)-1)                      
                    end
                else
                    begin
                        select @Octet = @IP
                    end

                /*                  
                    If @Octet >= 128, append 1 to @Output and subtract 128 from @Octet 
                    If not, append 0 to @Output 
                */
                if cast(@Octet as int) >= @Div
                    begin                       
                        select @Output = @Output + '1'                      
                        select @Octet = cast(@Octet as int) - @Div
                    end
                    else
                        begin
                            select @Output = @Output + '0'
                        end

                /*                  
                    If @Octet >= 64, append 1 to @Output and subtract 64 from @Octet 
                    If not, append 0 to @Output 
                */
                if cast(@Octet as int) >= (@Div/2)
                    begin
                        select @Output = @Output + '1'
                        select @Octet = cast(@Octet as int) - (@Div/2)
                    end
                    else
                        begin
                            select @Output = @Output + '0'
                        end

                /*                  
                    If @Octet >= 32, append 1 to @Output and subtract 32 from @Octet 
                    If not, append 0 to @Output 
                */
                if cast(@Octet as int) >= (@Div/4)
                    begin
                        select @Output = @Output + '1'
                        select @Octet = cast(@Octet as int) - (@Div/4)
                    end
                    else
                        begin
                            select @Output = @Output + '0'
                        end

                /*                  
                    If @Octet >= 16, append 1 to @Output and subtract 16 from @Octet 
                    If not, append 0 to @Output 
                */
                if cast(@Octet as int) >= (@Div/8)
                    begin
                        select @Output = @Output + '1'
                        select @Octet = cast(@Octet as int) - (@Div/8)
                    end
                    else
                        begin
                            select @Output = @Output + '0'
                        end

                /*                  
                    If @Octet >= 8, append 1 to @Output and subtract 8 from @Octet 
                    If not, append 0 to @Output 
                */
                if cast(@Octet as int) >= (@Div/16)
                    begin
                        select @Output = @Output + '1'
                        select @Octet = cast(@Octet as int) - (@Div/16)
                    end
                    else
                        begin
                            select @Output = @Output + '0'
                        end

                /*                  
                    If @Octet >= 4, append 1 to @Output and subtract 4 from @Octet 
                    If not, append 0 to @Output 
                */
                if cast(@Octet as int) >= (@Div/32)
                    begin
                        select @Output = @Output + '1'
                        select @Octet = cast(@Octet as int) - (@Div/32)
                    end
                    else
                        begin
                            select @Output = @Output + '0'
                        end 

                /*                  
                    If @Octet >= 2, append 1 to @Output and subtract 2 from @Octet 
                    If not, append 0 to @Output 
                */
                if cast(@Octet as int) >= (@Div/64)
                    begin
                        select @Output = @Output + '1'
                        select @Octet = cast(@Octet as int) - (@Div/64)
                    end
                    else
                        begin
                            select @Output = @Output + '0'
                        end

                /*                  
                    If @Octet >= 1, append 1 to @Output and subtract 1 from @Octet 
                    If not, append 0 to @Output 
                */
                if cast(@Octet as int) >= (@Div/128)
                    begin
                        select @Output = @Output + '1'
                        select @Octet = cast(@Octet as int) - (@Div/128)
                    end
                    else
                        begin
                            select @Output = @Output + '0'
                        end                     

                /* 
                    if @n < 3, append . to @Output
                */  
                if @n < 3 
                    begin
                        select @Output = @Output + '.'
                    end 

            /*
                Remove the Octet just converted to Binary from @IP and increment the counter
            */
            select @IP = right(@IP,len(@IP) - charindex('.',@IP))           
            select @n = @n+1            
        end 
            /*
                Update table, set Binary = @Output 
            */
            UPDATE MyDB.dbo.MyTable Set Binary = @Output WHERE left(IP,charindex('/',IP)-1) = @OriginalIP                                   
        end

  FETCH NEXT FROM IPList 
    INTO @IP
END
CLOSE IPList
DEALLOCATE IPList   

回答1:

Well it looks like IPV4, so I am going to go on that assumption. You are also converting to some really long binary text representation, I recommend that you just use BINARY(4) instead, and my answer will assume that. You can convert this your 131-character text representation if you want, but I don't know why you would want to.

I posted an answer to a similar general question some years ago here: https://stackoverflow.com/a/1385701/109122

That basically showed this function:

CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)

    SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    RETURN @bin
END
go

Today however, I would recommend making it an inline Table-Value Function instead for performance.

This is how you could do that:

CREATE FUNCTION dbo.itvfBinaryIPv4(@ip AS VARCHAR(15)) RETURNS TABLE
AS RETURN (
    SELECT CAST(
               CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
            +  CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
            +  CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
            +  CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))
                AS BINARY(4)) As bin
        )
go

And this is how you could use that to perform your conversion and update without a cursor:

;
WITH cte As 
(
    SELECT      *
    FROM        MyDB.dbo.MyTable
    OUTER APPLY dbo.itvfBinaryIPv4(left(IP,charindex('/',IP)-1)) 
)
UPDATE  cte
Set     Binary = bin
WHERE   IP IS NOT NULL 
  AND   [Binary] IS NULL 

This should be very fast.

回答2:

For those who are looking for one time IP4 to bigint conversion rather then performance issues with existing stored procedures. Below is the sample inline code that makes conversion

SELECT CONVERT(bigint, LEFT([IP], CHARINDEX('.', [IP]) - 1)) + 
   CONVERT(bigint, SUBSTRING([IP],
                             CHARINDEX('.', [IP]) + 1,
                             CHARINDEX('.', [IP], CHARINDEX('.', [IP]) + 1)
                                - CHARINDEX('.', [IP]) - 1)) * 256 +
   CONVERT(bigint, SUBSTRING([IP],
                             CHARINDEX('.', [IP], CHARINDEX('.', [IP]) + 1) + 1,
                             CHARINDEX('.', [IP], CHARINDEX('.', [IP], CHARINDEX('.', [IP]) + 1) + 1)
                                - CHARINDEX('.', [IP], CHARINDEX('.', [IP]) + 1) - 1)) * 65536 +
   CONVERT(bigint, RIGHT([IP],
                   LEN([IP]) - 1
                        - CHARINDEX('.', [IP], CHARINDEX('.', [IP], CHARINDEX('.', [IP]) + 1) + 1) + 1)) * 16777216
   AS Value
FROM IPAddresses
-- additional check if IPv6 addresses are in the table
WHERE CHARINDEX(':', [IP]) = 0