从 '' IS NULL 引发的思考（关于空串, NULL 与索引）

使用过 Oracle 数据库的 IT 从业人员，可能都或多或少踩过空字符串（’’）与 NULL 对比的坑。

在知道 Oracle B 树索引不存储全 NULL 字段的前提下，考虑如下测试用例：

create table test(id number, name varchar2(10) unique); -- Oracle
create table test(id int, name varchar(10) unique);     -- PostgreSQL, MySQL
insert into test values(1, 'name1');        -- 1
insert into test values(1, 'name1');        -- 2
insert into test values(2, '');         -- 3
insert into test values(2, '');         -- 4
insert into test values(3, NULL);           -- 5
insert into test values(3, NULL);           -- 6

在 Oracle, PostgreSQL, MySQL 中，第 2, 4, 6 条语句，哪些能成功执行？哪些会报错？
各个数据库中是否有区别？

以下分别在这些数据库中进行相关用例的测试。

Oracle 中测试

SQL> create table test(id number, name varchar2(10) unique);

Table created.

SQL> insert into test values(1, 'name1');

1 row created.

SQL> insert into test values(1, 'name1');
insert into test values(1, 'name1')
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C0050200) violated

SQL> insert into test values(2, '');

1 row created.

SQL> insert into test values(2, '');

1 row created.

SQL> insert into test values(3, NULL);

1 row created.

SQL> insert into test values(3, NULL);

1 row created.

SQL> select id, case when name is null then 'NULL' else name end as name from test;

        ID NAME
---------- ----------
         1 name1
         2 NULL
         2 NULL
         3 NULL
         3 NULL

PostgreSQL 中测试

mydb@[local]:5432 =# create table test(id int, name varchar(10) unique);
CREATE TABLE
mydb@[local]:5432 =# insert into test values(1, 'name1');
INSERT 0 1
mydb@[local]:5432 =# insert into test values(1, 'name1');
ERROR:  duplicate key value violates unique constraint "test_name_key"
DETAIL:  Key (name)=(name1) already exists.
mydb@[local]:5432 =# insert into test values(2, '');
INSERT 0 1
mydb@[local]:5432 =# insert into test values(2, '');
ERROR:  duplicate key value violates unique constraint "test_name_key"
DETAIL:  Key (name)=() already exists.
mydb@[local]:5432 =# insert into test values(3, NULL);
INSERT 0 1
mydb@[local]:5432 =# insert into test values(3, NULL);
INSERT 0 1
mydb@[local]:5432 =# select id, case when name is null then 'NULL' else name end as name from test;
 id | name
----+-------
  1 | name1
  2 |
  3 | NULL
  3 | NULL
(4 rows)

MySQL 中测试

mysql> create table test(id int, name varchar(10) unique);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test values(1, 'name1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values(1, 'name1');
ERROR 1062 (23000): Duplicate entry 'name1' for key 'name'
mysql> insert into test values(2, '');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(2, '');
ERROR 1062 (23000): Duplicate entry '' for key 'name'
mysql> insert into test values(3, NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values(3, NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select id, case when name is null then 'NULL' else name end as name from test;
+------+-------+
| id   | name  |
+------+-------+
|    1 | name1 |
|    2 |       |
|    3 | NULL  |
|    3 | NULL  |
+------+-------+
4 rows in set (0.00 sec)

结果分析

从以上案例的实际测试结果可以看出：

Oracle 中字面量 '' (空字符串) 在入库时被当成 NULL 处理了，而 Oracle 索引不存储全 NULL 字段，所以第 4 和 6 步骤均不会报错
PostgreSQL 与 MySQL 中，'' (空字符串) 与 NULL 在处理时有明确区分，所以第 4 步报错，而第六步不会报错（NULL 的特殊处理）

在不了解 Oracle 对空字符串的处理逻辑情况下，还可能会对下面的过程感到费解：

SQL> delete from test;

5 rows deleted.

SQL> insert into test values(1, '');

1 row created.

SQL> select * from test where name = '';

no rows selected

为什么命名插入了一行记录，却无法查询到？实际上就是因为在内部存储时，已经将 name 字段处理为 NULL 值字段了。所以，在 Oracle 中，对于这种情况，只能使用 IS NULL 来判断查询：

SQL> select * from test where name IS NULL;

        ID NAME
---------- ----------
         1

而在 PostgreSQL, MySQL 以及 SQL Server 等数据库中，空字符串与 NULL 都是有明确区分的，不容易造成歧义。

各位可以对比如下两个表达式在这些数据库中的结果，也会有同样惊奇的发现。

'' = ''
'' IS NULL

简单总结

基于 Oracle 对空字符串的特殊处理，在异构数据库迁移或同步过程中，可能会出现一些不兼容的问题：

从 Oracle 到 PostgreSQL/MySQL/SQL Server 等数据库时，若唯一索引对应的列数据，在应用数据入库时允许空值，那么可能存在 Oracle 数据库能运行的语句，在其他数据库中报错的情况（空字符串重复）
从 PostgreSQL/MySQL/SQL Server 等数据库到 Oracle 数据库，则会存在部分 IS NULL 过滤条件的数据不准确，且 = ''无法查询数据的问题
总体来说，我觉得还是 PostgreSQL/MySQL/SQL Server 等数据库对空字符串的处理方式比较容易理解与接受

知识扩展

前面讲到 Oracle B树索引不会存储全为 NULL 字段，下面以 PostgreSQL 数据库为例，验证 PosgreSQL 中索引对空值的处理。

PostgreSQL 索引列空值

PostgreSQL 中的 pageinspect 扩展模块不仅仅能查看表 Page 及其相关行信息，还能查看索引 Page 及索引列的记录数据。
查看 B 树索引相关函数有 4 个，说明如下：

bt_metap(relname text) returns record
返回 B 树索引的元数据 Page 相关信息，通常这是 B 树索引的第一个 Page (即 0 号 Page)
bt_page_stats(relname text, blkno int) returns record
返回 B 树索引中指定 Page 的汇总信息，B 数索引的数据 Page 通常从第二个 Page 开始（即 1 号 Page）
bt_page_items(relname text, blkno int) returns setof record
返回 B 树索引中指定 Page 中的索引记录信息
bt_page_items(page bytea) returns setof record
返回 B 树索引中指定 Page 中的索引记录信息，与上一个函数不同之处在于，该函数接受从 get_raw_page 函数得到的原始 Page 数据作为参数

环境准备
注意：以下 DISTRIBUTE BY REPLICATION 是 AntDB 中的语法，在分布式环境中指定数据分布方式，普通的 PostgreSQL 一般不需要此语句。

DROP TABLE TEST_NULL;
CREATE TABLE TEST_NULL(ID INT, FIRST_NAME VARCHAR(10), LAST_NAME VARCHAR(10)) DISTRIBUTE BY REPLICATION;
CREATE UNIQUE INDEX IDX_TEST_NULL_NAME ON TEST_NULL(FIRST_NAME, LAST_NAME);
INSERT INTO TEST_NULL VALUES(1, 'FIRST1', 'LAST1');
INSERT INTO TEST_NULL VALUES(2, NULL, 'LAST2');
INSERT INTO TEST_NULL VALUES(3, 'FIRST3', NULL);
INSERT INTO TEST_NULL VALUES(4, NULL,  NULL);

查看索引信息

mydb@[local]:5432 =# \dt+ test_null
                       List of relations
 Schema |   Name    | Type  | Owner  |    Size    | Description 
--------+-----------+-------+--------+------------+-------------
 public | test_null | table | pguser | 8192 bytes | 
(1 row)

mydb@[local]:5432 =# \di+ idx_test_null_name 
                               List of relations
 Schema |        Name        | Type  | Owner  |   Table   | Size  | Description 
--------+--------------------+-------+--------+-----------+-------+-------------
 public | idx_test_null_name | index | pguser | test_null | 16 kB | 
(1 row)

可以看到，即使这个表很小，其中也仅仅只有 4 行数据，整个表数据大小也只有 8K 的情况下，其索引的大小却已经达到 16KB 了。这是因为 PostgreSQL 中 B 树索引的第一个块中存储了索引的元数据，而真正的索引记录从第二个块开始存储。

查看索引元数据

mydb@[local]:5432 =# SELECT * FROM bt_metap('idx_test_null_name');
 magic  | version | root | level | fastroot | fastlevel 
--------+---------+------+-------+----------+-----------
 340322 |       2 |    1 |     0 |        1 |         0
(1 row)

查看索引 Page 的汇总信息

mydb@[local]:5432 =# SELECT * FROM bt_page_stats('idx_test_null_name', 0);
ERROR:  block 0 is a meta page

mydb@[local]:5432 =# SELECT * FROM bt_page_stats('idx_test_null_name', 1);
 blkno | type | live_items | dead_items | avg_item_size | page_size | free_size | btpo_prev | btpo_next | btpo | btpo_flags 
-------+------+------------+------------+---------------+-----------+-----------+-----------+-----------+------+------------
     1 | l    |          4 |          0 |            22 |      8192 |      8044 |         0 |         0 |    0 |          3
(1 row)

可以看到，0 号 Page 属于 meta page，无法查看汇总信息。从 1 号 Page 开始，可以查看其当前 Page 的汇总信息。
当前的 1 号 Page 中，可以看到有 2 行记录，没有 dead 记录，平均每个记录大小为 16。

查看索引 Page 中的记录信息

mydb@[local]:5432 =# SELECT * FROM bt_page_items('idx_test_null_name', 0);
ERROR:  block 0 is a meta page

mydb@[local]:5432 =# SELECT * FROM bt_page_items('idx_test_null_name', 1);
 itemoffset | ctid  | itemlen | nulls | vars |                      data                       
------------+-------+---------+-------+------+-------------------------------------------------
          1 | (0,1) |      24 | f     | t    | 0f 46 49 52 53 54 31 0d 4c 41 53 54 31 00 00 00
          2 | (0,3) |      24 | t     | t    | 0f 46 49 52 53 54 33 00
          3 | (0,2) |      24 | t     | t    | 0d 4c 41 53 54 32 00 00
          4 | (0,4) |      16 | t     | f    | 
(4 rows)

同理， 0 号 meta page 中，也没有记录信息，索引记录从 1 号 Page 开始存储。但从索引中的记录数据来看，NULL 值也与正常值一样存储进了索引。
不管是前导列为 NULL，还是后续列为 NULL，甚至是两个字段全为 NULL，都在索引中存在记录。

验证 IS NULL 是否可走索引

postgres=# explain analyze select last_name from test_null where first_name is null;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_null  (cost=4.18..12.63 rows=4 width=38) (actual time=0.216..0.219 rows=3 loops=1)
   Recheck Cond: (first_name IS NULL)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_test_null_name  (cost=0.00..4.18 rows=4 width=0) (actual time=0.025..0.025 rows=3 loops=1)
         Index Cond: (first_name IS NULL)
 Planning time: 0.120 ms
 Execution time: 0.286 ms
(7 rows)

postgres=# explain analyze select last_name from test_null where first_name is null and last_name is null;
                                                             QUERY PLAN                                                             
----------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_test_null_name on test_null  (cost=0.15..8.17 rows=1 width=38) (actual time=0.057..0.061 rows=2 loops=1)
   Index Cond: ((first_name IS NULL) AND (last_name IS NULL))
   Heap Fetches: 2
 Planning time: 0.128 ms
 Execution time: 0.109 ms
(5 rows)

可以看到，在 PostgreSQL 中，通过 IS NULL 过滤条件的 SQL 也是能够正常使用索引的。不管是只使用前导列，还是索引两列都使用 IS NULL 过滤，均能正常走索引扫描。

Oracle 索引列空值

同样的测试环境，在 Oracle 中执行测试

DROP TABLE TEST_NULL;
CREATE TABLE TEST_NULL(ID NUMBER, FIRST_NAME VARCHAR2(10), LAST_NAME VARCHAR2(10));
CREATE UNIQUE INDEX IDX_TEST_NULL_NAME ON TEST_NULL(FIRST_NAME, LAST_NAME);
INSERT INTO TEST_NULL VALUES(1, 'FIRST1', 'LAST1');
INSERT INTO TEST_NULL VALUES(2, NULL, 'LAST2');
INSERT INTO TEST_NULL VALUES(3, 'FIRST3', NULL);
INSERT INTO TEST_NULL VALUES(4, NULL,  NULL);

查看索引数据
Oracle 中没有函数可以直接查看索引数据，一般有两种方式可以间接查看相关数据：

通过 BBED 工具，直接查看对应的数据块，早期版本直接提供，使用有一定的难度
通过转储的方式，将部分数据块转储为日志文件

以下我们通过转储的方式查看索引块中的数据

SQL> select extent_id, FILE_ID, BLOCK_ID, BLOCKS from dba_extents where segment_name = 'IDX_TEST_NULL_NAME';

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0         12     222873          8

SQL> alter system dump datafile 12 block min 222873 block max 222881;

System altered.

SQL> SET LINESIZE 195
SQL> COL "trace file name" FOR A100
SQL> SELECT (SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest')
  2         ||'/'
  3         ||TRIM(INSTANCE_NAME)
  4          ||'_ora_'
  5          ||(SELECT P.SPID FROM V$SESSION S,V$PROCESS P WHERE S.SID=SYS_CONTEXT('USERENV','SID') AND P.ADDR=S.PADDR)
  6          ||'.trc' "trace file name"
  7    FROM  V$INSTANCE;

trace file name
----------------------------------------------------------------------------------------------------
/home/oracle/admin/MYDB/udump/MYDB_ora_54657.trc

以下截取 Dump 文件中关于索引记录相关的信息：

row#0[8011] flag: ------, lock: 2, len=21, data:(6):  03 03 66 95 00 00
col 0; len 6; (6):  46 49 52 53 54 31
col 1; len 5; (5):  4c 41 53 54 31
row#1[7980] flag: ------, lock: 2, len=16, data:(6):  03 03 66 95 00 02
col 0; len 6; (6):  46 49 52 53 54 33
col 1; NULL
row#2[7996] flag: ------, lock: 2, len=15, data:(6):  03 03 66 95 00 01
col 0; NULL
col 1; len 5; (5):  4c 41 53 54 32
----- end of leaf block dump -----

可以看到， Oracle 索引中只存储了 3 行数据，这 3 行中，都有至少一列的数据不为 NULL，而全 NULL 的记录并没有存储。

验证 IS NULL 是否可走索引
既然索引中全为 NULL 的数据不存储，那么推测 IS NULL 条件是不可以走索引的，以下加以验证：

SQL> explain plan for select /*+index(test_null, IDX_TEST_NULL_NAME)*/ last_name from test_null where first_name is null;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1897709643

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     2 |    28 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_NULL |     2 |    28 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("FIRST_NAME" IS NULL)

Note
-----
   - dynamic sampling used for this statement

17 rows selected.

SQL> explain plan for select /*+index(test_null, IDX_TEST_NULL_NAME)*/ last_name from test_null where first_name is null and last_name is null;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1897709643

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    14 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_NULL |     1 |    14 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("FIRST_NAME" IS NULL AND "LAST_NAME" IS NULL)

Note
-----
   - dynamic sampling used for this statement

17 rows selected.

可以看到，即使使用了 /*+index(test_null, IDX_TEST_NULL_NAME)*/ 提示，SQL 都无法使用到索引。这也很好理解，因为 Oracle 索引中不会记录全为 NULL 的数据，那么如果走了索引，那么全为 NULL 的数据肯定就无法获取，那么最终的结果集就有可能不准确，所以无法通过索引扫描。

简单小结

通过上述实验，验证了 Oracle 中索引不会记录全为 NULL 的数据，而 PostgreSQL 中的索引则会记录所有的行记录数据。
二者这种设计上的差异，也各有各的优势与劣势。

忽略 NULL 值，则索引与表的记录数可能会不一致，这也使得索引大小能得到一定程度降低，间接改善非 NULL 值的索引扫描性能
记录 NULL 值，使得 IS NULL 的条件也可以使用索引，直接改善这类 SQL 的性能，同时在层级不变的情况下，对非 NULL 值的扫描也不会有太大影响

值得一提的是，在 PostgreSQL 中还直接支持部分数据索引，可以达到与 Oracle 索引一致的需求：

CREATE INDEX IDX_TEST_NULL_NAME ON TEST_NULL(FIRST_NAME, LAST_NAME) WHERE FIRST_NAME IS NOT NULL OR LAST_NAME IS NOT NULL;

在创建索引的时候，指定 WHERE 过滤条件，可以使得部分数据不进入索引中，减少索引大小。上例中指定 FIRST_NAME 或 LAST_NAME 不为 NULL，也就是说，排除了两列均为 NULL 的数据，这与 Oracle 中不存储全为 NULL 的效果相当，但也会导致 IS NULL 无法使用索引。

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 219,589评论 6赞 508
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 93,615评论 3赞 396
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 165,933评论 0赞 356
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,976评论 1赞 295
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,999评论 6赞 393
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,775评论 1赞 307
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,474评论 3赞 420
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 39,359评论 0赞 276
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,854评论 1赞 317
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 38,007评论 3赞 338
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 40,146评论 1赞 351
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,826评论 5赞 346
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,484评论 3赞 331
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 32,029评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 33,153评论 1赞 272
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 48,420评论 3赞 373
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 45,107评论 2赞 356