mysql 使用inet_aton和inet_ntoa处理ip地址数据

本文将介绍如何在数据库中使用合适格式保存ip地址数据，并能方便的对ip地址进行比较的方法。

1.保存ip地址到数据库

数据库中保存ip地址，字段一般会定义为：

<code class="hljs autohotkey has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-escape" style="box-sizing: border-box;">`i</span>p<span class="hljs-escape" style="box-sizing: border-box;">` </span>char(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">15</span>) <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">NOT</span> NULL,</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

因为ip地址(255.255.255.255)的最大长度是15，使用15位char已足够。

创建表user

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TABLE</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`user`</span> (
 <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`id`</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">11</span>) unsigned <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NOT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span> AUTO_INCREMENT,
 <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`name`</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">varchar</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">30</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NOT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,
 <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`ip`</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">char</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">15</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NOT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,
 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">PRIMARY</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">KEY</span> (<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`id`</span>)
) ENGINE=InnoDB;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li></ul>

插入几条数据

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`user`</span> (<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`id`</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`name`</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`ip`</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>
(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Abby'</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'192.168.1.1'</span>),
(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Daisy'</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'172.16.11.66'</span>),
(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">4</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Christine'</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'220.117.131.12'</span>);</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li></ul>

2.mysql inet_aton 与 inet_ntoa 方法

mysql提供了两个方法来处理ip地址

inet_aton 把ip转为无符号整型(4-8位) 
inet_ntoa 把整型的ip转为电地址

插入数据前，先用inet_aton把ip地址转为整型，可以节省空间，因为char(15) 占16字节。 
显示数据时，使用inet_ntoa把整型的ip地址转为电地址显示即可。

例子:

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TABLE</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`user`</span> (
 <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`id`</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">11</span>) unsigned <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NOT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span> AUTO_INCREMENT,
 <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`name`</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">varchar</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">100</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NOT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,
 <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`ip`</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10</span>) unsigned <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NOT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,
 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">PRIMARY</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">KEY</span> (<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`id`</span>)
) ENGINE=InnoDB;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li></ul>

插入几条数据

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`user`</span> (<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`id`</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`name`</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">`ip`</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>
(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Abby'</span>, inet_aton(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'192.168.1.1'</span>)),
(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Daisy'</span>, inet_aton(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'172.16.11.66'</span>)),
(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">4</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'Christine'</span>, inet_aton(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'220.117.131.12'</span>));</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li></ul>

<code class="hljs asciidoc has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-header" style="box-sizing: border-box;">mysql> select * from `user`;
+----+-----------+------------+</span>
<span class="hljs-header" style="box-sizing: border-box;">| id | name      | ip         |
+----+-----------+------------+</span>
|  2 | Abby      | 3232235777 |
|  3 | Daisy     | 2886732610 |
<span class="hljs-header" style="box-sizing: border-box;">|  4 | Christine | 3698688780 |
+----+-----------+------------+</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li></ul>

查询显示为电地址

<code class="hljs asciidoc has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-header" style="box-sizing: border-box;">mysql> select id,name,inet_ntoa(ip) as ip from `user`;
+----+-----------+----------------+</span>
<span class="hljs-header" style="box-sizing: border-box;">| id | name      | ip             |
+----+-----------+----------------+</span>
|  2 | Abby      | 192.168.1.1    |
|  3 | Daisy     | 172.16.11.66   |
<span class="hljs-header" style="box-sizing: border-box;">|  4 | Christine | 220.117.131.12 |
+----+-----------+----------------+</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li></ul>

3.比较方法

如果需要找出在某个网段的用户（例如：172.16.11.1 ~ 172.16.11.100)，可以利用php的ip2long方法，把ip地址转为整型，再进行比较。

<code class="hljs xml has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="php" style="box-sizing: border-box;"><span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;"><?php</span>
<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$ip_start</span> = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'172.16.11.1'</span>;
<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$ip_end</span> = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'172.16.11.100'</span>;

<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">echo</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'ip2long(ip_start):'</span>.sprintf(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%u'</span>,ip2long(<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$ip_start</span>)); <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">// 2886732545</span>
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">echo</span> <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'ip2long(ip_end):'</span>.sprintf(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'%u'</span>,ip2long(<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$ip_end</span>));     <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">// 2886732644</span>
<span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;">?></span></span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li></ul>

查询：

<code class="hljs asciidoc has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-header" style="box-sizing: border-box;">mysql> select ip,name,inet_ntoa(ip) as ip from `user` where ip>=2886732545 and ip<=2886732644;
+------------+-------+---------------+</span>
<span class="hljs-header" style="box-sizing: border-box;">| ip         | name  | ip            |
+------------+-------+---------------+</span>
<span class="hljs-header" style="box-sizing: border-box;">| 2886732610 | Daisy | 172.16.11.66  |
+------------+-------+---------------+</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li></ul>

注意：使用ip2long方法把ip地址转为整型时，对于大的ip会出现负数，出现原因及处理方法可以参考我另一篇文章：《php ip2long 出现负数原因及解决方法》 

4.总结

1.保存ip地址到数据库，使用unsigned int格式，插入时使用inet_aton方法把ip先转为无符号整型，可以节省存储空间。 
2.显示时使用inet_ntoa把整型ip地址转为电地址。 
3.php ip2long转ip为整型时，需要注意出现负数。