浅谈Mysql中VARBINARY(16)存储IP地址

简介

本文用于整理记录一下使用 ChatCPT 学习使用 Mysql 如何存储IP地址那点事。如果你详细阅读本文, 相信你可以搞懂 VARBINARY 类型的一些知识.

用什么数据类型？

问：mysql数据库存储IP地址，建议使用什么数据类型

CharGPT：

当在 MySQL 数据库中存储 IP 地址时，可以考虑使用 VARBINARY 或 CHAR 数据类型，这取决于你的具体需求。以下是两种常用的方式：

VARBINARY：使用 VARBINARY 类型可以存储不定长的二进制数据，适合存储 IPv4 或 IPv6 地址。如果你的应用程序需要处理不同版本的 IP 地址（IPv4 和 IPv6），VARBINARY 是更灵活的选择。

示例：
sql
CREATE TABLE ip_addresses (
    id INT PRIMARY KEY,
    ip_address VARBINARY(16) -- 对于 IPv6 地址，可以使用 VARBINARY(16)，对于 IPv4 地址，可以使用 VARBINARY(4)
);
CHAR：如果你知道你的应用程序只处理特定长度的 IP 地址，可以考虑使用 CHAR 类型。例如，IPv4 地址是 15 个字符，IPv6 地址是 39 个字符。

示例：
sql
CREATE TABLE ip_addresses (
    id INT PRIMARY KEY,
    ip_address CHAR(39) -- 对于 IPv6 地址，可以使用 CHAR(39)，对于 IPv4 地址，可以使用 CHAR(15)
);
注意：

在实际应用中，可能会使用 INET_ATON 和 INET_NTOA 函数将 IP 地址转换为整数，然后存储为整数类型，这样能够更方便地进行查询和排序。不过，这样做会失去直接存储 IP 地址字符串的可读性。

如果你的应用程序需要进行 IP 地址范围查询或其他复杂的 IP 地址相关操作，你可能需要设计更复杂的数据结构或使用专门的数据库扩展/插件。

综上所述，选择使用 VARBINARY 或 CHAR 主要取决于你的应用需求和对存储空间的关注。

VARBINARY如何使用?

问: VARBINARY是什么类型？可读性怎么样？给个实例

ChatGPT: (提示, 下面的示例不太对, 起码不常用)

VARBINARY 是一种 MySQL 数据库中用于存储可变长度二进制数据的数据类型。它与 VARCHAR 类似，但用于存储二进制数据而不是字符数据。VARBINARY 可以存储任意长度的二进制数据，包括字节、图片、文件等。这种数据类型在存储 IP 地址时非常有用，特别是当处理 IPv6 地址时，因为 IPv6 地址比 IPv4 地址更长。

以下是使用 VARBINARY 存储 IP 地址的简单示例：
sql
CREATE TABLE ip_addresses (
    id INT PRIMARY KEY,
    ip_address VARBINARY(16) -- 对于 IPv6 地址，可以使用 VARBINARY(16)，对于 IPv4 地址，可以使用 VARBINARY(4)
);

-- 插入 IPv6 地址
INSERT INTO ip_addresses (id, ip_address) VALUES (1, UNHEX('2001:0db8:85a3:0000:0000:8a2e:0370:7334'));

-- 插入 IPv4 地址
INSERT INTO ip_addresses (id, ip_address) VALUES (2, UNHEX('c0a80101'));
在上面的示例中，UNHEX 函数用于将十六进制字符串转换为二进制数据，然后将其插入到 VARBINARY 类型的字段中。这样，你可以存储 IPv4 或 IPv6 地址，而不需要考虑长度限制。

注意：尽管 VARBINARY 在存储二进制数据时非常灵活，但它不是可读的。如果你需要可读性，你可能需要使用 CHAR 类型或将 IP 地址存储为字符串。

如何存储IPv4?

问: IPv4地址一般不都是四位十进制的整数吗？你的示例中为什么是16进制

ChatGPT:

你是对的，IPv4 地址通常由四个十进制数表示，每个数在 0 到 255 之间。我在前一个回答中的示例中使用十六进制是为了演示存储 IPv6 地址时 VARBINARY 数据类型的用法。对于 IPv4 地址，你可以使用十进制或将其转换为二进制。

以下是使用十进制表示 IPv4 地址的示例：
sql
CREATE TABLE ipv4_addresses (
    id INT PRIMARY KEY,
    ip_address VARBINARY(4)
);

-- 插入 IPv4 地址，例如 192.168.1.1
INSERT INTO ipv4_addresses (id, ip_address) VALUES (1, INET_ATON('192.168.1.1'));

-- 查询 IPv4 地址
SELECT id, INET_NTOA(ip_address) AS ipv4_address FROM ipv4_addresses;
在这个示例中，INET_ATON 函数用于将 IPv4 地址转换为整数，然后将其存储在 VARBINARY(4) 数据类型的字段中。使用 INET_NTOA 函数可以将整数转换回可读的 IPv4 地址。

如果你更喜欢使用十六进制表示，可以使用十六进制的字符串表示法，然后使用 UNHEX 函数将其转换为二进制数据，但这并不是通常的做法，因为它会降低可读性。

如何存储IPv6?

问: varbinary(16)类型的字段如何存储 2409:8a3c:46d:5b70:48e:3882:11dd:3119 这样的IPV6地址

ChatGPT:

IPv6 地址是128位的，通常以8组16进制数表示，每组由4个字符组成，以冒号分隔。在 MySQL 中，如果要存储 IPv6 地址，可以使用 VARBINARY(16) 数据类型。

以下是一个存储 IPv6 地址的示例：
sql
CREATE TABLE ipv6_addresses (
    id INT PRIMARY KEY,
    ip_address VARBINARY(16)
);

-- 插入 IPv6 地址
INSERT INTO ipv6_addresses (id, ip_address) VALUES (1, INET6_ATON('2409:8a3c:46d:5b70:48e:3882:11dd:3119'));

-- 查询 IPv6 地址
SELECT id, INET6_NTOA(ip_address) AS ipv6_address FROM ipv6_addresses;
在这个示例中，INET6_ATON 函数用于将 IPv6 地址转换为二进制数据，然后将其存储在 VARBINARY(16) 数据类型的字段中。使用 INET6_NTOA 函数可以将二进制数据转换回可读的 IPv6 地址。

请注意，IPv6 地址的长度为128位，相当于16个字节。因此，我们选择了 VARBINARY(16) 作为存储 IPv6 地址的数据类型。

总结实例

假设你有一张 log 表，有 ip 和 v 两个字段，分别用于存储IP地址和类型。

ip 字段：VARBINARY(16)
v 字段：int，4代表IPv4，6代表IPv6。

基本操作如下:

sql
-- 插入IPv4数据
insert into iplog(ip,v) value (INET_ATON('192.168.0.1'), 4);
-- 查询IPv4数据
select INET_NTOA(ip) from iplog where v = 4;

-- 插入IPv6数据
insert into iplog(ip,v) value (INET6_ATON('2409:8a3c:46d:5b70:48e:3882:11dd:3119'), 6);
-- 查询IPv6数据
select INET6_NTOA(ip) from iplog where v = 6;

拓展

问: VARBINARY和BINARY有什么区别

ChatGPT:

VARBINARY 和 BINARY 都是 MySQL 中用于存储二进制数据的数据类型，它们之间的主要区别在于是否具有可变长度。

VARBINARY：VARBINARY 表示可变长度的二进制数据类型。在创建表时，你需要指定最大长度。例如，VARBINARY(255) 表示可以存储最多 255 个字节的可变长度二进制数据。如果存储的实际数据长度小于最大长度，MySQL 将只使用实际数据所需的存储空间。示例：VARBINARY(255)

BINARY：BINARY 表示固定长度的二进制数据类型。在创建表时，你需要指定确切的长度。例如，BINARY(10) 表示存储固定长度为 10 个字节的二进制数据。如果存储的实际数据长度小于指定的长度，MySQL 会在右侧填充空字节。示例：BINARY(10)

示例用法：
sql
-- 使用 VARBINARY 存储二进制数据
CREATE TABLE varbinary_example (
    id INT PRIMARY KEY,
    data VARBINARY(255)
);

-- 使用 BINARY 存储固定长度二进制数据
CREATE TABLE binary_example (
    id INT PRIMARY KEY,
    data BINARY(10)
);
总的来说，选择使用 VARBINARY 还是 BINARY 取决于你的需求。如果你存储的二进制数据长度可能变化，可以使用 VARBINARY。如果你要确保固定长度的存储，并希望在存储实际数据不足指定长度时填充空字节，可以使用 BINARY。

问: IPv6换算为2进制有多少位？为什么能存到VARBINARY(16)中

ChatGPT:

IPv6 地址是128位的，通常表示为8组16进制数，每组由4个字符组成，中间用冒号分隔。这样的表示法下，IPv6 地址总共有 32 个字符（8 组 * 4 字符/组），而每个十六进制字符可以表示4位二进制数。

因此，IPv6 地址换算为二进制的位数为 32 * 4 = 128 位。

VARBINARY(16) 是 MySQL 中用于存储可变长度二进制数据的类型，其中的数字 16 表示该字段最大能够存储的字节数。虽然 IPv6 地址本身有128位，但 VARBINARY(16) 的目的并不是直接存储 IPv6 地址的二进制表示，而是提供了一个足够大的空间来存储 IPv6 地址的二进制形式。

在 MySQL 中，IPv6 地址通常会使用 VARBINARY(16) 存储，以确保有足够的空间来容纳 IPv6 地址的二进制表示。这并不意味着每个 IPv6 地址都会占用 16 字节，而是提供了足够的空间以应对 IPv6 地址未来可能的变化和扩展。

问: 16 字节相当于多少位2进制？

ChatGPT:

一个字节（Byte）等于8位（bits）。因此，16字节等于16 * 8 = 128位。

目录

简介

用什么数据类型？

VARBINARY如何使用?

如何存储IPv4?

如何存储IPv6?

总结实例

拓展