在TSQL中将IP转换为32位二进制文件 [英] Convert IP to 32-bit Binary in TSQL
问题描述
我有以下存储过程，可以在TSQL中将IP转换为32位二进制文件。它可以工作，但是速度非常慢（在40分钟内仅转换了8,400个）-可能是因为它使用了游标。是否有人提出建议和/或采用其他方法来提高性能？
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?
这里有一个示例：1.1.79.129转换为00000001.00000001.01001111.10000001
Here's an example: 1.1.79.129 is converted to 00000001.00000001.01001111.10000001
谢谢
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
推荐答案
它看起来像IPV4，所以我将继续这个假设。您还将转换为一些非常长的二进制文本表示形式，我建议您只使用 BINARY（4），而我的回答将假定那样。您可以根据需要将其转换为131个字符的文本表示形式，但是我不知道您为什么要这么做。
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.
我发布了一个类似的一般问题的答案几年前在这里： https://stackoverflow.com/a/1385701/109122
I posted an answer to a similar general question some years ago here: https://stackoverflow.com/a/1385701/109122
基本上显示了此功能：
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
这应该很快。
这篇关于在TSQL中将IP转换为32位二进制文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
