MySQL存储IP地址应该用什么类型

之前一直都是用的CHAR或者VARCHAR存的IP, 看了书才知道原来一直都搞错了.

References

• 《高性能MySQL 第3版》
• https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_inet-aton
• https://en.wikipedia.org/wiki/Dot-decimal_notation
• https://zh.wikipedia.org/wiki/IPv4

直接引用书中原文：

人们经常使用VARCHAR(15)列来存储IP地址。然而，它们实际上是32位无符号整数，不是字符串。用小数点将地址分成四段的表示方法只是为了让人们阅读容易。所以应该用无符号整数存储IP地址。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。

再看看MySQL官方文档中是怎么说的, 简单翻译一下：

• INET_ATON (expr)

  Given the dotted-quad representation of an IPv4 network address as a string, returns an integer that represents the numeric value of the address in network byte order (big endian). INET_ATON() returns NULL if it does not understand its argument.

传入点分四段形式的IPv4地址字符串，返回一个网络字节序（大端序）的整数地址值。 如果INET_ATON()解析失败则返回NULL。

mysql> SELECT INET_ATON('10.0.5.9');
        -> 167773449

上面例子中, 返回值的计算方式是: 10×256³ + 0×256² + 5×256 + 9.

使用IP地址的缩写形式（例如'127.1'表示'127.0.0.1'）时，INET_ATON()可能会也可能不会返回一个非NULL值。因此，INET_ATON()不应该用于这种地址的解析。

注意！！！
要存储INET_ATON生成的IP地址，要使用INT UNSIGNED而不是INT（也就是应该使用无符号的INT），如果使用有符号的INT表示IP地址，那么第一个字节的值大于127时就不能正确存储了。

• INET_NTOA (expr)

  Given a numeric IPv4 network address in network byte order, returns the dotted-quad string representation of the address as a string in the connection character set. INET_NTOA() returns NULL if it does not understand its argument.

以网络字节序传入一个数值型IPv4地址，以链接的字符集返回字符点分四段形式的地址，解析失败返回NULL

mysql> SELECT INET_NTOA(167773449);
        -> '10.0.5.9'

IPv4地址的长度为32位（4字节），可被写作任何表示一个32位整数值的形式，但为了方便人类阅读和分析，它通常被写作点分十进制（Dot-decimal notation）的形式，即四个字节被分开用十进制写出，中间用点分隔。

然后使用一下这俩函数，感觉也没什么好展示的：

mysql> SELECT INET_ATON('8.8.8.8') FROM DUAL;
+----------------------+
| INET_ATON('8.8.8.8') |
+----------------------+
|            134744072 |
+----------------------+
1 row in set (0.000 sec)

mysql> SELECT INET_NTOA(2130706433) FROM DUAL;
+-----------------------+
| INET_NTOA(2130706433) |
+-----------------------+
| 127.0.0.1             |
+-----------------------+
1 row in set (0.000 sec)

mysql> SELECT INET_ATON('256.8.8.8') FROM DUAL;
+------------------------+
| INET_ATON('256.8.8.8') |
+------------------------+
|                   NULL |
+------------------------+
1 row in set (0.000 sec)

最后，IPv6的转换可以查看MySQL的文档，都有详细的说明