IP地址排序问题(排序IP)

最新推荐文章于 2022-06-13 19:10:37 发布

shanliangliuxing

最新推荐文章于 2022-06-13 19:10:37 发布

阅读量1.1w

点赞数 3

CC 4.0 BY-SA版权

分类专栏：数据库

本文链接：https://blog.csdn.net/shanliangliuxing/article/details/8970764

数据库专栏收录该内容

44 篇文章

订阅专栏

本文介绍如何在MySQL数据库中正确地对作为字符串存储的IP地址进行排序，并提供了使用INET_ATON函数的具体方法，确保了IP地址能按数值顺序排列。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据库中IP都是作为字符串存储的，这样就有一个问题，在sql排序时，如果直接按IP地址排序，返回的结果跟预想的不太一样：

sql查询语句：

select * from ippool_ipmodel order by ipAddress Asc limit 0,100 ;

返回结果：

23530,"1.1.1.10","255.255.255.0",0,0,2,""
24042,"1.1.1.10","255.255.255.0",0,1,3,""
24298,"1.1.1.10","255.255.255.0",0,1,5,""
23786,"1.1.1.10","255.255.255.0",0,1,4,""
23876,"1.1.1.100","255.255.255.0",0,0,4,""
24132,"1.1.1.100","255.255.255.0",0,0,3,""
24388,"1.1.1.100","255.255.255.0",0,0,5,""
23620,"1.1.1.100","255.255.255.0",0,0,2,""
17668,"1.1.1.100","255.255.255.0",0,0,1,""
17669,"1.1.1.101","255.255.255.0",0,0,1,""
23621,"1.1.1.101","255.255.255.0",0,0,2,""
24389,"1.1.1.101","255.255.255.0",0,0,5,""
23877,"1.1.1.101","255.255.255.0",0,0,4,""
24133,"1.1.1.101","255.255.255.0",0,0,3,""
24134,"1.1.1.102","255.255.255.0",0,0,3,""
24390,"1.1.1.102","255.255.255.0",0,0,5,""
23878,"1.1.1.102","255.255.255.0",0,0,4,""
23622,"1.1.1.102","255.255.255.0",0,0,2,""
17670,"1.1.1.102","255.255.255.0",0,0,1,""
17671,"1.1.1.103","255.255.255.0",0,0,1,""
23623,"1.1.1.103","255.255.255.0",0,0,2,""
23879,"1.1.1.103","255.255.255.0",0,0,4,""

发现顺序是这样的：1，10，100，101，102，103，……，11，110，111，112，……

而我想要得到的结果应该是这样才对：1，2，3，……，9，10，11，12，……，20，21，22，……，100，101，102，……

其实mysql自带了一个内部函数，可以使用此内部函数实现上述的排序：

select * from ippool_ipmodel order by inet_aton(ipAddress) Asc limit 0,100 ;

返回结果：

17568,"1.1.1.0","255.255.255.0",0,0,1,""
23520,"1.1.1.0","255.255.255.0",0,0,2,""
23776,"1.1.1.0","255.255.255.0",0,0,4,""
24032,"1.1.1.0","255.255.255.0",0,0,3,""
24288,"1.1.1.0","255.255.255.0",0,0,5,""
24289,"1.1.1.1","255.255.255.0",0,1,5,""
24033,"1.1.1.1","255.255.255.0",0,0,3,""
23777,"1.1.1.1","255.255.255.0",0,1,4,""
23521,"1.1.1.1","255.255.255.0",0,1,2,""
17569,"1.1.1.1","255.255.255.0",0,1,1,""
17570,"1.1.1.2","255.255.255.0",0,1,1,""
23522,"1.1.1.2","255.255.255.0",0,0,2,""
23778,"1.1.1.2","255.255.255.0",0,1,4,""
24034,"1.1.1.2","255.255.255.0",0,0,3,""
24290,"1.1.1.2","255.255.255.0",0,1,5,""
24291,"1.1.1.3","255.255.255.0",0,1,5,""
24035,"1.1.1.3","255.255.255.0",0,1,3,""
23779,"1.1.1.3","255.255.255.0",0,1,4,""
23523,"1.1.1.3","255.255.255.0",0,0,2,""
17571,"1.1.1.3","255.255.255.0",0,0,1,""
17572,"1.1.1.4","255.255.255.0",0,0,1,""
23524,"1.1.1.4","255.255.255.0",0,0,2,""

Django中的写法：
ips =IPModel.objects.extra(select={'ipa': "inet_aton(ipAddress)"})
ips =ips.extra(order_by=['-ipa'])

下面是关于此内部函数的说明：

转自：http://lanfeng898.blog.163.com/blog/static/1625900772011813112732375/

Mysql的IP处理函数inet_aton()和inet_ntoa()

有个统计需求，需要对IP进行分类统计，Mysql提供了inet_aton()和inet_ntoa()函数进程处理。具体可以查看官方手册。

INET_ATON(expr)
给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。地址可以是4或8比特地址。

mysql> SELECT INET_ATON('209.207.224.40');+-----------------------------+| INET_ATON('209.207.224.40') |+-----------------------------+| 3520061480 |+-----------------------------+1 row in set (0.01 sec)

产生的数字总是按照网络字节顺序。如上面，数字按照 209×256³ + 207×256² + 224×256¹ + 40 ×256⁰ 进行计算。我们来验算下：

mysql> select 209*POW(256,3)+207*POW(256,2)+224*POW(256,1)+40*POW(256,0);+------------------------------------------------------------+| 209*POW(256,3)+207*POW(256,2)+224*POW(256,1)+40*POW(256,0) |+------------------------------------------------------------+| 3520061480 |+------------------------------------------------------------+1 row in set (0.05 sec)

INET_ATON() 也能理解短格式 IP 地址：

mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');+------------------------+--------------------+| INET_ATON('127.0.0.1') | INET_ATON('127.1') |+------------------------+--------------------+| 2130706433 | 2130706433 |+------------------------+--------------------+1 row in set (0.00 sec)

可以理解成为中间2为地址默认为0。

注：在存储由INET_ATON() 产生的值时，推荐你使用 INT UNSIGNED 列。假如你使用 (带符号) INT列, 则相应的第一个八位组大于127的IP 地址值会被截至 2147483647 (即, INET_ATON(’127.255.255.255′) 所返回的值)。当然，直接使用bigint更加省事。

INET_NTOA(expr)
给定一个数字网络地址 (4 或 8 比特),返回作为字符串的该地址的电地址表示。也就是inet_aton()的反函数。

mysql> SELECT INET_NTOA(3520061480);+-----------------------+| INET_NTOA(3520061480) |+-----------------------+| 209.207.224.40 |+-----------------------+1 row in set (0.00 sec)