参数 ora_input_emptystr_isnull 对于数据存储的影响

原生的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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

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 的值，避免后续数据入库后再去修改参数。