NULL和空字符串不同数据库的表现

null和空字符串在不同的数据库中表现不一样,找了一张图,可以很清晰的对比了解。

首先null不是一个空字符串,也不是一个为零的值,上图,Oracle将NULL和空字符串都视为NULL。PostgreSQL将NULL视为NULL,将空字符串视为空字符串。与PostgreSQL类似,SQL Server也将NULL视为NULL,将空字符串视为空字符串。

Oracle需要一个字节来存储NULL。然而,PostgreSQL和SQL Server都不需要任何空间来存储NULL。

在Oracle中,存在唯一约束的列可以存储任意数量的NULL项和空字符串。
在SQL Server中,在一个列上存在唯一约束时,只允许插入一个NULL和一个空字符串。
在PostgreSQL中,允许插入多个类似于Oracle的NULL值。与SQL Server类似,PostgreSQL的列存在唯一约束时,只允许一个空字符串。

oracle和SQL Server不一一举例,pg中例子如下:

#pg中,列b虽然有唯一越苏,但可以插入多个null值postgres=# create table tab(a bigint,b text, UNIQUE(b));CREATE TABLEpostgres=# insert into tab values(1,null);INSERT 0 1postgres=# insert into tab values(1,null);INSERT 0 1#但是不能插入多个空字符串postgres=# insert into tab values(2,'');INSERT 0 1postgres=# insert into tab values(2,'');ERROR:  duplicate key value violates unique constraint "tab_b_key"DETAIL:  Key (b)=() already exists.

从PostgreSQL 15开始,我们可以使用NULLS NOT DISTINCT子句来防止null值被处理为DISTINCT。这可以防止我们在具有唯一约束的列中插入多个空值。

支持以下可选项:
NULLS NOT DISTINCT null也被认为是等同于null的, unique字段不能存多个null值;
NULLS DISTINCT 保持向后兼容, unique字段可以存储多个null值.

创建索引也支持该选项
CREATE UNIQUE INDEX unique_idx1 ON unique_tbl (i) NULLS DISTINCT;
CREATE UNIQUE INDEX unique_idx2 ON unique_tbl (i) NULLS NOT DISTINCT;

看一下例子:

#创建测试表,插入null值,可见只能插入一个,再插违反唯一约束postgres=# CREATE TABLE tab(a text, b text, UNIQUE NULLS NOT DISTINCT(a));CREATE TABLEpostgres=# insert into tab values (null);INSERT 0 1postgres=# insert into tab values (null);ERROR:  duplicate key value violates unique constraint "tab_a_key"DETAIL:  Key (a)=(null) already exists.

判断和NULL值冲突的例子

有以下一张表,我们要确保a和b的组合是唯一的,并遵守以下两个规则:

  1. 如果b为NULL,那么a不能有重复的值插入
  2. 如果b不为NULL,是其它的不同值,那么a可以插入重复的值
CREATE TABLE tab (   a integer NOT NULL,   b integer);-- 可插入INSERT INTO tab VALUES (1, NULL);-- 由于规则1,不能插入,因为b为NULLINSERT INTO tab VALUES (1, 2);-- 可以插入INSERT INTO tab VALUES (5, 2);-- 可以插入,遵守规则2INSERT INTO tab VALUES (5, 3);-- 不能插入,违反规则1INSERT INTO tab VALUES (5, NULL);
如果我们在a和b上建一个NULLS NOT DISTINCT的约束,可以实现上述要求吗?
#创建唯一索引,插入数据测试,NULLS NOT DISTINCT不符合我们想要的效果create unique index idx_tab_a_b on tab (a,b) NULLS NOT DISTINCT;postgres=# INSERT INTO tab VALUES (1, NULL);INSERT 0 1postgres=# INSERT INTO tab VALUES (1, 2);INSERT 0 1postgres=# INSERT INTO tab VALUES (5, 2);INSERT 0 1postgres=# INSERT INTO tab VALUES (5, 3);INSERT 0 1postgres=# INSERT INTO tab VALUES (5, NULL);INSERT 0 1
使用Range Types实现目的
#Range Types几个实例,[]表示闭区间,包括边界,()表示开区间,不包括边界SELECT '[2022-09-15 00:00:00,2022-09-16 00:00:00)'::tsrange;                     tsrange                    ═══════════════════════════════════════════════ ["2022-09-15 00:00:00","2022-09-16 00:00:00") SELECT '[-10,10]'::int4range;  int4range ═══════════ [-10,11) SELECT '[0,)'::numrange;  numrange ══════════ [0,)

使用排除约束替代唯一约束

# 排除约束是使用GiST索引实现的,而GiST索引又是b树索引的扩展,我们需要安装 btree_gist插件支持我们想要操作符postgres=# CREATE EXTENSION IF NOT EXISTS btree_gist;CREATE EXTENSIONpostgres=# truncate tab ;TRUNCATE TABLE#解释一下下面的排它约束,该约束表示不允许插入,a相等,b构建的整数范围相重叠的行,另外int4range(b, b, '[]')表示如果b不为NULL, int4range函数将构造一个只包含b的整数范围。如果b为NULL,则得到的范围在两端都是无界的。所以这正是我们测试所需要的postgres=# ALTER TABLE tab ADD CONSTRAINT null_uniqueEXCLUDE USING gist (   a WITH =,   int4range(b, b, '[]') WITH &&);ALTER TABLEpostgres=# INSERT INTO tab VALUES (1, NULL);INSERT 0 1postgres=# INSERT INTO tab VALUES (1, 2);ERROR:  conflicting key value violates exclusion constraint "null_unique"DETAIL:  Key (a, int4range(b, b, '[]'::text))=(1, [2,3)) conflicts with existing key (a, int4range(b, b, '[]'::text))=(1, (,)).postgres=# INSERT INTO tab VALUES (5, 2);INSERT 0 1postgres=# INSERT INTO tab VALUES (5, 3);INSERT 0 1postgres=# INSERT INTO tab VALUES (5, NULL);ERROR:  conflicting key value violates exclusion constraint "null_unique"DETAIL:  Key (a, int4range(b, b, '[]'::text))=(5, (,)) conflicts with existing key (a, int4range(b, b, '[]'::text))=(5, [2,3)).

由上可见:

  1. 在已知的情况下,不要使用NULL值表示任何含义,这将使得我们数据的操作更加复杂
  2. 如果使用B树索引的唯一约束不行,可以考虑使用GiST索引中通用的排它约束
  3. 范围数据类型的灵活使用

参考:
https://www.postgresql.org/docs/current/rangetypes.html
https://www.postgresql.org/docs/current/btree-gist.html
https://www.cybertec-postgresql.com/en/unique-constraint-null-conflicts-with-everything/
https://www.migops.com/blog/null-and-empty-string-in-oracle-vs-postgresql-vs-sql-server/