小巧。快速。可靠。
三选二。

• 首页
• 菜单
• 关于
• 文档
• 下载
• 许可证
• 支持
• 购买
• 搜索

• 关于
• 文档
• 下载
• 支持
• 购买

Search Documentation Search Changelog

SQLite 与其他数据库引擎的 NULL 处理对比

目标是使 SQLite 以符合标准的方式处理 NULL。但 SQL 标准中关于如何处理 NULL 的描述似乎模棱两可。从标准文档中无法明确在所有情况下如何处理 NULL。

因此，而不是遵循标准文档，我们测试了各种流行的 SQL 引擎如何处理 NULL。我们的想法是让 SQLite 的工作方式与其他所有引擎相同。一个 SQL 测试脚本由志愿者在各种 SQL RDBMS 上运行，并且这些测试的结果被用来推断每个引擎如何处理 NULL 值。最初的测试于 2002 年 5 月运行。此文档末尾提供了测试脚本的副本。

SQLite 最初的编码方式使得下表中所有问题的答案都是“是”。但是，对其他 SQL 引擎运行的实验表明，没有一个引擎以这种方式工作。因此，SQLite 被修改为与 Oracle、PostgreSQL 和 DB2 工作方式相同。这涉及到将 NULL 对于 SELECT DISTINCT 语句和 SELECT 中的 UNION 运算符而言视为不 distinct。在 UNIQUE 列中，NULL 仍然是 distinct 的。这看起来有些随意，但与其他引擎兼容的愿望超过了这一反对意见。

可以使 SQLite 将 NULL 对于 SELECT DISTINCT 和 UNION 而言视为 distinct。为此，应更改sqliteInt.h源文件中的 NULL_ALWAYS_DISTINCT #define 的值，然后重新编译。

2003-07-13 更新：自从本文档最初编写以来，一些测试的数据库引擎已经更新，并且用户已经非常友好地向下面的图表发送了更正。原始数据显示了各种各样的行为，但随着时间的推移，行为范围已收敛到 PostgreSQL/Oracle 模型。唯一的显著区别是 Informix 和 MS-SQL 都将 NULL 在 UNIQUE 列中视为不 distinct。

NULL 对于 UNIQUE 列是 distinct 的，但对于 SELECT DISTINCT 和 UNION 是不 distinct 的，这一事实仍然令人费解。似乎 NULL 应该在任何地方都是 distinct 的，或者都不 distinct。并且 SQL 标准文档建议 NULL 在任何地方都应该是 distinct 的。然而，在撰写本文时，没有一个测试过的 SQL 引擎在 SELECT DISTINCT 语句或 UNION 中将 NULL 视为 distinct。

下表显示了 NULL 处理实验的结果。

以下脚本用于收集上表的信息。

-- I have about decided that SQL's treatment of NULLs is capricious and cannot be
-- deduced by logic.  It must be discovered by experiment.  To that end, I have 
-- prepared the following script to test how various SQL databases deal with NULL.
-- My aim is to use the information gathered from this script to make SQLite as
-- much like other databases as possible.
--
-- If you could please run this script in your database engine and mail the results
-- to me at [email protected], that will be a big help.  Please be sure to identify the
-- database engine you use for this test.  Thanks.
--
-- If you have to change anything to get this script to run with your database
-- engine, please send your revised script together with your results.
--

-- Create a test table with data
create table t1(a int, b int, c int);
insert into t1 values(1,0,0);
insert into t1 values(2,0,1);
insert into t1 values(3,1,0);
insert into t1 values(4,1,1);
insert into t1 values(5,null,0);
insert into t1 values(6,null,1);
insert into t1 values(7,null,null);

-- Check to see what CASE does with NULLs in its test expressions
select a, case when b<>0 then 1 else 0 end from t1;
select a+10, case when not b<>0 then 1 else 0 end from t1;
select a+20, case when b<>0 and c<>0 then 1 else 0 end from t1;
select a+30, case when not (b<>0 and c<>0) then 1 else 0 end from t1;
select a+40, case when b<>0 or c<>0 then 1 else 0 end from t1;
select a+50, case when not (b<>0 or c<>0) then 1 else 0 end from t1;
select a+60, case b when c then 1 else 0 end from t1;
select a+70, case c when b then 1 else 0 end from t1;

-- What happens when you multiply a NULL by zero?
select a+80, b*0 from t1;
select a+90, b*c from t1;

-- What happens to NULL for other operators?
select a+100, b+c from t1;

-- Test the treatment of aggregate operators
select count(*), count(b), sum(b), avg(b), min(b), max(b) from t1;

-- Check the behavior of NULLs in WHERE clauses
select a+110 from t1 where b<10;
select a+120 from t1 where not b>10;
select a+130 from t1 where b<10 OR c=1;
select a+140 from t1 where b<10 AND c=1;
select a+150 from t1 where not (b<10 AND c=1);
select a+160 from t1 where not (c=1 AND b<10);

-- Check the behavior of NULLs in a DISTINCT query
select distinct b from t1;

-- Check the behavior of NULLs in a UNION query
select b from t1 union select b from t1;

-- Create a new table with a unique column.  Check to see if NULLs are considered
-- to be distinct.
create table t2(a int, b int unique);
insert into t2 values(1,1);
insert into t2 values(2,null);
insert into t2 values(3,null);
select * from t2;

drop table t1;
drop table t2;