Choose any three.
|
|
SQLite对比其他数据库引擎处理NULL的方法
NULL Handling in SQLite Versus Other Database Engines
目标是使SQLite处理NULL的逻辑能按照标准的方式。
但是，SQL标准中关于如何处理NULL的描述似乎模糊不清。
SQL标准中关于在所有情况下如何精确的处理NULL并没有描述清楚。
The goal is
to make SQLite handle NULLs in a standards-compliant way.
But the descriptions in the SQL standards on how to handle
NULLs seem ambiguous.
It is not clear from the standards documents exactly how NULLs should
be handled in all circumstances.
所以，SQLite没有依照标准文档来进行，而是考察了各种各样流行的SQL引擎看看它们是如何处理NULL的。这个方案使SQLite运行的更加接近其它引擎。
于是开发了一个SQL测试脚本，并且由志愿者在各种各样的SQL RDBMS上运行。然后依照这些测试的运行结果推断出每个引擎是如何处理NULL值的。
原始的测试是在2002年五月运行的。
在这篇文档的末尾可以找到这个测试脚本。
So instead of going by the standards documents, various popular
SQL engines were tested to see how they handle NULLs. The idea
was to make SQLite work like all the other engines.
An SQL test script was developed and run by volunteers on various
SQL RDBMSes and the results of those tests were used to deduce
how each engine processed NULL values.
The original tests were run in May of 2002.
A copy of the test script is found at the end of this document.
在SQLite最初实现的时候是按照下面的图表中所有问题的答案都是"Yes"进行的。
但是在其它SQL引擎上运行的试验表明没有一个引擎是按照这个方式工作的。
所以SQLite修改为按照Oracle、PostgreSQL和DB2的方式来实现。
这个方式导致在SELECT DISTINCT语句和SELECT里的UNION操作符中将多个NULL当做相同的。而在UNIQUE列中多个NULL依然是相互不同的。这似乎有点儿武断，但是兼容其它引擎的期望超过了这个缺点。
SQLite was originally coded in such a way that the answer to
all questions in the chart below would be "Yes". But the
experiments run on other SQL engines showed that none of them
worked this way. So SQLite was modified to work the same as
Oracle, PostgreSQL, and DB2. This involved making NULLs
indistinct for the purposes of the SELECT DISTINCT statement and
for the UNION operator in a SELECT. NULLs are still distinct
in a UNIQUE column. This seems somewhat arbitrary, but the desire
to be compatible with other engines outweighed that objection.
让SQLite在SELECT DISTINCT和UNION中将所有NULL区分对待是可以的。如果想要这么做只需要修改源文件sqliteInt.h中NULL_ALWAYS_DISTINCT宏定义的值，然后重新编译即可。
It is possible to make SQLite treat NULLs as distinct for the
purposes of the SELECT DISTINCT and UNION. To do so, one should
change the value of the NULL_ALWAYS_DISTINCT #define in the
sqliteInt.h source file and recompile.
2003-07-13更新：在这篇文档最初写完后，一些数据库引擎的测试已经更新，并且很多用户非常友好的提交了下面图标的修正。 原始数据展示了非常多样的行为，但是经过一段时间，这些行为的范围慢慢向PostgreSQl/Oracle模式聚拢。 唯一明显的不同是Informix和MS-SQL都在UNIQUE列中将多个NULL当做相同处理。
Update 2003-07-13: Since this document was originally written some of the database engines tested have been updated and users have been kind enough to send in corrections to the chart below. The original data showed a wide variety of behaviors, but over time the range of behaviors has converged toward the PostgreSQL/Oracle model. The only significant difference is that Informix and MS-SQL both treat NULLs as indistinct in a UNIQUE column.事实上，多个NULL在UNIQUE列中是不同的，而在SELECT DISTINCT和UNION中是相同的，这依旧是模糊的。 这似乎应当是多个NULL在任何地方都相同或都不同。并且SQL标准文档建议多个NULL应当在所有地方都是不同的。 然而，从写本文开始，没有一个测试的SQL引擎在SELECT DISTINCT或UNION中奖多个NULL当做不同来对待。
The fact that NULLs are distinct for UNIQUE columns but are indistinct for SELECT DISTINCT and UNION continues to be puzzling. It seems that NULLs should be either distinct everywhere or nowhere. And the SQL standards documents suggest that NULLs should be distinct everywhere. Yet as of this writing, no SQL engine tested treats NULLs as distinct in a SELECT DISTINCT statement or in a UNION.
下面的表格展示了NULL处理实验的结果。
The following table shows the results of the NULL handling experiments.
| SQLite | PostgreSQL | Oracle | Informix | DB2 | MS-SQL | OCELOT | |
|---|---|---|---|---|---|---|---|
| NULL加任何内容都返回NULL
Adding anything to null gives null |
Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| null乘以零返回null
Multiplying null by zero gives null |
Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| 在UNIQUE列中每个NULL都是不同的
nulls are distinct in a UNIQUE column |
Yes | Yes | Yes | No | (备注 4) | No | Yes |
| 在SELECT DISTINCT中每个NULL都是不同的
nulls are distinct in SELECT DISTINCT |
No | No | No | No | No | No | No |
| 在UNION中每个NULL都是不同的
nulls are distinct in a UNION |
No | No | No | No | No | No | No |
| "CASE WHEN null THEN 1 ELSE 0 END" 是0？
"CASE WHEN null THEN 1 ELSE 0 END" is 0? |
Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| "null OR true"是true？
"null OR true" is true |
Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| "not (null AND false)"是true？
"not (null AND false)" is true |
Yes | Yes | Yes | Yes | Yes | Yes | Yes |
| MySQL 3.23.41 |
MySQL 4.0.16 |
Firebird | SQL Anywhere |
Borland Interbase |
|
|---|---|---|---|---|---|
| NULL加任何内容都返回NULL
Adding anything to null gives null |
Yes | Yes | Yes | Yes | Yes |
| NULL乘以零返回NULL
Multiplying null by zero gives null |
Yes | Yes | Yes | Yes | Yes |
| 在UNIQUE列中每个NULL都是不同的
nulls are distinct in a UNIQUE column |
Yes | Yes | Yes | (备注 4) | (备注 4) |
| 在SELECT DISTINCT中每个NULL都是不同的
nulls are distinct in SELECT DISTINCT |
No | No | No (备注 1) | No | No |
| 在UNION中每个NULL都是不同的
nulls are distinct in a UNION |
(备注 3) | No | No (备注 1) | No | No |
| "CASE WHEN null THEN 1 ELSE 0 END" 是0？
"CASE WHEN null THEN 1 ELSE 0 END" is 0? |
Yes | Yes | Yes | Yes | (备注 5) |
| "null OR true"是true？
"null OR true" is true |
Yes | Yes | Yes | Yes | Yes |
| "not (null AND false)"是true？
"not (null AND false)" is true |
No | Yes | Yes | Yes | Yes |
| 备注： | 1. | 老版本的firebird中会忽略SELECT DISTINCT和UNION中的所有NULL。
Older versions of firebird omits all NULLs from SELECT DISTINCT and from UNION. |
| 2. | 测试数据无法获得
Test data unavailable. |
|
| 3. | MySQL 3.23.41版不支持UNION。
MySQL version 3.23.41 does not support UNION. |
|
| 4. | DB2、 SQL Anywhere 和 Borland Interbase 在UNIQUE列中不支持NULL。
DB2, SQL Anywhere, and Borland Interbase do not allow NULLs in a UNIQUE column. |
|
| 5. | Borland Interbase不支持CASE表达式。
Borland Interbase does not support CASE expressions. |
下面的脚本用于手机上面表格中的数据。
The following script was used to gather information for the table
above.
-- 我确定SQL在处理NULL上是多变的，并且无法按照逻辑来推理的。这必须依靠试验来 -- 发现。为了这个目的，我准备了后面这个脚本来测试各种SQL数据库是如何处理NULL的。 -- 我的目的是通过这个脚本收集的信息来使得SQLite尽可能的与其他数据库相似。 -- 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. -- -- 如果你乐意在你的数据库引擎上运行这个脚本，并把结果邮件发送给我drh@hwaci.com，这将是对我的大帮助。请确认你用来测试的数据库引擎的标识。谢谢。 -- 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); -- 检查CASE在测试表达式中如何处理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; -- 测试当NULL和零相乘会发生什么？ -- What happens when you multiple a NULL by zero? select a+80, b*0 from t1; select a+90, b*c from t1; -- NULL和其它操作符会发生什么？ -- What happens to NULL for other operators? select a+100, b+c from t1; -- 测试聚合操作符如何处理NULL -- Test the treatment of aggregate operators select count(*), count(b), sum(b), avg(b), min(b), max(b) from t1; -- 检查WHERE子句中NULL的行为 -- 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); -- 检查在DISTINCT查询中NULL的行为 -- Check the behavior of NULLs in a DISTINCT query select distinct b from t1; -- 检查在UNION查询中NULL的行为 -- Check the behavior of NULLs in a UNION query select b from t1 union select b from t1; -- 创建一个包含UNIQUE列的新表。检查多个NULL是否会当做不同记录。 -- 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;