Тип данных для хранения ip-адреса в SQL Server

какой тип данных следует выбрать для хранения IP-адреса в SQL Server?



выбрав правильный тип данных, будет ли достаточно легко фильтровать по IP-адресу?

720   10  

10 ответов:

технически правильный способ хранения 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

наконец, при выполнении поиска и сравнения всегда используйте двоичную форму, если вы хотите иметь возможность использовать свои индексы.

обновление:

Я хотел добавить, что один из способов решения присущих проблем производительности скалярных UDF в SQL Server, но все же сохранить код-повторное использование функции заключается в использовании iTVF (inline таблично-значная функция) вместо этого. Вот как первая функция выше (строка в двоичный код) может быть переписана как 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'))
117  
2013-12-09 17:52:00

вы можете использовать varchar. Длина IPv4 статична, но длина IPv6 может быть очень переменной.

Если у вас нет веской причины хранить его как двоичный, придерживайтесь строкового (текстового) типа.

23  
2015-07-10 01:28:01

вот код для преобразования IPV4 или IPv6 в формате varchar в двоичный (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
16  
2015-01-05 11:25:22

как я хочу, чтобы обрабатывать IPv4 и IPv6, Я использую VARBINARY(16) и после SQL CLR функции для преобразования text представление IP-адреса в байтах и наоборот:

[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);
}
7  
2015-03-31 17:00:34

sys.dm_exec_connections использует varchar (48) после SQL Server 2005 SP1. Звучит достаточно хорошо для меня, особенно если вы хотите использовать его по сравнению с вашей стоимостью.

реалистично, вы еще не увидите IPv6 как мейнстрим, поэтому я бы предпочел маршрут 4 tinyint. Говоря это, я использую varchar (48), потому что я должен использовать sys.dm_exec_connections...

в противном случае. Ответ Марка Редмана упоминает предыдущий SO дебаты вопрос.

6  
2009-09-06 15:38:20

Я обычно использую обычную старую фильтрацию VARCHAR для IPAddress отлично работает.

Если вы хотите фильтровать по диапазонам IP-адреса, я бы разбил его на четыре целых числа.

2  
2014-07-16 00:42:09

Спасибо RBarry. Я собираю систему распределения IP-блоков и храню как двоичный-это единственный способ пойти.

Я сохраняю представление CIDR (например: 192.168.1.0/24) блока IP в поле 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
1  
2010-04-08 22:35:30

для людей, использующих .NET можно использовать класс IPAddress для разбора строки IPv4 / IPv6 и хранить его как VARBINARY(16). Можно использовать тот же класс для преобразования byte[] в строку. Если хотите конвертировать VARBINARY в 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
1  
2015-03-04 11:31:47

мне нравятся функции SandRock. Но я нашел ошибку в коде dbo.fn_ConvertIpAddressToBinary. Входящий параметр @ ipAddress VARCHAR (39) слишком мал, когда вы соединяете @delim с ним.

SET @ipAddress = @ipAddress + @delim

вы можете увеличить его до 40. Или еще лучше использовать новую переменную, которая больше, и использовать ее внутри. Таким образом, вы не потеряете последнюю пару на больших числах.

SELECT dbo.fn_ConvertIpAddressToBinary('ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff')
-1  
2017-04-27 18:44:40

Я использую varchar(15) пока все работает на меня. Вставить, Обновить, Выбрать. Я только что запустил приложение, которое имеет IP-адреса,хотя я еще не сделал много работы dev.

вот оператор select:

select * From dbo.Server 
where  [IP] = ('132.46.151.181')
Go
-2  
2018-09-18 06:10:43

Comments

    Ничего не найдено.
Click here to cancel reply.