代码改变世界

Oracle、PostgreSQL、SQL Server中的NULL和空字符串的比较

2023-05-11 11:44  abce  阅读(461)  评论(0编辑  收藏  举报

什么是NULL?
根据ANSI SQL-92规范,null既不是空字符串(对于字符或日期时间数据类型),也不是零值(对于数字数据类型)。为了确保所有null被统一处理,ANSI SQL-92规范规定所有数据类型的null必须是相同的。对于给定记录,当属性没有要存储的数据时,它由SQL值NULL表示。

Oracle、PostgreSQL、SQL Server中的NULL和空字符串
Oracle将NULL和空字符串都当作NULL来处理。Oracle需要一个byte来存储NULL。
PostgreSQL将NULL和空字符串分开处理,NULL是NULL,空字符串是空字符串。PostgreSQL不需要空间来存储NULL。
SQL Server也是将NULL和空字符串分开处理,NULL是NULL,空字符串是空字符串。SQL Server不需要空间来存储NULL。

NULL和空字符串上的唯一性约束
Oracle中,具有唯一性约束的列,可以包含任意数量的NULL和空字符串;而SQLServer中,只是允许有一个NULL、一个空字符串。对于NULL,PostgreSQL的处理方式类似Oracle,而对于空字符串,PostgreSQL的处理方式类似SQLServer。

Unique NULLS NOT DISTINCT语句
有意思的是,从PostgreSQL 15,可以使用nulls not distinct子句来防止将NULL作为不同的值。这就阻止了我们在唯一性约束列上插入多个NULL值。

Treating NULLS as DISTINCT
#################################
CREATE TABLE null_empty_test(a text, b text, UNIQUE(a));

INSERT INTO null_empty_test VALUES(null);
INSERT 0 1

INSERT INTO null_empty_test VALUES(null);
INSERT 0 1

下面来尝试一下15中的新特性

Treating NULLS as NOT DISTINCT
#################################
CREATE TABLE null_empty_test(a text, b text, UNIQUE NULLS NOT DISTINCT(a));

INSERT INTO null_empty_test VALUES(null);
INSERT 0 1

postgres=# INSERT INTO null_empty_test VALUES(null);
ERROR:  duplicate key value violates unique constraint "null_empty_test_a_key"
DETAIL:  Key (a)=(null) already exists.

可以看到,报错显示插入了重复的值。