SQL Server에 IP 주소를 저장하려면 어떤 데이터 유형을 선택해야합니까?
올바른 데이터 유형을 선택하면 IP 주소로 쉽게 필터링 할 수 있습니까?
답변
IPv4를 저장하는 기술적으로 올바른 방법은 binary (4)입니다. 왜냐하면 그것이 실제로있는 것이기 때문입니다 (아니요, INT32 / INT (4)도 아닙니다. 우리 모두가 알고 사랑하는 숫자 텍스트 형식 (255.255.255.255)). 바이너리 콘텐츠의 표시 변환).
이렇게하면 함수가 텍스트 표시 형식으로 또는 텍스트 표시 형식으로 변환되기를 원할 것입니다.
텍스트 표시 형식을 바이너리로 변환하는 방법은 다음과 같습니다.
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바이너리를 다시 텍스트 표시 형식으로 변환하는 방법은 다음과 같습니다.
CREATE FUNCTION dbo.fnDisplayIPv4(@ip AS BINARY(4)) RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @str AS VARCHAR(15)
SELECT @str = CAST( CAST( SUBSTRING( @ip, 1, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
+ CAST( CAST( SUBSTRING( @ip, 2, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
+ CAST( CAST( SUBSTRING( @ip, 3, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
+ CAST( CAST( SUBSTRING( @ip, 4, 1) AS INTEGER) AS VARCHAR(3) );
RETURN @str
END;
go사용 방법에 대한 데모는 다음과 같습니다.
SELECT dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go
SELECT dbo.fnDisplayIPv4( 0xC04144C9 )
-- should return '192.65.68.201'
go마지막으로, 조회 및 비교를 수행 할 때 인덱스를 활용하려면 항상 이진 형식을 사용하십시오.
최신 정보:
SQL Server에서 스칼라 UDF의 고유 한 성능 문제를 해결하기위한 한 가지 방법을 추가하고 싶었지만 함수의 코드 재사용을 유지하는 대신 iTVF (인라인 테이블 반환 함수)를 사용하는 것입니다. 위의 첫 번째 함수 (문자열에서 바이너리로)를 iTVF로 다시 작성할 수있는 방법은 다음과 같습니다.
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예를 들면 다음과 같습니다.
SELECT bin FROM dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
goINSERT에서 사용하는 방법은 다음과 같습니다.
INSERT INTo myIpTable
SELECT {other_column_values,...},
(SELECT bin FROM dbo.itvfBinaryIPv4('192.65.68.201'))답변
varchar를 사용할 수 있습니다. IPv4의 길이는 고정적이지만 IPv6의 길이는 매우 가변적 일 수 있습니다.
바이너리로 저장할 이유가없는 한 문자열 (텍스트) 유형을 사용하십시오.
답변
다음은 varchar 형식의 IPV4 또는 IPv6를 binary (16) 및 그 반대로 변환하는 코드입니다. 이것은 제가 생각할 수있는 가장 작은 형태입니다. 인덱싱이 잘되어야하며 서브넷을 필터링하는 비교적 쉬운 방법을 제공해야합니다. SQL Server 2005 이상이 필요합니다. 완전히 방탄인지 확실하지 않습니다. 도움이 되었기를 바랍니다.
-- SELECT dbo.fn_ConvertIpAddressToBinary('2002:1ff:6c2::1ff:6c2')
-- SELECT dbo.fn_ConvertIpAddressToBinary('10.4.46.2')
-- SELECT dbo.fn_ConvertIpAddressToBinary('bogus')
ALTER FUNCTION dbo.fn_ConvertIpAddressToBinary
(
@ipAddress VARCHAR(39)
)
RETURNS BINARY(16) AS
BEGIN
DECLARE
@bytes BINARY(16), @vbytes VARBINARY(16), @vbzone VARBINARY(2)
, @colIndex TINYINT, @prevColIndex TINYINT, @parts TINYINT, @limit TINYINT
, @delim CHAR(1), @token VARCHAR(4), @zone VARCHAR(4)
SELECT
@delim = '.'
, @prevColIndex = 0
, @limit = 4
, @vbytes = 0x
, @parts = 0
, @colIndex = CHARINDEX(@delim, @ipAddress)
IF @colIndex = 0
BEGIN
SELECT
@delim = ':'
, @limit = 8
, @colIndex = CHARINDEX(@delim, @ipAddress)
WHILE @colIndex > 0
SELECT
@parts = @parts + 1
, @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1)
SET @colIndex = CHARINDEX(@delim, @ipAddress)
IF @colIndex = 0
RETURN NULL
END
SET @ipAddress = @ipAddress + @delim
WHILE @colIndex > 0
BEGIN
SET @token = SUBSTRING(@ipAddress, @prevColIndex + 1, @Colindex - @prevColIndex - 1)
IF @delim = ':'
BEGIN
SET @zone = RIGHT('0000' + @token, 4)
SELECT
@vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(2)')
, @vbytes = @vbytes + @vbzone
IF @token = ''
WHILE @parts + 1 < @limit
SELECT
@vbytes = @vbytes + @vbzone
, @parts = @parts + 1
END
ELSE
BEGIN
SET @zone = SUBSTRING('' + master.sys.fn_varbintohexstr(CAST(@token AS TINYINT)), 3, 2)
SELECT
@vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(1)')
, @vbytes = @vbytes + @vbzone
END
SELECT
@prevColIndex = @colIndex
, @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1)
END
SET @bytes =
CASE @delim
WHEN ':' THEN @vbytes
ELSE 0x000000000000000000000000 + @vbytes
END
RETURN @bytes
END-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x200201FF06C200000000000001FF06C2)
-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x0000000000000000000000000A0118FF)
ALTER FUNCTION [dbo].[fn_ConvertBinaryToIpAddress]
(
@bytes BINARY(16)
)
RETURNS VARCHAR(39) AS
BEGIN
DECLARE
@part VARBINARY(2)
, @colIndex TINYINT
, @ipAddress VARCHAR(39)
SET @ipAddress = ''
IF SUBSTRING(@bytes, 1, 12) = 0x000000000000000000000000
BEGIN
SET @colIndex = 13
WHILE @colIndex <= 16
SELECT
@part = SUBSTRING(@bytes, @colIndex, 1)
, @ipAddress = @ipAddress
+ CAST(CAST(@part AS TINYINT) AS VARCHAR(3))
+ CASE @colIndex WHEN 16 THEN '' ELSE '.' END
, @colIndex = @colIndex + 1
IF @ipAddress = '0.0.0.1'
SET @ipAddress = '::1'
END
ELSE
BEGIN
SET @colIndex = 1
WHILE @colIndex <= 16
BEGIN
SET @part = SUBSTRING(@bytes, @colIndex, 2)
SELECT
@ipAddress = @ipAddress
+ CAST('' as xml).value('xs:hexBinary(sql:variable("@part") )', 'varchar(4)')
+ CASE @colIndex WHEN 15 THEN '' ELSE ':' END
, @colIndex = @colIndex + 2
END
END
RETURN @ipAddress
END 답변
IPv4및을 모두 처리하고 싶기 때문에 및 다음 함수를 IPv6사용 하여 IP 주소 표시를 바이트 및 그 반대로 변환합니다 .VARBINARY(16)SQL CLRtext
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlBytes GetIPAddressBytesFromString (SqlString value)
{
IPAddress IP;
if (IPAddress.TryParse(value.Value, out IP))
{
return new SqlBytes(IP.GetAddressBytes());
}
else
{
return new SqlBytes();
}
}
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlString GetIPAddressStringFromBytes(SqlBytes value)
{
string output;
if (value.IsNull)
{
output = "";
}
else
{
IPAddress IP = new IPAddress(value.Value);
output = IP.ToString();
}
return new SqlString(output);
}답변
.NET을 사용하는 사람들은 IPAddress 클래스를 사용하여 IPv4 / IPv6 문자열을 구문 분석하고 VARBINARY(16). 동일한 클래스를 사용하여 byte[]문자열 로 변환 할 수 있습니다 . VARBINARYin SQL 을 변환하려면 다음 을 수행하십시오.
--SELECT
-- dbo.varbinaryToIpString(CAST(0x7F000001 AS VARBINARY(4))) IPv4,
-- dbo.varbinaryToIpString(CAST(0x20010DB885A3000000008A2E03707334 AS VARBINARY(16))) IPv6
--ALTER
CREATE
FUNCTION dbo.varbinaryToIpString
(
@varbinaryValue VARBINARY(16)
)
RETURNS VARCHAR(39)
AS
BEGIN
IF @varbinaryValue IS NULL
RETURN NULL
IF DATALENGTH(@varbinaryValue) = 4
BEGIN
RETURN
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 1, 1))) + '.' +
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 2, 1))) + '.' +
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 3, 1))) + '.' +
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 4, 1)))
END
IF DATALENGTH(@varbinaryValue) = 16
BEGIN
RETURN
sys.fn_varbintohexsubstring(0, @varbinaryValue, 1, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 3, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 5, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 7, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 9, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 11, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 13, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 15, 2)
END
RETURN 'Invalid'
END답변
sys.dm_exec_connectionsSQL Server 2005 SP1 이후에 varchar (48)를 사용합니다. 특히 당신이 당신의 가치와 비교하여 그것을 사용하고 싶다면 나에게 충분하게 들립니다.
현실적으로, 당신은 아직 IPv6를 주류로 보지 않을 것이기 때문에 저는 4 개의 tinyint 경로를 선호합니다. 즉, 나는 사용해야하기 때문에 varchar (48)을 사용하고 있습니다.sys.dm_exec_connections …
그렇지 않으면. Mark Redman의 답변은 이전 SO 토론 질문을 언급합니다 .
답변
감사합니다 RBarry. IP 블록 할당 시스템을 구성하고 바이너리로 저장하는 것이 유일한 방법입니다.
IP 블록의 CIDR 표현 (예 : 192.168.1.0/24)을 varchar 필드에 저장하고 2 개의 계산 된 필드를 사용하여 블록의 시작과 끝의 이진 형식을 유지합니다. 거기에서 빠른 쿼리를 실행하여 주어진 블록이 이미 할당되었거나 할당 가능한지 확인할 수 있습니다.
다음과 같이 끝 IP 주소를 계산하도록 함수를 수정했습니다.
CREATE FUNCTION dbo.fnDisplayIPv4End(@block AS VARCHAR(18)) RETURNS BINARY(4)
AS
BEGIN
DECLARE @bin AS BINARY(4)
DECLARE @ip AS VARCHAR(15)
DECLARE @size AS INT
SELECT @ip = Left(@block, Len(@block)-3)
SELECT @size = Right(@block, 2)
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))
SELECT @bin = CAST(@bin + POWER(2, 32-@size) AS BINARY(4))
RETURN @bin
END;
go