kingbase中''与null的兼容

问题描述：

数据库是oracle 模式时，参数ora_input_emptystr_isnull=on是为了兼容oracle 对于‘’作为null处理。

分析与解决方法：

以下是oracle环境下，’’ 比较结果：

SQL> create table t1 (id integer, name char (9) ) ;Table created.SQL> insert into t1 values(1,'') ;1 row created.SQL> select * from t1 where name='' ;no rows selectedSQL> select * from t1 where name is null;ID NAME

同样，开启ora_input_emptystr_isnull参数后，KES结果如下：

test-# create table t1 (id integer,name char(9)) ;CREATE TABLEtest=# insert into t1 values(1, '') ;INSERT 0 1test=# select * from t1 where name=' ' ;id | name---+-------(10 rows )test=# select * from t1 where name is null;id | namc---+--------1  |(1 row)test=# show database_mode;database_mode----------------oracle(1 row)

2.1.2. 判断值是否为空，要用 is null ¶

问题描述：

对于空值使用 =‘’ 返回结果与is null返回结果不同

test=# show ora_input_emptystr_isnull;

ora_input_emptystr_isnull----------------------------on(1 row)

test=# create table t1 (id1 integer ，id2 integer) ;

CREATE TABLEtest=# insert into t1 values (1, nul1) ;

INSERT 0 1

test=# insert into t1 values(2, '');

INSERT 0 1

test=# select * from t1 where id2 is null;

id1 | id2----+------1  |2  |

(2 rows)

test=# select * from t1 where id2='';id1  | id2-----+-----(0 rows)

分析与解决方法：

当参数ora_input_emptystr_isnull=on时，’’会转换为null，而null表示不确定的值，因此，NULL值比较只能是IS NULL或IS NOT NULL，不可以是= NULL 或<> NULL的的形式判断，无论= NULL 或<> NULL，都是不成立的。

2.1.3. PG模式下‘’值问题 ¶

问题描述：

PG模式下，需要设置ora_input_emptystr_isnull=off，否则会有很多问题（因为这个参数本身就是为兼容oracle设置的，必须在oracle模式下使用）。

分析与解决方法：

PG模式，‘’行为如下：

test=# show ora_input_emptystr_isnull;

ora_input_emptystr_isnull--------------------------off

(1 rows)

test=# select * from t1 where name='';

id | name---+-----1  |(1 rows)

test=# select * from t1 where name is null;

id | name---+-----

(0 rows)

test=# set ora_input_emptystr_isnull=on;

SET

test=# select * from t1 where name='';

id | name---+-----1  |(1 rows)

test=# select * from t1 where name is null;

id | name---+------(0 rows)

2.1.4. Ora_input_emptystr_isnull对于字符类型空值的影响 ¶

问题描述：

当insert数据时，对于空值可以是 ‘’（中间没有空格）或 null，不同参数值结果是不同的，会影响后续的select访问结果。

分析与解决方法：

1) ora_input_emptystr_isnull=on 插入的数据，‘’和null都会转为null，之后的select操作不管ora_input_emptystr_isnull为何值，返回结果都相同：

test=# show ora_input_emptystr_isull; --在参数开启的情况下insert数据ora_input_emptystr_isull-------------------------on(1 row)

test=# create table t1(id integer,name varchar(9));
CREATE TABLEtest=# insert into tl values(1,''); --没有空格

INSERT 0 1

test=# insert into 1 values(2,null);

INSERT 0 1test=# select * from tl where name is nu11;

id |name---+------1  |2  |(2 rows)

test=# select * from t1 where name='';

id | name---+------(0 rows)

test=# set ora_input_emptystr_isull=off; 

--参数改为off， 不影响select结果SET

test=# select * from t1 where name is null;id  | name----+------1  |2  |(2 rows)test=# select * from t1 where name='';id  | name---+------(0 rows)

2) 在ora_input_emptystr_isnull=off时插入的数据，ora_input_emptystr_isnull值不同，返回的结果也不同：

test=# show ora_input_emptystr_isnull;

ora_input_emptystr_isnull-------------------------off(1 row)

test=# create table t1 (id integer,name varchar(9));

CREATE TABLEtest=# insert intc t1 value(1,'');

INSERT 0 1

test=# insert inte tl values(2,null);

INSERT 0 1

test=# select * from t1 where name is null;

id | name---+-------2  |(1 row)

test=# select * trom tl where name='';
--与null不同id | name---+-------2  |(1 row)

test=# set ora_input_emptystr_isnull=on;

SET

test=# select * from t1 where name is null;

id | name---+------2  |(1 row)

test=# select * from t1 where name ''; --虽然开启set ora_input_emptystr_isnull，对先前插入的数据无效id | name---+------(0 rows)

test=# select length(name) from t1 where id=1;

length--------0(1 row)

test=# select * from ti where name is not null;

id | name---+------1 |(1 row)

测试结论：数据内部对于 ‘’ 与 null值的存储是不同的，当ora_input_emptystr_isnull=on 时，不管的insert ‘’， 还是 where col=’’ ，’’ 都会转为 null ，

2.1.5. Ora_input_emptystr_isnull对于数值类型空值的影响 ¶

问题描述：

对于integer类型数据，当ora_input_emptystr_isnull=off时，’’对于insert 或 select 都会因为类型转换问题报错。

分析与解决方法：

当ora_input_emptystr_isnull=off时，’’当做字符串，无法转换成整型。当ora_input_emptystr_isnull=on 时，’’被转成null，而null 没有类型约束，所以转换没有问题。

1) 当ora_input_emptystr_isnull=on ，insert 与 select 对于 ‘’ 都没问题

test=# show ora_input_emptystr_isnull;

ora_input_emptystr_isnull-------------------------on(1 row)

test=# create table t1 (id1 integer,id2 integer);

CREATE TABLE

test=# insert intc t1 value(1,null);

INSERT 0 1

test=# insert inte tl values(2,'');--可以insert ''INSERT 0 1

test=# select * from t1 where id2 is null;

id1 | id2----+-------1  |2  |(2 rows)

test=# select * trom tl where id2='';id1 | id2----+-------(0 row)

2) 当ora_input_emptystr_isnull=off ，insert 与 select 对于 ‘’都有问题

test=# show ora_input_emptystr_isnull;ora_input_emptystr_isnull-------------------------off(1 row)

test=# select * from t1 where id2 is null; 

 id1 | id2  ----+-------  1  |  2  |(2 rows)

test=# select * from t1 where id2='';--对于条件转换有问题ERROR: invalid input syntax for type integer:""LINE 1:select * from tl where id2='';

test=# create table t1 (id1 integer,id2 integer);

CREATE TABLE

test=# insert intc t1 value(1,null);INSERT 0 1

test=# insert into tl values(2,'');-

-不可以insert ''ERROR: invalid input synlax for type integer:""LINE 1: insert into t1 values(2,'');