You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

查询VIEW返回4行，但INSERT INTO...SELECT仅插入2行的问题排查

avatar
API 探路者
2026-6-27
IP合并视图查询返回4条记录，但插入目标表仅成功2条的排查与解决

问题背景

原表mit_service_collapsed包含4条无CIDR交集的记录，创建collapsed视图用于IP范围合并，直接查询视图能返回4条记录，但执行插入语句将视图数据导入同结构目标表mit_service_collapsed_second时，仅成功插入2条记录，无任何警告或报错。

相关结构与数据

原表结构

CREATE TABLE `mit_service_collapsed` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(16) NOT NULL DEFAULT 'empty',
  `cidr` varchar(5) NOT NULL DEFAULT 'empty',
  `type` varchar(20) NOT NULL DEFAULT 'empty',
  `ioc` text,
  `time` timestamp NULL DEFAULT NULL,
  `reason` text,
  `asn` varchar(30) NOT NULL DEFAULT 'empty',
  `city` varchar(100) NOT NULL DEFAULT 'empty',
  `country` varchar(100) NOT NULL DEFAULT 'empty',
  `country_iso` varchar(10) NOT NULL DEFAULT 'empty',
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `delete_reason` text,
  `delete_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

原表数据

mysql> select * from mit_service_collapsed;
+----+--------------+------+-------+---------------------------+---------------------+-------------+-------+--------------+---------------+-------------+------------------+---------------+-------------+
| id | ip           | cidr | type  | ioc                       | time                | reason      | asn   | city         | country       | country_iso | deleted          | delete_reason | delete_time |
+----+--------------+------+-------+---------------------------+---------------------+-------------+-------+--------------+---------------+-------------+------------------+---------------+-------------+
|  1 | 100.11.12.20 | 30   | range | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | AS701 | Collegeville | United States | US          | 0x00             | NULL          | NULL        |
|  3 | 100.11.12.28 | 31   | range | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | AS701 | Collegeville | United States | US          | 0x00             | NULL          | NULL        |
|  4 | 100.11.12.24 | 31   | cidr  | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | AS701 | Collegeville | United States | US          | 0x00             | NULL          | NULL        |
|  5 | 100.11.12.27 | 32   | cidr  | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | AS701 | Collegeville | United States | US          | 0x00             | NULL          | NULL        |
+----+--------------+------+-------+---------------------------+---------------------+-------------+-------+--------------+---------------+-------------+------------------+---------------+-------------+
4 rows in set (0,00 sec)

视图查询结果

mysql> select * from collapsed;
+--------------+------+-------+---------------------------+---------------------+-------------+--------------+---------------+-------------+-------+
| ip           | cidr | type  | ioc                       | time                | reason      | city         | country       | country_iso | asn   |
+--------------+------+-------+---------------------------+---------------------+-------------+--------------+---------------+-------------+-------+
| 100.11.12.20 | 30   | range | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | Collegeville | United States | US          | AS701 |
| 100.11.12.24 | 31   | cidr  | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | Collegeville | United States | US          | AS701 |
| 100.11.12.27 | 32   | cidr  | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | Collegeville | United States | US          | AS701 |
| 100.11.12.28 | 31   | range | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | Collegeville | United States | US          | AS701 |
+--------------+------+-------+---------------------------+---------------------+-------------+--------------+---------------+-------------+-------+
4 rows in set (0,01 sec)

插入操作及结果

mysql> insert into mit_service_collapsed_second 
   (ip,cidr,type,ioc,time,reason,city,country,country_iso,asn)
  select ip,cidr,type,ioc,time,reason,city,country,country_iso,asn 
  from collapsed;
Query OK, 2 rows affected (0,01 sec)
Records: 2  Duplicates: 0  Warnings: 0

目标表结果

mysql> select * from mit_service_collapsed_second;
+----+--------------+------+-------+---------------------------+---------------------+-------------+-------+--------------+---------------+-------------+------------------+---------------+-------------+
| id | ip           | cidr | type  | ioc                       | time                | reason      | asn   | city         | country       | country_iso | deleted          | delete_reason | delete_time |
+----+--------------+------+-------+---------------------------+---------------------+-------------+-------+--------------+---------------+-------------+------------------+---------------+-------------+
|  1 | 100.11.12.20 | 30   | range | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | AS701 | Collegeville | United States | US          | 0x00             | NULL          | NULL        |
|  2 | 100.11.12.24 | 31   | cidr  | 100.11.12.20-100.11.12.30 | 2024-03-31 15:50:59 | some_record | AS701 | Collegeville | United States | US          | 0x00             | NULL          | NULL        |
+----+--------------+------+-------+---------------------------+---------------------+-------------+-------+--------------+---------------+-------------+------------------+---------------+-------------+
2 rows in set (0,00 sec)

目标表结构

CREATE TABLE `mit_service_collapsed_second` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(16) NOT NULL DEFAULT 'empty',
  `cidr` varchar(5) NOT NULL DEFAULT 'empty',
  `type` varchar(20) NOT NULL DEFAULT 'empty',
  `ioc` text,
  `time` timestamp NULL DEFAULT NULL,
  `reason` text,
  `asn` varchar(30) NOT NULL DEFAULT 'empty',
  `city` varchar(100) NOT NULL DEFAULT 'empty',
  `country` varchar(100) NOT NULL DEFAULT 'empty',
  `country_iso` varchar(10) NOT NULL DEFAULT 'empty',
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `delete_reason` text,
  `delete_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

视图定义

CREATE VIEW collapsed AS
WITH
cte1 AS (                                -- 计算IP范围起始值和范围大小
  SELECT DISTINCT
     id,
     ip,
     cidr,
     type,
     ioc,
     time,
     reason,
     city, 
     country,
     country_iso,
     asn,
     ( ( 0xFFFFFFFF << (32 - cidr) ) & INET_ATON(ip) ) AS range_start,        
     pow(2,(32 - cidr)) AS ip_range              
  FROM  mit_service_collapsed 
  WHERE deleted=b'0'                       
  ),
cte2 AS (                                -- 计算IP范围结束值
  SELECT ip,
     cidr,
     type,
     ioc,
     time,
     reason,     
     city, 
     country,
     country_iso,
     asn,
     range_start,
     if(ip_range!=1,range_start+ip_range-1,range_start) AS range_end     
  FROM cte1
  ORDER BY range_start
  ),
cte3 AS (                                -- 计算累计最大范围结束值
  SELECT ip,
     cidr,
     type,
     ioc,
     time,
     reason,     
     city,
     country,
     country_iso,
     asn,
     range_start,
     range_end, 
     MAX(range_end) OVER(ROWS UNBOUNDED PRECEDING) AS max_end
  FROM cte2
  ),
cte4 AS (                                -- 按累计最大结束值分组，取最小范围起始值
  SELECT ip,
     cidr,
     type,
     ioc,
     time,
     reason,     
     country,
     city,
     country_iso,
     asn,
     range_start,
     range_end,
     MIN(range_start) OVER(PARTITION BY max_end) AS min_start,
     max_end
  FROM cte3
  ORDER BY cidr
  ),                               
 cte5 AS (
     SELECT ip,     
     cidr,
     type,
     ioc, 
     time,
     reason,     
     country,
     city,
     country_iso,
     asn,
     range_start,
     range_end,
     min_start,
     MAX(range_end) OVER(PARTITION BY range_start) AS max_max_end
FROM cte4)
SELECT ip,      
    cidr,
    type,
    ioc,
    time,
    reason,     
    city,
    country,
    country_iso,
    asn
  FROM cte5
WHERE range_start=min_start AND max_max_end=range_end;

排查原因

核心问题出在CTE排序失效导致窗口函数计算错误：

  1. MySQL中，CTE内的ORDER BY如果不搭配LIMIT，不会强制保留排序顺序，数据库会根据执行计划自行调整行顺序，因此cte2中的ORDER BY range_start实际上是无效的。
  2. cte3中的窗口函数MAX(range_end) OVER(ROWS UNBOUNDED PRECEDING)是累计最大值，依赖行的顺序。当cte2的行顺序随机时，累计最大值计算会出错：
    • 若行顺序为1→3→4→5，记录3的range_end（167838429）会成为后续记录的累计最大值，导致记录4、5被归到同一个max_end分组中，其min_start取该分组的最小range_start（167838424），与自身range_start不匹配，最终被过滤。
  3. 直接查询视图时，MySQL可能临时使用了预期的排序，返回4条记录；但执行插入时，优化器选择了不同的执行计划，导致排序失效，最终仅保留2条符合过滤条件的记录。

解决方法

方法一：给CTE添加LIMIT强制保留排序

修改cte2，添加一个极大的LIMIT值（覆盖所有记录），确保排序生效：

cte2 AS (                                -- 计算IP范围结束值
  SELECT ip,
     cidr,
     type,
     ioc,
     time,
     reason,     
     city, 
     country,
     country_iso,
     asn,
     range_start,
     if(ip_range!=1,range_start+ip_range-1,range_start) AS range_end     
  FROM cte1
  ORDER BY range_start
  LIMIT 18446744073709551615 -- MySQL最大无符号整数，确保所有记录被保留
),

方法二：在窗口函数中指定排序

修改cte3的窗口函数，直接在窗口内指定排序规则，无需依赖CTE的排序：

cte3 AS (                                -- 计算累计最大范围结束值
  SELECT ip,
     cidr,
     type,
     ioc,
     time,
     reason,     
     city,
     country,
     country_iso,
     asn,
     range_start,
     range_end, 
     MAX(range_end) OVER(ORDER BY range_start ROWS UNBOUNDED PRECEDING) AS max_end
  FROM cte2
),

验证与插入

修改视图后，重新查询collapsed视图确认返回4条记录，再执行插入操作即可成功导入全部4条数据。

补充验证

执行以下语句查看CTE各字段的计算结果，确认过滤条件是否对所有记录生效：

WITH
cte1 AS (                                
  SELECT DISTINCT
     id,
     ip,
     cidr,
     type,
     ioc,
     time,
     reason,
     city, 
     country,
     country_iso,
     asn,
     ( ( 0xFFFFFFFF << (32 - cidr) ) & INET_ATON(ip) ) AS range_start,        
     pow(2,(32 - cidr)) AS ip_range              
  FROM  mit_service_collapsed 
  WHERE deleted=b'0'                       
  ),
cte2 AS (                                
  SELECT ip,
     cidr,
     type,
     ioc,
     time,
     reason,     
     city, 
     country,
     country_iso,
     asn,
     range_start,
     if(ip_range!=1,range_start+ip_range-1,range_start) AS range_end     
  FROM cte1
  ORDER BY range_start
  LIMIT 18446744073709551615
  ),
cte3 AS (                                
  SELECT ip,
     cidr,
     type,
     ioc,
     time,
     reason,     
     city,
     country,
     country_iso,
     asn,
     range_start,
     range_end, 
     MAX(range_end) OVER(ORDER BY range_start ROWS UNBOUNDED PRECEDING) AS max_end
  FROM cte2
  ),
cte4 AS (                                
  SELECT ip,
     cidr,
     type,
     ioc,
     time,
     reason,     
     country,
     city,
     country_iso,
     asn,
     range_start,
     range_end,
     MIN(range_start) OVER(PARTITION BY max_end) AS min_start,
     max_end
  FROM cte3
  ORDER BY cidr
  ),                               
 cte5 AS (
     SELECT ip,     
     cidr,
     type,
     ioc, 
     time,
     reason,     
     country,
     city,
     country_iso,
     asn,
     range_start,
     range_end,
     min_start,
     MAX(range_end) OVER(PARTITION BY range_start) AS max_max_end
FROM cte4)
SELECT *, range_start=min_start AS start_match, max_max_end=range_end AS end_match
  FROM cte5;

若所有记录的start_match和end_match均为1，说明过滤条件会保留

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.06.27 00:13:16