SQL Server无匹配行时的条件WHERE子句及IP掩码匹配查询

首先，假设你的表结构是这样的（如果实际结构不同，调整列名即可）：

CREATE TABLE IPRules (
    ID INT PRIMARY KEY IDENTITY,
    IPPattern VARCHAR(15) NOT NULL -- 存储精确IP或带*的掩码IP，比如'192.168.99.5'、'192.168.99.*'、'192.*.*.7'
);

1. 核心IP匹配逻辑 #

我们可以利用PARSENAME函数拆分IP的四个段，然后逐段对比模式和查询IP的对应段：如果模式段是*，则匹配任意值；否则必须和查询IP的对应段完全相等。

以下是带参数的查询示例，假设要查询的IP是@QueryIP：

DECLARE @QueryIP VARCHAR(15) = '192.168.99.65';

SELECT *
FROM IPRules
WHERE 
    -- 匹配第一段（注意PARSENAME从右往左数，所以第4段是IP的第一个部分）
    (PARSENAME(IPPattern, 4) = '*' OR PARSENAME(IPPattern, 4) = PARSENAME(@QueryIP, 4))
    -- 匹配第二段
    AND (PARSENAME(IPPattern, 3) = '*' OR PARSENAME(IPPattern, 3) = PARSENAME(@QueryIP, 3))
    -- 匹配第三段
    AND (PARSENAME(IPPattern, 2) = '*' OR PARSENAME(IPPattern, 2) = PARSENAME(@QueryIP, 2))
    -- 匹配第四段
    AND (PARSENAME(IPPattern, 1) = '*' OR PARSENAME(IPPattern, 1) = PARSENAME(@QueryIP, 1));

这个查询会精准匹配所有符合掩码规则的条目，比如你提到的192.168.99.*会匹配192.168.99.65，192.*.*.7会匹配192.100.200.7等。

2. 无匹配行的处理 #

如果没有找到任何匹配的IP规则，你可能需要返回默认结果或者处理空集的情况。这里提供两种常见的处理方式：

方式一：返回默认值（如果无匹配） #

可以用UNION ALL结合NOT EXISTS来实现，当没有匹配时返回预设的默认行：

DECLARE @QueryIP VARCHAR(15) = '10.0.0.1';

-- 先查询匹配的规则
SELECT ID, IPPattern
FROM IPRules
WHERE 
    (PARSENAME(IPPattern, 4) = '*' OR PARSENAME(IPPattern, 4) = PARSENAME(@QueryIP, 4))
    AND (PARSENAME(IPPattern, 3) = '*' OR PARSENAME(IPPattern, 3) = PARSENAME(@QueryIP, 3))
    AND (PARSENAME(IPPattern, 2) = '*' OR PARSENAME(IPPattern, 2) = PARSENAME(@QueryIP, 2))
    AND (PARSENAME(IPPattern, 1) = '*' OR PARSENAME(IPPattern, 1) = PARSENAME(@QueryIP, 1))

-- 如果没有匹配，返回默认行
UNION ALL
SELECT -1 AS ID, '无匹配规则' AS IPPattern
WHERE NOT EXISTS (
    SELECT 1
    FROM IPRules
    WHERE 
        (PARSENAME(IPPattern, 4) = '*' OR PARSENAME(IPPattern, 4) = PARSENAME(@QueryIP, 4))
        AND (PARSENAME(IPPattern, 3) = '*' OR PARSENAME(IPPattern, 3) = PARSENAME(@QueryIP, 3))
        AND (PARSENAME(IPPattern, 2) = '*' OR PARSENAME(IPPattern, 2) = PARSENAME(@QueryIP, 2))
        AND (PARSENAME(IPPattern, 1) = '*' OR PARSENAME(IPPattern, 1) = PARSENAME(@QueryIP, 1))
);

方式二：判断是否存在匹配（用于逻辑分支） #

如果你需要在存储过程或脚本中根据是否有匹配执行不同逻辑，可以用EXISTS先判断：

DECLARE @QueryIP VARCHAR(15) = '10.0.0.1';
DECLARE @HasMatch BIT = 0;

IF EXISTS (
    SELECT 1
    FROM IPRules
    WHERE 
        (PARSENAME(IPPattern, 4) = '*' OR PARSENAME(IPPattern, 4) = PARSENAME(@QueryIP, 4))
        AND (PARSENAME(IPPattern, 3) = '*' OR PARSENAME(IPPattern, 3) = PARSENAME(@QueryIP, 3))
        AND (PARSENAME(IPPattern, 2) = '*' OR PARSENAME(IPPattern, 2) = PARSENAME(@QueryIP, 2))
        AND (PARSENAME(IPPattern, 1) = '*' OR PARSENAME(IPPattern, 1) = PARSENAME(@QueryIP, 1))
)
BEGIN
    SET @HasMatch = 1;
    -- 有匹配时的逻辑，比如返回匹配结果
    SELECT * FROM IPRules WHERE ...;
END
ELSE
BEGIN
    SET @HasMatch = 0;
    -- 无匹配时的逻辑，比如插入默认规则或返回提示
    PRINT '未找到匹配的IP规则';
END

注意事项 #

• PARSENAME函数适用于标准的点分隔IP地址，如果你的IPPattern有非标准格式（比如多个连续点、空段），需要先做格式校验。
• 如果你的SQL Server版本低于2012，PARSENAME依然可用，但如果需要更复杂的拆分逻辑，可以用字符串函数（比如CHARINDEX、SUBSTRING）来手动拆分IP段。
• 为了提高查询性能，可以考虑对IPPattern的各段建立计算列，然后创建索引，比如：

  ALTER TABLE IPRules
  ADD IP1 AS CASE WHEN PARSENAME(IPPattern,4) = '*' THEN '*' ELSE PARSENAME(IPPattern,4) END PERSISTED;
  -- 同理创建IP2、IP3、IP4计算列，然后创建复合索引
  CREATE NONCLUSTERED INDEX IX_IPRules_IPSegments ON IPRules(IP1, IP2, IP3, IP4);
  

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