MySQL中如何查询指定IP所属的CIDR格式IP网段

嘿，我完全懂你的需求——你把CIDR格式的网段存在varchar字段里，想找出包含指定IP的那行数据，但之前找到的用起止IP匹配的方案根本不适用，对吧？别担心，我给你分不同数据库整理了可行的SQL写法，你按需选用就行：

MySQL 解决方案 #

如果你的MySQL版本是8.0.12及以上，那直接用官方提供的INET_CIDR_TO_RANGE()函数就很方便，它能把CIDR网段转换成起始IP-结束IP的格式，我们只需要把指定IP转成数值后判断是否在这个区间内：

SELECT range 
FROM ipranges 
WHERE INET_ATON('195.124.199.201') BETWEEN 
    INET_ATON(SUBSTRING_INDEX(INET_CIDR_TO_RANGE(range), '-', 1)) 
    AND INET_ATON(SUBSTRING_INDEX(INET_CIDR_TO_RANGE(range), '-', -1));

要是你的MySQL版本更低，就得手动计算网段的起止IP了，用位运算来实现：

SELECT range 
FROM ipranges
WHERE 
    -- 把目标IP转成数值
    INET_ATON('195.124.199.201') >= INET_ATON(SUBSTRING_INDEX(range, '/', 1))
    -- 计算网段的结束IP并转成数值，再做比较
    AND INET_ATON('195.124.199.201') <= INET_ATON(
        CONCAT(
            SUBSTRING_INDEX(SUBSTRING_INDEX(range, '/', 1), '.', 1), '.',
            SUBSTRING_INDEX(SUBSTRING_INDEX(range, '/', 1), '.', 2), '.',
            SUBSTRING_INDEX(SUBSTRING_INDEX(range, '/', 1), '.', 3), '.',
            (SUBSTRING_INDEX(SUBSTRING_INDEX(range, '/', 1), '.', -1) | ((1 << (32 - SUBSTRING_INDEX(range, '/', -1))) - 1)) & 255
        )
    );

PostgreSQL 解决方案 #

PostgreSQL对CIDR格式的支持非常友好，哪怕你的字段是varchar类型，也可以直接转成cidr类型后用<<=操作符（表示IP属于该网段）：

SELECT range 
FROM ipranges 
WHERE '195.124.199.201'::inet <<= range::cidr;

小建议：如果能把range字段的类型改成cidr，不仅查询更简洁，还能建更高效的索引，提升大数据量下的查询速度：

-- 修改字段类型
ALTER TABLE ipranges ALTER COLUMN range TYPE cidr USING range::cidr;
-- 之后的查询会更简单
SELECT range 
FROM ipranges 
WHERE '195.124.199.201'::inet <<= range;

SQL Server 解决方案 #

SQL Server需要通过拆分CIDR字符串，结合位运算来判断IP是否在网段内：

SELECT range
FROM ipranges
WHERE 
    -- 把目标IP转成数值
    CAST(PARSENAME('195.124.199.201', 4) AS BIGINT) * POWER(2,24) +
    CAST(PARSENAME('195.124.199.201', 3) AS BIGINT) * POWER(2,16) +
    CAST(PARSENAME('195.124.199.201', 2) AS BIGINT) * POWER(2,8) +
    CAST(PARSENAME('195.124.199.201', 1) AS BIGINT)
    BETWEEN
    -- 计算网段起始IP的数值
    (CAST(PARSENAME(SUBSTRING(range, 1, CHARINDEX('/', range)-1), 4) AS BIGINT) * POWER(2,24) +
    CAST(PARSENAME(SUBSTRING(range, 1, CHARINDEX('/', range)-1), 3) AS BIGINT) * POWER(2,16) +
    CAST(PARSENAME(SUBSTRING(range, 1, CHARINDEX('/', range)-1), 2) AS BIGINT) * POWER(2,8) +
    CAST(PARSENAME(SUBSTRING(range, 1, CHARINDEX('/', range)-1), 1) AS BIGINT))
    AND
    -- 计算网段结束IP的数值
    (CAST(PARSENAME(SUBSTRING(range, 1, CHARINDEX('/', range)-1), 4) AS BIGINT) * POWER(2,24) +
    CAST(PARSENAME(SUBSTRING(range, 1, CHARINDEX('/', range)-1), 3) AS BIGINT) * POWER(2,16) +
    CAST(PARSENAME(SUBSTRING(range, 1, CHARINDEX('/', range)-1), 2) AS BIGINT) * POWER(2,8) +
    CAST(PARSENAME(SUBSTRING(range, 1, CHARINDEX('/', range)-1), 1) AS BIGINT)) |
    (POWER(2, 32 - CAST(SUBSTRING(range, CHARINDEX('/', range)+1, LEN(range)) AS INT)) - 1);

额外提示 #

如果你的表数据量较大，建议：

• 把IP和网段转成数值类型存储（比如BIGINT），减少查询时的计算量
• 给转换后的数值字段或者CIDR字段建立索引，能大幅提升查询效率

内容的提问来源于stack exchange，提问作者Askerman