参数 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

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