programing

SQL Server에 IP 주소를 저장하기 위한 데이터 유형

sourcejob 2023. 4. 9. 21:27
반응형

SQL Server에 IP 주소를 저장하기 위한 데이터 유형

SQL Server에 IP 주소를 저장하려면 어떤 데이터 유형을 선택해야 합니까?

올바른 데이터 유형을 선택하면 IP 주소로 필터링하는 것이 쉬워집니까?

IPv4 를 보존하는 기술적으로 올바른 방법은, 바이너리(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(Inline table-value function)를 사용하는 것입니다.위의 첫 번째 함수(문자열에서 바이너리로)를 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
go

INSERT에서 사용하는 방법은 다음과 같습니다.

INSERT INTo myIpTable
SELECT {other_column_values,...},
       (SELECT bin FROM dbo.itvfBinaryIPv4('192.65.68.201'))

바르샤를 사용할 수 있습니다.IPv4 의 길이는 정적이지만, IPv6 의 길이는 매우 가변적인 경우가 있습니다.

바이너리로 저장할 이유가 없는 한 문자열(텍스트형)을 사용합니다.

다음은 varchar 형식의 IPV4 또는 IPv6 중 하나를 바이너리(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 , , 을 사용하고 .VARBINARY(16) 그 에 있는 및음음 andSQL CLR" " " 를 textIP " " ( " " ) " :

[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);
}

사용하는 . 는, 해 IPv4해, , IPv4/IPv6, IPAddress 로서 할 수 .VARBINARY(16)할 수 .byte[]스트링에 접속합니다.「」를 .VARBINARYSQL:

--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_connections는 SQL SP1에 varchar합니다.SQL Server 2005 SP1의 varchar(48)는 varchar(48)를 사용합니다.특히 당신의 가치에 비해 그것을 사용하고 싶다면, 나에게도 충분히 좋을 것 같습니다.

현실적으로 IPv6는 당분간 주류로 인식되지 않을 것이기 때문에 4개의 작은 루트를 선호합니다.Varchar(48에 Varchar(48)를 sys.dm_exec_connections

그렇지않으면.Mark Redman의 답변은 이전 SO에 대해 언급하고 있습니다. 토론하다 질문합니다.

아래 답변은 M의 답변에 근거하고 있습니다. 이 질문에 대해 턴아웃과 제리 버클러에게 다음과 같은 개선점을 제시합니다.

  • 되어 있지 않은 기능 「 」 )의 해 주세요.sys.fn_varbintohexsubstring,fn_varbintohexstr가 있는 경우CONVERT()바이너리 스타일의 경우
  • "핵"(XML "핵CAST('' as xml).value('xs:hexBinary())가 있는 경우CONVERT()바이너리 스타일의 경우
  • Jerry Birchler 구현 오류 수정fn_ConvertIpAddressToBinary(C가 지적한 바와 같이).플록)
  • 마이너 구문설탕 추가

이 코드는 SQL Server 2014 및 SQL Server 2016에서 테스트되었습니다(마지막 테스트 사례 참조).

IPAddressVarbinaryToString

4 바이트 값을 IPV4로, 16 바이트 값을 IPV6 문자열 표현으로 변환합니다.이 함수는 주소를 단축하지 않습니다.

ALTER FUNCTION dbo.IPAddressVarbinaryToString
(
    @varbinaryValue VARBINARY( 16 )
)
RETURNS VARCHAR(39)
AS
BEGIN
    IF @varbinaryValue IS NULL
        RETURN NULL;
    ELSE IF DATALENGTH( @varbinaryValue ) = 4
        RETURN 
            CONVERT( VARCHAR(3), CONVERT(TINYINT, SUBSTRING( @varbinaryValue, 1, 1 ))) + '.' +
            CONVERT( VARCHAR(3), CONVERT(TINYINT, SUBSTRING( @varbinaryValue, 2, 1 ))) + '.' +
            CONVERT( VARCHAR(3), CONVERT(TINYINT, SUBSTRING( @varbinaryValue, 3, 1 ))) + '.' +
            CONVERT( VARCHAR(3), CONVERT(TINYINT, SUBSTRING( @varbinaryValue, 4, 1 )));
    ELSE IF DATALENGTH( @varbinaryValue ) = 16
        RETURN 
            CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue,  1, 2 ), 2 ) + ':' +
            CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue,  3, 2 ), 2 ) + ':' +
            CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue,  5, 2 ), 2 ) + ':' +
            CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue,  7, 2 ), 2 ) + ':' +
            CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue,  9, 2 ), 2 ) + ':' +
            CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue, 11, 2 ), 2 ) + ':' +
            CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue, 13, 2 ), 2 ) + ':' +
            CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue, 15, 2 ), 2 );

    RETURN 'Invalid';
END

테스트 케이스:

SELECT dbo.IPAddressVarbinaryToString(0x00000000000000000000000000000000) -- 0000:0000:0000:0000:0000:0000:0000:0000 (no address shortening)
SELECT dbo.IPAddressVarbinaryToString(0x00010002000300400500060070000089) -- 0001:0002:0003:0040:0500:0600:7000:0089
SELECT dbo.IPAddressVarbinaryToString(0xC0A80148) -- 255.168.1.72
SELECT dbo.IPAddressVarbinaryToString(0x7F000001) -- 127.0.0.1 (no address shortening)
SELECT dbo.IPAddressVarbinaryToString(NULL) -- NULL

IPAddressStringToVarbinary

IPV4 및 IPV6 문자열 표현을 각각4 바이트 및 16 바이트의 바이너리 값으로 변환합니다.이 함수는 대부분의 (일반적으로 사용되는) 속기 주소 표현(예: 127...1 및 2001:db8:1319:370:7348)을 해석할 수 있습니다.이 함수가 항상 16바이트 이진수 값을 반환하도록 강제하려면 함수의 끝에 0s 연결을 유도하는 주석 해제해야 합니다.

ALTER FUNCTION [dbo].[IPAddressStringToVarbinary]
(
    @IPAddress VARCHAR( 39 )
)
RETURNS VARBINARY(16) AS
BEGIN

IF @ipAddress IS NULL
    RETURN NULL;

DECLARE @bytes VARBINARY(16), @token VARCHAR(4),
    @vbytes VARBINARY(16) = 0x, @vbzone VARBINARY(2),
    @tIPAddress VARCHAR( 40 ),
    @colIndex TINYINT,
    @delim CHAR(1) = '.',
    @prevColIndex TINYINT = 0,
    @parts TINYINT = 0, @limit TINYINT = 4;

-- Get position if IPV4 delimiter
SET @colIndex = CHARINDEX( @delim, @ipAddress );

-- If not IPV4, then assume IPV6
IF @colIndex = 0
BEGIN
    SELECT @delim = ':', @limit = 8, @colIndex = CHARINDEX( @delim, @ipAddress );

    -- Get number of parts (delimiters)
    WHILE @colIndex > 0
        SELECT @parts += 1, @colIndex = CHARINDEX( @delim, @ipAddress, @colIndex + 1 );

    SET @colIndex = CHARINDEX( @delim, @ipAddress );

    IF @colIndex = 0
        RETURN NULL;
END

-- Add trailing delimiter (need new variable of larger size)
SET @tIPAddress = @IPAddress + @delim;

WHILE @colIndex > 0
BEGIN
    SET @token = SUBSTRING( @tIPAddress, @prevColIndex + 1, @Colindex - @prevColIndex - 1 );

    IF @delim = ':'
    BEGIN
        SELECT @vbzone = CONVERT( VARBINARY(2), RIGHT( '0000' + @token, 4 ), 2 ), @vbytes += @vbzone;

        -- Handles consecutive sections of zeros representation rule (i.e. ::)(https://en.wikipedia.org/wiki/IPv6#Address_representation)
        IF @token = ''
            WHILE @parts + 1 < @limit
                SELECT @vbytes += @vbzone, @parts += 1;
    END
    ELSE
    BEGIN
        SELECT @vbzone = CONVERT( VARBINARY(1), CONVERT( TINYINT, @token )), @vbytes += @vbzone
    END

    SELECT @prevColIndex = @colIndex, @colIndex = CHARINDEX( @delim, @tIPAddress, @colIndex + 1 ) 
END

SET @bytes =
    CASE @delim
        WHEN ':' THEN @vbytes
        ELSE /*0x000000000000000000000000 +*/ @vbytes -- Return IPV4 addresses as 4 byte binary (uncomment leading 0s section to force 16 byte binary)
    END 

RETURN @bytes

END

테스트 케이스

유효한 케이스

SELECT dbo.IPAddressStringToVarbinary( '0000:0000:0000:0000:0000:0000:0000:0001' ) -- 0x0000000000000000000000000001 (check bug fix)
SELECT dbo.IPAddressStringToVarbinary( '0001:0002:0003:0040:0500:0600:7000:0089' ) -- 0x00010002000300400500060070000089
SELECT dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3:1319::370:7348' )     -- 0x20010DB885A308D31319000003707348 (check short hand)
SELECT dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3:1319:0000:370:7348' ) -- 0x20010DB885A308D31319000003707348
SELECT dbo.IPAddressStringToVarbinary( '192.168.1.72' ) -- 0xC0A80148
SELECT dbo.IPAddressStringToVarbinary( '127...1' ) -- 0x7F000001 (check short hand)
SELECT dbo.IPAddressStringToVarbinary( NULL ) -- NULL
SELECT dbo.IPAddressStringToVarbinary( '' ) -- NULL
-- Check that conversions return original address
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '0001:0002:0003:0040:0500:0600:7000:0089' )) -- '0001:0002:0003:0040:0500:0600:7000:0089' 
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '127...1' )) -- 127.0.0.1
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '192.168.1.72' )) -- 192.168.1.72
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3:1319::370:7348' ))     -- 2001:0db8:85a3:08d3:1319:0000:0370:7348
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3:1314:0000:370:7348' )) -- 2001:0db8:85a3:08d3:1319:0000:0370:7348
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3::370:7348' )) -- 2001:0DB8:85A3:08D3:0000:0000:0370:7348
-- This is technically an invalid IPV6 (according to Wikipedia) but it parses correctly
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8::1319::370:7348' )) -- 2001:0DB8:0000:0000:1319:0000:0370:7348

무효 케이스

SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8::1319::7348' )) -- 2001:0DB8:0000:0000:0000:1319:0000:7348 (ambiguous address)
SELECT dbo.IPAddressStringToVarbinary( '127.1' ) -- 127.0.0.1 (not supported short-hand)
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '127.1' )) -- 127.0.0.1 (not supported short-hand)
SELECT dbo.IPAddressStringToVarbinary( '0300.0000.0002.0353' ) -- octal byte values
SELECT dbo.IPAddressStringToVarbinary( '0xC0.0x00.0x02.0xEB' ) -- hex values
SELECT dbo.IPAddressStringToVarbinary( 'C0.00.02.EB' ) -- hex values

고마워요 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

IPAddress에는 보통 플레인오래된 VARCHAR 필터링을 사용합니다.

IP 주소의 범위를 필터링 하려면 , 4 개의 정수로 분할합니다.

나는 샌드록의 기능을 좋아한다.그런데 dbo.fn_ConvertIpAddressToBinary 코드에서 오류가 발견되었습니다.@delim을 @ipAddress VARCHAR(39)의 착신 파라미터가 너무 작습니다.

SET @ipAddress = @ipAddress + @delim

40까지 늘릴 수 있습니다.또는 더 큰 변수를 사용하여 내부적으로 사용하는 것이 좋습니다.그래야 마지막 한 쌍이 큰 수에서 지는 일이 없어.

SELECT dbo.fn_ConvertIpAddressToBinary('ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff')

특정 서브넷 내에 어떤 IP가 있는지 알아내야 하는 많은 작업을 수행합니다.가장 간단하고 신뢰할 수 있는 방법은 다음과 같습니다.

  1. 각 테이블에 IPInterger(bigint)라는 필드를 추가합니다(잘못된 경우 IP= '0.0.0.0' 설정).
  2. 작은 테이블의 경우 변경 시 IPInterger를 업데이트하는 트리거를 사용합니다.
  3. 큰 테이블의 경우 SPROC를 사용하여 IPInteger를 새로 고칩니다.
    ALTER FUNCTION [dbo].[IP_To_INT ]
    ( 
        @IP CHAR(15) 
    ) 
    RETURNS BIGINT 
    AS 
    BEGIN 
        DECLARE @IntAns BIGINT, 
            @block1 BIGINT, 
            @block2 BIGINT, 
            @block3 BIGINT, 
            @block4 BIGINT, 
            @base BIGINT 
     
        SELECT 
            @block1 = CONVERT(BIGINT, PARSENAME(@IP, 4)), 
            @block2 = CONVERT(BIGINT, PARSENAME(@IP, 3)), 
            @block3 = CONVERT(BIGINT, PARSENAME(@IP, 2)), 
            @block4 = CONVERT(BIGINT, PARSENAME(@IP, 1)) 
     
        IF (@block1 BETWEEN 0 AND 255) 
            AND (@block2 BETWEEN 0 AND 255) 
            AND (@block3 BETWEEN 0 AND 255) 
            AND (@block4 BETWEEN 0 AND 255) 
        BEGIN      
            SET @base = CONVERT(BIGINT, @block1 * 16777216)
            SET @IntAns = @base +  
                (@block2 * 65536) +  
                (@block3 * 256) + 
                (@block4) 
        END 
        ELSE 
            SET @IntAns = -1 
        RETURN @IntAns 
    END

사용하고 있다varchar(15)지금까지는 모든 게 잘 되고 있어요.삽입, 업데이트, 선택.저는 아직 개발 작업을 많이 하지 않았지만 IP 주소를 가진 앱을 시작했습니다.

select 스테이트먼트는 다음과 같습니다.

select * From dbo.Server 
where  [IP] = ('132.46.151.181')
Go

언급URL : https://stackoverflow.com/questions/1385552/datatype-for-storing-ip-address-in-sql-server

반응형

'programing' 카테고리의 다른 글

WPF: 스크롤바가 있는 항목 제어(ScrollViewer)  (0) 2023.04.09
WPF 응용 프로그램에서 응용 프로그램 디렉터리 가져오기  (0) 2023.04.09
의 , 어떻게 수? 의 , 어떻게 수? 의 , 어떻게 수?  (0) 2023.04.09
SQL Server에서 여러 행에서 하나의 쉼표로 구분된 값 사용  (0) 2023.04.09
현재 Git 작업 트리에서 로컬(추적되지 않은) 파일을 제거하려면 어떻게 해야 합니까?  (0) 2023.04.09

'programing'의 다른글

  • 현재글SQL Server에 IP 주소를 저장하기 위한 데이터 유형

관련글

댓글

티스토리툴바