字符串型IP地址从小到大排序

Question

数据库有IP一列，我想取出时按从小到大排列，但因为是字符串型，排列结果变成了
x.x.x.1
x.x.x.10
x.x.x.11
x.x.x.2.....
有没有办法让字符串像数字一样从小到大排列？或者取出后在java中有什么办法？

Answer 1

Postgres本来就有IP类型，设计的时候就应该用上作为字段类型Anyway，既然你提到了这个问题，那就把它转化为IP类型再排序即可order by cast(ip as inet)

Answer 2

转成整数后排序。另外建议对于IP这种字段，直接在数据库里面存整数而不是字符串给你个转换的例子：&nbsp; &nbsp; public long ipToLong(String ipAddress) {&nbsp; &nbsp; &nbsp; &nbsp; long result = 0;&nbsp; &nbsp; &nbsp; &nbsp; String[] ipAddressInArray = ipAddress.split("\\.");&nbsp; &nbsp; &nbsp; &nbsp; for (int i = 3; i >= 0; i--) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; long ip = Long.parseLong(ipAddressInArray[3 - i]);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; result |= ip << (i * 8);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; return result;&nbsp; &nbsp; }&nbsp; &nbsp; public String longToIp(long ip) {&nbsp; &nbsp; &nbsp; &nbsp; StringBuilder sb = new StringBuilder(15);&nbsp; &nbsp; &nbsp; &nbsp; for (int i = 0; i < 4; i++) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sb.insert(0, Long.toString(ip & 0xff));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (i < 3) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sb.insert(0, '.');&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ip = ip >> 8;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; return sb.toString();&nbsp; &nbsp; }

Answer 3

可以先将Ip地址转为int类型，再存入数据库。或者数据库中的字符串取到Java中转成Long再比较。Ip地址又四个0-255组成，每个部分1Byte，四个部分刚好4Byte，也就是一个Integer。

Answer 4

思路:order by每个字段order by to_number(substr(ip, 1, instr(ip,'.',1,1)-1)),&nbsp; to_number(substr(ip,instr(ip, '.',1,1)+1,instr(ip, '.',1,2)-instr(ip, '.',1,1)-1)),&nbsp; to_number(substr(ip,instr(ip, '.',1,2)+1,instr(ip, '.',1,3)-instr(ip, '.',1,2)-1)),&nbsp; to_number(substr(ip,instr(ip, '.',1,3)+1,length(ip)-instr(ip, '.',1,3)));

Answer 5

思路是把IP转成整数后排序:<?php$ip = array('127.0.0.3','127.0.0.1','127.0.0.2');foreach($ip as $k => $v) {&nbsp; &nbsp; $ip[$k] = ip2long($v);}sort($ip);var_export($ip);foreach($ip as $k => $v) {&nbsp; &nbsp; echo long2ip($v)."\n";}//输出array (&nbsp; 0 => 2130706433,&nbsp; 1 => 2130706434,&nbsp; 2 => 2130706435,)127.0.0.1127.0.0.2127.0.0.3