SQLite中UNIQUE约束加ON CONFLICT IGNORE仍重复插入记录问题求助

嘿，这个问题其实是踩了SQL里NULL的一个经典坑！

核心原因在于SQL标准对NULL的特殊定义，SQLite严格遵循了这个规则：

• NULL在SQL里代表“未知值”，所以**NULL不等于任何值，包括另一个NULL**。换句话说，NULL = NULL的结果既不是true也不是false，而是NULL（未知状态）。
• 你的唯一约束UNIQUE (S, SS, HID, ISH)会检查新记录和已有记录的所有列是否完全相等，但由于两条记录的S列都是NULL，SQLite不认为这两个NULL是相等的，所以判定这两条记录不违反唯一约束，自然不会触发ON CONFLICT IGNORE，两条都插入了。

来验证一下这个逻辑 #

你可以执行这条查询，直观看看SQL对这两个NULL的判定：

SELECT 
    (S1.S = S2.S) AS s_column_compare,
    (S1.SS = S2.SS) AS ss_column_compare,
    (S1.HID = S2.HID) AS hid_column_compare,
    (S1.ISH = S2.ISH) AS ish_column_compare
FROM SEARCH S1, SEARCH S2
WHERE S1.rowid != S2.rowid;

结果里的s_column_compare列会显示NULL，而不是true——这就说明SQLite确实不认为两个NULL是相等的。

怎么解决这个问题？ #

如果你希望NULL被视为“相同值”来触发唯一约束，可以试试这两种方案：

1. 给S列设置默认值，比如空字符串''，这样插入时如果没给S赋值，就会用默认值，两次插入的空字符串会被判定为相等：

   CREATE TABLE `SEARCH` ( 
       `S` TEXT DEFAULT '', 
       `SS` TEXT, 
       `HID` INTEGER, 
       `ISH` BOOL, 
       UNIQUE (`S`, `SS`, `HID`, `ISH`) ON CONFLICT IGNORE 
   );
   

2. 在唯一约束里使用COALESCE函数转换NULL，把NULL统一替换成一个特定值（比如空字符串），这样不管输入是NULL还是这个特定值，都会被当成同一个值处理：

   CREATE TABLE `SEARCH` ( 
       `S` TEXT, 
       `SS` TEXT, 
       `HID` INTEGER, 
       `ISH` BOOL, 
       UNIQUE (COALESCE(S, ''), SS, HID, ISH) ON CONFLICT IGNORE 
   );
   

这样再执行那两条INSERT语句，第二条就会被ON CONFLICT IGNORE忽略了。

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