原生的PG 对于 '' 和 null 认为是不同值:空值 和不确定值;而oracle 认为二者都是不确定的值。KingbaseES 为了兼容Oracle,增加了参数ora_input_emptystr_isnull,用于控制 '' 和 null 的比较。
一、Oracle null and '' 比较
SQL> create table t1(id number,name varchar(9));
Table created.
SQL> insert into t1(id) values(1);
1 row created.
SQL> insert into t1 values(2,''); --注意,中间没有空格。有空格就不是null
1 row created.
SQL> insert into t1 values(3,null);
1 row created.
SQL> select * from t1 where name is null;
ID NAME
---------- ---------
1
2
3
SQL> select * from t1 where name='';
no rows selected
SQL> select length(name) from t1;
LENGTH(NAME)
------------
SQL>
可以看到,不管插入 null or '',Oracle 都看做 null。
二、KingbaseES
1、ora_input_emptystr_isnull=off
test=# show ora_input_emptystr_isnull;
ora_input_emptystr_isnull
---------------------------
off
(1 row)
test=# create table t1(id number,name varchar(9));
CREATE TABLE
test=# insert into t1(id) values(1);
INSERT 0 1
test=# insert into t1 values(2,'');
INSERT 0 1
test=# insert into t1 values(3,null);
INSERT 0 1
test=# select * from t1 where name is null;
id | name
----+------
1 |
3 |
(2 rows)
test=# select * from t1 where name='';
id | name
----+------
2 |
(1 row)
test=# select id,length(name) from t1;
id | length
---+--------
1 |
2 | 0
3 |
(3 rows)
结论:当ora_input_emptystr_isnull=off时,null 和 '' 实际不同的。
在数据插入后,将参数改成on,看下查询的不同结果:
test=# show ora_input_emptystr_isnull;
ora_input_emptystr_isnull
---------------------------
on
(1 row)
test=# select * from t1 where name=''; --这个也没结果,这个数据“丢失“了
id | name
----+------
(0 rows)
test=# select * from t1 where name is null;
id | name
----+------
1 |
3 |
(2 rows)
test=# select id,length(name) from t1;
id | length
----+--------
1 |
2 | 0
3 |
(3 rows)
结论:当数据保存到数据库时,会根据参数ora_input_emptystr_isnull 保存为不同格式,后续的参数修改不影响已存储的数据。
2、ora_input_emptystr_isnull=on
test=# show ora_input_emptystr_isnull;
ora_input_emptystr_isnull
---------------------------
on
(1 row)
test=# drop table t1;
DROP TABLE
test=# create table t1(id number,name varchar(9));
CREATE TABLE
test=# insert into t1(id) values(1);
INSERT 0 1
test=# insert into t1 values(2,'');
INSERT 0 1
test=# insert into t1 values(3,null);
INSERT 0 1
test=# select * from t1 where name='';
id | name
----+------
(0 rows)
test=# select * from t1 where name is null;
id | name
----+------
1 |
2 |
3 |
(3 rows)
test=# select id,length(name) from t1;
id | length
----+--------
1 |
2 |
3 |
(3 rows)
将参数改为off ,看下执行结果
test=# set ora_input_emptystr_isnull=off;
SET
test=# select * from t1 where name is null;
id | name
----+------
1 |
2 |
3 |
(3 rows)
test=# select * from t1 where name='';
id | name
----+------
(0 rows)
结论:将参数改为off后,执行结果不变。
注意:由于参数 ora_input_emptystr_isnull不同设置,会修改实际的数据存储,为了保证数据不发生“丢失”,建议用户在系统初始化后就确定参数 ora_input_emptystr_isnull 的值,避免后续数据入库后再去修改参数。 更多信息,参见https://help.kingbase.com.cn/v8/index.html