NULL Handling in SQLite
NULL Handling in SQLite
NULL Handling in SQLite Versus Other Database Engines
目标是让SQLite以符合标准的方式处理NULL。但是SQL标准中关于如何处理NULL的描述看起来很模糊。从标准文件中不清楚在任何情况下应该如何处理NULL。
因此，不用标准文档去测试各种流行的SQL引擎，看看它们是如何处理NULL的。这个想法是让SQLite像所有其他引擎一样工作。一个SQL测试脚本是由志愿者在各种SQL RDBMS上开发和运行的，这些测试的结果用于推导每个引擎如何处理NULL值。原始测试在2002年5月运行。测试脚本的副本位于本文末尾。
SQLite最初编码的方式是，下图中所有问题的答案都是“是”。但是在其他SQL引擎上运行的实验表明，他们没有一个以这种方式工作。所以SQLite被修改为与Oracle，PostgreSQL和DB2一样工作。这涉及为SELECT DISTINCT语句和SELECT中的UNION运算符的目的而使NULL不明显。NULL在UNIQUE列中仍然不同。这似乎有点武断，但与其他引擎兼容的愿望超过了这种反对意见。
为了SELECT DISTINCT和UNION的目的，SQLite可以将NULL视为不同的对象。为此，应该更改sqliteInt.h源文件中的NULL_ALWAYS_DISTINCT #define的值并重新编译。
更新2003-07-13：由于本文档最初编写的一些数据库引擎测试已更新，用户已经足够发送更正到下面的图表。原始数据显示了各种各样的行为，但随着时间的推移，行为范围已经趋向于PostgreSQL / Oracle模型。唯一明显的区别是Informix和MS-SQL都将NULL视为UNIQUE列中的不明确。NULL对于UNIQUE列是不同的，但对于SELECT DISTINCT和UNION不清楚。看起来NULL应该在任何地方或任何地方都不同。并且SQL标准文档建议NULL应该在每个地方都是不同的。然而截至本文撰写时，没有任何SQL引擎测试将NULL视为SELECT DISTINCT语句或UNION中的不同。
下表显示了NULL处理实验的结果。
| SQLite的 | PostgreSQL的 | 神谕 | Informix的 | DB2 | MS-SQL | 豹猫 | |
|---|---|---|---|---|---|---|---|
| 将任何内容添加到null都会给null | 是 | 是 | 是 | 是 | 是 | 是 | 是 |
| 将null乘以零得出null | 是 | 是 | 是 | 是 | 是 | 是 | 是 |
| 空值在UNIQUE列中是不同的 | 是 | 是 | 是 | 没有 | （注4） | 没有 | 是 |
| 在SELECT DISTINCT中，空值是不同的 | 没有 | 没有 | 没有 | 没有 | 没有 | 没有 | 没有 |
| 在UNION中，空值是不同的 | 没有 | 没有 | 没有 | 没有 | 没有 | 没有 | 没有 |
| “CASE WHEN 1 1 ELSE 0 END”为0？ | 是 | 是 | 是 | 是 | 是 | 是 | 是 |
| “null或true”是真的 | 是 | 是 | 是 | 是 | 是 | 是 | 是 |
| “not（null and false）”为真 | 是 | 是 | 是 | 是 | 是 | 是 | 是 |
| MySQL3.23.41 | MySQL4.0.16 | 火鸟 | SQLAnywhere的 | BorlandInterbase | |
|---|---|---|---|---|---|
| 将任何内容添加到null都会给null | 是 | 是 | 是 | 是 | 是 |
| 将null乘以零得出null | 是 | 是 | 是 | 是 | 是 |
| 空值在UNIQUE列中是不同的 | 是 | 是 | 是 | （注4） | （注4） |
| 在SELECT DISTINCT中，空值是不同的 | 没有 | 没有 | 否（注1） | 没有 | 没有 |
| 在UNION中，空值是不同的 | （注3） | 没有 | 否（注1） | 没有 | 没有 |
| “CASE WHEN 1 1 ELSE 0 END”为0？ | 是 | 是 | 是 | 是 | （注5） |
| “null或true”是真的 | 是 | 是 | 是 | 是 | 是 |
| “not（null and false）”为真 | 没有 | 是 | 是 | 是 | 是 |
| 笔记： | 1. | 老版本的firebird省略了SELECT DISTINCT和UNION中的所有NULL。 |
|---|---|---|
| 2. | 测试数据不可用。 | |
| 3. | MySQL版本3.23.41不支持UNION。 | |
| 4. | DB2，SQL Anywhere和Borland Interbase不允许在UNIQUE列中使用NULL。 | |
| 5. | Borland Interbase不支持CASE表达式。 |
以下脚本用于收集上表中的信息。
- 我已经决定SQL对NULL的处理是反复无常的，不能
- 由逻辑推断。它必须通过实验发现。为此，我有
- 准备下面的脚本来测试各种SQL数据库如何处理NULL。
- 我的目标是使用这个脚本中的信息收集来制作SQLite
- 尽可能像其他数据库一样。
-
- 如果你可以在数据库引擎中运行这个脚本并发送结果
- 对我来说drh@hwaci.com，这将是一个很大的帮助。请务必确定
- 您用于此测试的数据库引擎。谢谢。
-
- 如果您必须更改任何内容才能使此脚本与您的数据库一起运行
- 引擎，请将您的修订脚本与您的结果一起发送。
-
- 用数据创建一个测试表
创建表t1（一个int，b int，c int）;
插入到t1值（1,0,0）;
插入到t1值（2,0,1）;
插入t1值（3,1,0）;
插入t1值（4,1,1）;
插入t1值（5，null，0）;
插入t1值（6，null，1）;
插入到t1值（7，null，null）;
- 检查CASE在其测试表达式中使用NULL做什么
选择a，b <> 0时的情况，1否则0从t1结束;
选择一个+10的情况，当不是b <> 0时，则另一个0从t1结束;
当b <> 0并且c <> 0时，选择一个+ 20的情况，然后1个其他0从t1结束;
如果不是（b <> 0和c <> 0），则选择一个+ 30的情况，然后1个其他0从t1结束;
当b <> 0或c <> 0时，选择+ 40，case 1，否则0从t1结束;
如果不是（b <> 0或c <> 0），则选择一个+ 50的情况，然后从t1结束1个否则0;
选择一个+ 60，情况b当c然后1 else 0从t1结束;
选择一个+ 70，情况c当b然后1其他0从t1结束;
- 当您将零乘以零时会发生什么？
从t1选择+ 80，b * 0;
从t1选择+ 90，b * c;
- 其他运营商的NULL会发生什么？
从t1中选择一个+ 100，b + c;
- 测试集合运营商的处理
从t1开始选择count（*），count（b），sum（b），avg（b），min（b），max（b）
- 检查WHERE子句中NULL的行为
从t1选择一个+ 110，其中b <10;
从t1选择一个+ 120而不是> 10;
从t1选择a + 130，其中b <10或c = 1;
从t1选择+ 140，其中b <10和c = 1;
从t1选择+ 150，其中不是（b <10 AND c = 1）;
从t1中选择+ 160（c = 1 AND b <10）;
- 检查DISTINCT查询中NULL的行为
从t1中选择不同的b;
- 检查UNION查询中NULL的行为
从t1中选择b union从t1中选择b;
- 创建一个具有唯一列的新表格。检查是否考虑NULL
- 有区别。
创建表t2（一个int，b int唯一）;
插入t2值（1,1）;
插入t2值（2，null）;
插入t2值（3，null）;
从t2选择*;
丢表t1;
下降表t2;
SQLite is in the Public Domain.
