[解決済み] SQL Server で ip アドレスを格納するためのデータ型

質問

SQL Server で IP Address を格納するために、どのようなデータ型を選択すればよいですか。

正しいデータ型を選択することで、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

最後に、ルックアップや比較を行う場合、インデックスを活用したいのであれば、常にバイナリ形式を使用することです。

UPDATEです。

SQL Server におけるスカラー UDF の固有のパフォーマンス問題に対処しつつ、関数のコードの再利用を維持する 1 つの方法は、代わりに iTVF (inline table-valued function) を使用することであることを追加したいと思います。 ここでは、上記の最初の関数（string to binary）を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'))