提取IPV4的前三个八位位组 [英] Extract 1st Three Octets of an IPV4

问题描述

假设有一个表只有一个字段.该表名为address，并具有一个名为ip的字段，其中包含IPV4地址作为其值

Let's say there is a table having only one field. Table is named address and has a field named ip which contains an IPV4 address as its value

样本数据

192.168.120.201
192.168.120.202
192.168.120.203
192.168.120.204
192.168.120.205
192.168.121.3
192.168.121.50

我需要在此表上运行查询，该查询将在前三个八位位组上返回数据COUNT

I need to run a query on this table which will return data COUNT on The First Three Octets

预期产量

网络数

192.168.120 5

192.168.120 5

192.168.121 3

192.168.121 3

我尝试使用SUBSTR之类的

SELECT SUBSTR(ip,1,10) as network,COUNT(*) as c FROM address GROUP BY network HAVING(c>1)

但是问题在于，如果所有的前三个八位位组每个都具有3位数字，则此SUBSTR仅能按预期工作，但是这会在前三个八位位组中每个都不具有3位数字的任何IP地址上中断.例如，这不适用于

But the problem is that this SUBSTR will only work as expected if all the first 3 Octets have 3 digits each, but this will break on any ip address which does not have 3 digits each in first three octets. For example this will not work for

192.168.0.0

192.168.0.0

192.2.3.50

192.2.3.50

192.23.4.60

192.23.4.60

问题

上述查询是否还有其他替代方法可以在上述所有情况下使用?

Is there any alternate to the above query which will work in all the cases above?

推荐答案

不执行字符串操作.您最好将IP转换为整数并使用一些位掩码，例如

Don't do string operations. you'd be better off converting the IPs to ints and using some bitmask, e.g.

SELECT INET_NTOA(INET_ATON(ipfield) & 0xFFFFFF00)

这篇关于提取IPV4的前三个八位位组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！