SQLite中如何让INSERT的WHERE子句支持动态逐行判断？

我尝试使用带WHERE条件的INSERT语句，其中WHERE条件依赖于每条新插入的记录，比如WHERE NOT EXISTS (select ... from <待插入的表>)。但发现WHERE子句只会被评估一次，不会考虑已经插入的新记录。我知道INSERT OR IGNORE、INSERT OR UPDATE或UPSERT这类语法，但我的场景里WHERE条件比单纯验证键存在要复杂得多。核心问题是查询优化器会一次性评估WHERE子句，而不是逐行插入时结合已插入的记录判断。

有没有办法强制查询优化器在每条记录插入后立即进行判断？举个示例：我想从递归生成的数列中插入素数，WHERE条件是该数不能被表中已存在的素数整除，但下面的代码无法正常工作，会把19个数全部插入：

CREATE TABLE pnumbers (pnumber number primary key);

with r as 
(select 2 as n union all select n+1 as n from r where n < 20)
insert into pnumbers
select n from r where not exists 
(select pnumber from pnumbers pn where r.n % pn.pnumber = 0 );

补充：相反，DELETE .. WHERE工作正常且速度很快。下面的代码在我的游戏本上只用了90秒就从1000万连续数中剔除了非素数——但这不是我想要的实现方式：

delete from pnumbers  where exists (select pnumber from pnumbers pn2 
where pn2.pnumber <= sqrt(pnumbers.pnumber) and pnumbers.pnumber % pn2.pnumber = 0);  

原INSERT语句失效的原因 #

原语句中，CTE生成的数列和WHERE NOT EXISTS的判断都是基于插入前pnumbers表的状态（此时表是空的），所以所有数都会通过判断被插入。SQL的INSERT...SELECT逻辑是先完整计算出SELECT的结果集，再批量插入，不会在插入过程中实时更新判断条件。

可行的实现方法 #

1. 逐行循环插入
   用循环遍历每个待插入的数，每次插入前执行条件判断，确保每次判断都基于最新的表状态。以T-SQL为例：

   CREATE TABLE pnumbers (pnumber number primary key);
   
   DECLARE @n INT = 2;
   WHILE @n <= 20
   BEGIN
       IF NOT EXISTS (SELECT pnumber FROM pnumbers pn WHERE @n % pn.pnumber = 0)
       BEGIN
           INSERT INTO pnumbers VALUES (@n);
       END
       SET @n = @n + 1;
   END;
   

   这种方式的缺点是数据量大时速度较慢，但能严格保证每次判断都使用插入后的最新表数据。

2. 在CTE中直接生成素数再插入
   既然目标是生成素数，可以直接在CTE内部实现埃氏筛的逻辑，先筛选出所有符合条件的素数，再一次性插入表中，完全避免依赖插入过程中的表状态：

   CREATE TABLE pnumbers (pnumber number primary key);
   
   WITH r AS (
       SELECT 2 AS n
       UNION ALL
       SELECT n + 1 FROM r WHERE n < 20
   ),
   primes AS (
       SELECT n FROM r
       WHERE NOT EXISTS (
           SELECT 1 FROM r r2 
           WHERE r2.n < r.n AND r.n % r2.n = 0
       )
   )
   INSERT INTO pnumbers SELECT n FROM primes;
   

   这种方式效率比循环更高，且结果准确，适合批量生成素数的场景。

3. 封装为存储过程
   如果需要更复杂的自定义判断逻辑，可以把插入和判断逻辑封装成存储过程，在过程中逐行处理数据，确保每一步的判断都基于表的最新状态。

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