Go Back to Sqlite Docs

SQLite 中 NULL 值的处理及与其它数据库系统的比较

我们的目的是让SQLite以与标准兼容的方式来处理 NULL。 但但起来，在 SQL 标准中，对于如何处理 NULL 是不明确的。 从标准文档中，并不能在所有情况下都清楚地看出应该如何确切地处理 NULL。

所以，除了遵循标准的文档，我们还测试了其它许多流行的 SQL 引擎， 来看它们是如果处理 NULL 的。我们的思想是，让 SQLite 像其它数据库引擎那样工作。 有志愿者编写了一个测试脚本，用于在各种各样的关系型数据库系统上测试 NULL 值的处理方式。我们最初是在2005年5月测试的。在本文的最后， 有该测试脚本的一个拷贝。

最初，SQLite 遵循的编码标准是－对于下面的表格，每个问题的回答都应该是 YES 。 但通过对其它 SQL 数据库引擎的测试，我们发现，没有任何一个是那样的。 所以，SQLite 就改成了与Oracle、PostgreSQL 以及 DB2 相同的方式来处理 NULL。 这就使得在 SELECT DISTINCT 语句及 SELECT 中的 UNION 操作符等为获得不重复值而设的操作，会认为 NULL 是彼此不同的。 但在一个具有唯一约束的列上， NULL 值仍然是相同的。这看起来有些专制， 但与我们与其它数据库引擎相比，这种专制就显得不那种重要了。

当然，也可以让 SQLite 认为 SELECT DISTINCT 及 UNION 中的 NULL 是相同的。 这可以通过改变 sqliteInt.h 源文件中 NULL_ALWAYS_DISTINCT 的 #define 预理宏并重新编译来实现。

更新于 2003-07-13: 由于本当初写文本的时候所测试的数据库引擎都已经更新了， 所以用户很可能期待修正下表。最初的数据显示了非常不一致的行为， 但随着时间的推移，这些不一致性大都趋从了 PostgreSQL/Oracle 模型。 仅有的重要区别是 Informix 和 MS-SQL 都认为 UNIQUE 列中的 NULL 是彼此不同的。

事实是，NULLs 在 UNIQUE 上应该是彼此相同的，而对于 SELECT DISTINCT 及 UNION 仍然不好说。 看起来，在任何地方，NULL都应该相同，或者任何地方都不同。 并且 SQL 标准文档也建议 NULL 值应该在任何地方都是相同的。 但是，直到本文写作时，也没有 SQL 引擎在 SELECT DISTINCT 或 UNION 中把 NULL 看作是相同的。

下表列出了对于 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 gather 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 drh@hwaci.com, 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 multiple 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;