在SQL中按IP地址排序的最佳方法

Question

我使用PDO通过SQLITE3连接到我的SpiceWorks数据库。我正在尝试按IP地址顺序显示设备列表。以下是我当前的查询：

SELECT name, ip_address FROM `devices` ORDER BY ip_address

这样做的问题是，它的组织方式很奇怪：

​

​

有什么简单的方法可以解决这个问题吗？

我不可能编辑数据库，因为它会通过SpiceWorks关闭。我需要一种在SQL中做到这一点的方法。

Answer 1

你有没有尝试过INET_ATON函数？这可能是一个迟来的答案，但可能会对其他人有所帮助。

SELECT name, ip_address
FROM devices
ORDER BY
INET_ATON(ip_address)

Answer 2

我已经实现了如下代码：

SELECT IP FROM iplist ORDER BY

CAST(substr(trim(IP),1,instr(trim(IP),'.')-1) AS INTEGER),  

   CAST(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')-1) AS INTEGER), 

        CAST(substr(substr(trim(IP),length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')-1) AS INTEGER), 

        CAST(substr(trim(IP),length(substr(substr(trim(IP),length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+ length(substr(trim(IP),1,instr(trim(IP),'.')))+length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+1,length(trim(IP))) AS INTEGER)

Answer 3

ORDER BY
CAST(substr(ip_address,1,instr(ip_address,'.')) AS NUMERIC),
CAST(substr(ip_address,instr(ip_address,'.'), instr(substr(ip_address,instr(ip_address,'.')))) AS NUMERIC),

像这样的东西应该是可行的。不过那会很糟糕的。(此应按前两个八位字节进行排序...)