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