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

## 简介

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

<!-- more -->

## 用什么数据类型？

问：*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位。