MySQL-存储IP地址一文解决（随便问~）

以下分析基于MySQL5.6及以上，参看：

• 《高性能MySQL 第三版》
• https://dev.mysql.com/doc/refman/8.0/en/

在MySQL中存储IP地址有多种方法，通过各自的特点，在不同的场景下选择不同的存储方式。这里结合实际应用提供并分析两种存储IP地址的方法。

一、IPv4和IPv6

IP地址分为IPv4和IPv6两种地址格式，下面介绍一下两者：

IPv4的地址是由4组十进制数值组成，数值之间采用.分隔，每一组占1byte，8比特位，范围是0~255。完整的IPv4地址采用数值表示就占4byte，它的范围是0.0.0.0 - 255.255.255.255.255，如127.0.0.0就是是IPv4的地址。且可由字符串表示。

IPv6的地址是由8组十六进制数值组成，每一组占2byte，16比特位，范围是0~65535。完整的IPv6地址采用数值表示就占16byte，它的范围是0:0:0:0:0:0:0:0 到FFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF。数值之间多种分隔方式，常见的三种表示方式是（每个X前的0都可省略）：

1. 冒分十六进制表示法：格式X:X:X:X:X:X:X:X，比如ABCD:EF01:2345:6789:ABCD:EF01:2345:6789。
2. 0位压缩表示法：格式和冒分十六进制表示法一致，但他可以把连续的一段0压缩位::，且::只能出现一次，比如FF01:0:0:0:0:0:0:1101 → FF01::1101。
3. 内嵌IPv4地址表示法：格式X:X:X:X:X:X:d.d.d.d，最后四组采用IPv4的规则。比如::FFFF:192.168.0.1和::192.168.0.1。

在对两者有简单的认识和了解后，下面就提供和分析使用字符串存储和使用数值存储的区别和适用情况。

---

二、数值存储IP地址（推荐）

1.数值类型分析

我们知道IPv4的范围是0.0.0.0 - 255.255.255.255，它占4byte，总共有256⁴种组合，即4,294,967,296种组合。在MySQL中，自然有用于能够提供存储服务的数据类型，我们采用**unsigned int(10)**存储IPv4，占4byte，非常的节约空间。

我们知道IPv6的范围是0:0:0:0:0:0:0:0 到FFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF，它占16byte。在MySQL中，自然有用于能够提供存储服务的数据类型，我们采用VARBINARY(16)（其中的16表示字节数）存储IPv6，占16byte，不仅可以使用VARBINARY(16)存储IPv6的地址，可以使用使用VARBINARY(16)存储IPv4的地址，在不确定IP地址类型的情况下可以使用VARBINARY(16)类型声明IP字段。

BINARY和VARBINARY与CHAR和VARCHAR类似类型，它们存储的是二进制字符串。二进制字符串和常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。即可以适用数值表示的字节码。

---

2.MySQL中转换IP

IPv4在MySQL中使用：

1. INET_ATON(String ip)把String类型IPv4的ip转换为数值类型；
2. INET_NTOA(int ip)把int类型IPv4的ip转换为String类型。

IPv6在MySQL中使用：

1. INET6_ATON(String ip)把String类型IPv6的ip转换为数值类型；
2. INET6_NTOA(int ip)把int类型IPv6的ip转换为String类型。

以为VARBINARY兼容IPv4地址，所以使用INET6_ATON和INET6_NTOA转换IPv4的地址也是可取的。

---

3.存储示例

3.1 IPv4 (int)

INET_ATON(Stringt ip) 和 INET_NTOA(int ip)

-- 1.创建ipv4_int表，包含int类型的ip字段
CREATE TABLE IF NOT EXISTS `ipv4_int` (
	ip INT(10) UNSIGNED
);

-- 2.插入三条数据 使用INET_ATON()将String类型的ip转换为int
INSERT INTO ipv4_int VALUES(INET_ATON('127.0.0.0')),(INET_ATON('192.168.129')),(INET_ATON('192.168.130'));

-- 3.查看存储在数据表的int类型的ip
SELECT ip FROM ipv4_int;
+------------+
| ip         |
+------------+
| 2130706432 |
| 3232235649 |
| 3232235650 |
+------------+

-- 4.查询数据表数据 使用INET_NTOA()将int类型的ip转换为String
SELECT INET_NTOA(ip) FROM ipv4_int;
+---------------+
| INET_NTOA(ip) |
+---------------+
| 127.0.0.0     |
| 192.168.0.129 |
| 192.168.0.130 |
+---------------+

---

3.2 IPv6 (varbinary)

INET6_ATON(Stringt ip) 和 INET6_NTOA(int ip)

-- 1.创建ipv6_int表，包含varbinary类型的ip字段
CREATE TABLE IF NOT EXISTS `ipv6_varbinary` (
	ip VARBINARY(16)
);

-- 2.插入三条数据 使用INET6_ATON()将String类型的ip转换为varbinary，包括IPv4类型ip
INSERT INTO ipv6_varbinary VALUES(INET6_ATON('1030::C9B4:FF12:48AA:1A2B')),(INET6_ATON('FF:F:F:F:F:F:F:F')),(INET6_ATON('::192.168.130.0')),(INET6_ATON('192.168.130.0'));

-- 3.查看存储在数据表的varbinary类型的ip 乱码我目前没有找到好的解决方法，乱码原因是varbinary为二进制存储数值，计算机在读取的时候自动转化为10十进制显示
SELECT ip FROM ipv6_varbinary;
+------------------+
| ip               |
+------------------+
| 0      ɴÿHª+    |
|  ÿ         |
|             À¨‚     |
| À¨‚                 |
+------------------+
4 rows in set (0.00 sec)

-- 4.查询数据表数据 使用INET6_NTOA()将int类型的ip转换为String
SELECT INET6_NTOA(ip) FROM ipv6_varbinary;
+---------------------------+
| INET6_NTOA(ip)            |
+---------------------------+
| 1030::c9b4:ff12:48aa:1a2b |
| ff:f:f:f:f:f:f:f          |
| ::192.168.130.0           |
| 192.168.130.0             |
+---------------------------+
4 rows in set (0.00 sec)

---

4.优点

使用数值模式存储IP地址的优点有：

1. 节省内存。以IPv4为例，unsigned int(10)占4byte，而varchar至少占7byte；
2. 支持范围查找，提升性能。以为IPv4为例，可以查找192.168.1.1到192.168.255.255范围内的IP，如果字符串类型的进行查找，那匹配速度是及其慢的。

---

三、字符串存储IP地址

推荐使用数值存储IP地址，节省内存，还支持范围查找。

1.字符串类型分析

在使用字符串存储IP地址时，考虑的东西就算字符串的长度，正如上面分析道，IPv4的IP地址范围是0.0.0.0 - 255.255.255.255，字符串长度7到15位。在MySQL中，采用**varchar(15)**存储IPv4，占15byte。

IPv6类型的IP的字符串长度是2到39位。在MySQL中，我们采用VARCHAR(39)（其中的139表示字节数）存储IPv6，占39byte，不仅存储IPv6的地址，还可以存储IPv4的地址。

2.存储示例

IPv4和IPv6的存储读取步骤都一样，演示IPv4的格式即可。

-- 1.创建ipv4_char表 有类型为varchar的字段
CREATE TABLE IF NOT EXISTS `ipv4_char` (
	ip VARCHAR(15)
);

-- 2.插入和查看ip
INSERT INTO ipv4_char VALUES('127.0.0.0'),('192.168.129'),('192.168.130');
SELECT ip FROM ipv4_char;
+-------------+
| ip          |
+-------------+
| 127.0.0.0   |
| 192.168.129 |
| 192.168.130 |
+-------------+
3 rows in set (0.01 sec)