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

Question

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

Answer 1

Postgres already has an IP type, which should be used as a field type during design

Anyway, since you mentioned this problem, just convert it into IP type and sort it

order by cast(ip as inet)

Answer 2

Convert to integer and then sort. It is also recommended that for fields such as IP, store integers directly in the database instead of strings

Give you an example of conversion:

    public long ipToLong(String ipAddress) {
        long result = 0;
        String[] ipAddressInArray = ipAddress.split("\.");

        for (int i = 3; i >= 0; i--) {
            long ip = Long.parseLong(ipAddressInArray[3 - i]);
            result |= ip << (i * 8);
        }

        return result;
    }

    public String longToIp(long ip) {
        StringBuilder sb = new StringBuilder(15);

        for (int i = 0; i < 4; i++) {
            sb.insert(0, Long.toString(ip & 0xff));
            if (i < 3) {
                sb.insert(0, '.');
            }
            ip = ip >> 8;
        }

        return sb.toString();
    }

Answer 3

You can first convert the IP address to int type and then store it in the database. Or take the string in the database and convert it into a Long in Java and then compare.

The IP address consists of four parts 0-255, each part is 1Byte, and the four parts are exactly 4Byte, which is an Integer.

Answer 4

shell can be sorted:

➜  ~ cat 1.txt
1.1.1.5
5.5.5.5
10.1.1.1
2.1.1.1
➜  ~ cat 1.txt|sort -n
1.1.1.5
2.1.1.1
5.5.5.5
10.1.1.1
➜  ~

Sort in vim:

:1,$!sort -n

Finally, I’ll give you a python version: you can modify it in Java:

#!/usr/local/bin/python
#-*- coding: UTF-8 -*-
import re
#打开文件
fp=open("ip.txt")
content=fp.readlines()
# print(len(content))
ip_str=''
#p=re.compile("(\d{1,3})\.(\d{1,3}).(\d{1,3})\.(\d{1,3})")
c=list()
for i in range(len(content)):
    c.append(content[i].replace('\n',''))
def ip2int(s):
    l = [int(i) for i in s.split('.')]     
    return (l[0] << 24) | (l[1] << 16) | (l[2] << 8) | l[3]   

c.sort(lambda x, y: cmp(ip2int(x), ip2int(y))) 
print(c)
#重构换行
for j in range(len(c)):
    c[j]=c[j]+'\n'
#写入到本地
succ_fp=open("ip_sort.txt","a+")
succ_fp.writelines(c)
succ_fp.close()
print("排序成功")

Answer 5

Convert it to int and then sort it

package org.plyy.utils;

public class IPUtil {
    
    /**
     * @param ip EG:192.168.1.1
     * @return  EG:0xc0a80101
     */
    public static int ipToInteger(String ip) {
        int result = 0;
        String ipArr[] = ip.split("\.");
        for(int i = 0; i < 4; i++) {
            result |= Integer.parseInt(ipArr[i]) << ((3-i) * 8);
        }
        return result;
    }
    
    /**
     * 
     * @param ip EG:0xc0a80101
     * @return EG:192.168.1.1
     */
    public static String intToIp(int ip) {
        StringBuilder sb = new StringBuilder();
        for(int i = 0; i < 4; i++) {
            sb.append(String.valueOf((ip >>> (3-i) * 8) & 0xFF)).append(".");
        }
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }
    
    public static void main(String[] args) {
        int result = ipToInteger("192.168.1.1");
        System.out.println(Integer.toHexString(result));
        System.out.println("-------------------------");
        String result2 = intToIp(0xc0a80101);
        System.out.println(result2);
    }
    
}

Answer 6

Idea: order by each field

order by to_number(substr(ip, 1, instr(ip,'.',1,1)-1)),
  to_number(substr(ip,instr(ip, '.',1,1)+1,instr(ip, '.',1,2)-instr(ip, '.',1,1)-1)),
  to_number(substr(ip,instr(ip, '.',1,2)+1,instr(ip, '.',1,3)-instr(ip, '.',1,2)-1)),
  to_number(substr(ip,instr(ip, '.',1,3)+1,length(ip)-instr(ip, '.',1,3)));

Answer 7

The idea is to convert the IP into an integer and sort it:

<?php
$ip = array('127.0.0.3','127.0.0.1','127.0.0.2');
foreach($ip as $k => $v) {
    $ip[$k] = ip2long($v);
}
sort($ip);
var_export($ip);
foreach($ip as $k => $v) {
    echo long2ip($v)."\n";
}
//输出
array (
  0 => 2130706433,
  1 => 2130706434,
  2 => 2130706435,
)
127.0.0.1
127.0.0.2
127.0.0.3