Сохранение IP-адресов в SQL server: подходящий тип данных

Быстрый ответ

При работе с IP-адресами в SQL Server рекомендуется использовать BINARY(4) для сохранения адресов IPv4 и BINARY(16) для адресов IPv6. Эти форматы обеспечивают компактное хранение данных, что особенно полезно при работе с диапазонами адресов. Например, вот как можно преобразовать IPv4-адрес в эффективный для хранения бинарный формат:

DECLARE @IP VARBINARY(4) = CAST('/x' + REPLACE('192.168.1.1', '.', '/x') AS VARBINARY(4))
SELECT @IP

Использование INT для индексации

Тип INT позволяет значительно усовершенствовать индексацию в SQL Server, а также осуществлять автоматическую сортировку по величине. Для преобразования IP-адреса из формата IPv4 в численный можно воспользоваться функцией BITCONVERTER.

-- Целочисленное представление IP-адресов – прекрасное решение!
DECLARE @IP INT = CAST(BITCONVERTER::ToInt32(IPAddress.Parse('192.168.1.1').GetAddressBytes(), 0) AS INT)
SELECT @IP

Извлечение составляющих с помощью PARSENAME

Функция PARSENAME может быть полезна, если вам требуется извлечь отдельные октеты из IP-адреса. Она рассматривает адрес как составное имя из четырех частей и позволяет работать с каждым из них отдельно:

-- Может быть, кому-то потребуется последний октет?
SELECT PARSENAME('192.168.1.1', 1) AS LastOctet

Работа с масками подсети используя BINARY

При использовании BINARY(4) для хранения адресов IPv4 становятся доступными побитовые операции. Это особенно актуально для работы с масками подсетей или отдельными диапазонами адресов:

-- Все мы любим побитовые операции, правда же?
SELECT * FROM YourTable
WHERE IPAddress & SubnetMask = SubnetAddress

Особенности хранения IPv6

Хотя BIGINT является избыточным для IPv4, для хранения существенно более крупных адресов IPv6 прекрасно подойдет BINARY(16).

Визуализация

| Версия IP | Рекомендуемый SQL тип | Визуализация |
|-----------|-----------------------|--------------|
| IPv4      | `INT` или `BINARY(4)` | 🏖️ -> 📦      |
| IPv6      | `BINARY(16)`          | ✉️ -> 📦      |

Обозначения:
🏖️ – Читаемый человеку формат IPv4
✉️ – Обширный формат IPv6, требующий больше места
📦 – Данные в базе SQL, итоговое место хранения

Обеспечьте безболезненное 'переезжание' и 'расхождение' 'открыток' и 'конвертов' по 'почтовым ящикам' вашей базы данных для более удобной и эффективной 'сортировки' данных. 🏠📬

Работа с IP-адресами как с числами

Чтобы преобразовать значения IP-адресов из формата INT обратно в удобочитаемую форму, можно воспользоваться классами BitConverter и IPAddress из .NET:

// Выходим из глубины индексов обратно к понятным адресам.
int ipAddressInt = ... // Значение, сохранённое в INT
byte[] bytes = BitConverter.GetBytes(ipAddressInt);
string ipAddress = new IPAddress(bytes).ToString();

Оптимизация индексации

При работе с большими объемами данных, где индексация играет ключевую роль, возможно добавление лидирующих нулей к IP-адресам, сохранённым в формате INT. Благодаря этому можно сделать индексы более упорядоченными, что снижает фрагментацию и ускоряет последовательное чтение:

-- Сделаем наши IP более читаемыми и оптимизируем их для индексации!
DECLARE @IP INT = 167772161 -- Это '10.0.0.1'
DECLARE @FormattedIP VARCHAR(15) = RIGHT('0000000000' + CAST(@IP AS VARCHAR(10)), 10)

Парадокс использования типа hierarchyid

Хотя казалось бы у hierarchyid есть преимущество в организации IP-адресов, на самом деле этот тип данных не подходит для их стандартного хранения и использования. Он предназначен для отображения иерархических структур, а не линейных наборов данных, которыми являются IP-адреса.

Полезные материалы

1. Типы данных (Transact-SQL) – SQL Server | Microsoft Docs — Полная информация от Microsoft о типах данных в SQL Server.
2. Какой тип данных использовать для хранения IP-адресов в SQL Server – Stack Overflow — Обзор и отбор лучших практик по хранению IP-адресов в SQL Server.
3. Типы данных сетевых адресов – PostgreSQL — Особенности и возможности типов данных для IP-адресов в PostgreSQL.
4. Разнообразные функции – MySQL — Примеры того, как MySQL работает с IP-адресами: варианты хранения и инструменты для обработки данных.