本文将介绍如何在数据库中使用合适格式保存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为整型时,需要注意出现负数。