1 测试目的

本次测试是为了了解在达梦参数COMPATIBLE_MODE在不同值的情况下对NULL和空值的查询影响，以便更好的处理在各种数据库迁移到达梦时的相关问题。

2 测试环境

操作系统：4.19.90-23.6.v2101.ky10.x86_64（麒麟V10）
数据库：1-3-162-2024.10.09-244813-20108-SEC Pack23

3 测试结果

注：只有在兼容ORACLE时，空值、NULL值与其他参数值下的效果不一样。其他参数值下，查询效果一致。

4 测试过程

4.1 测试准备

create table test(c1 varchar(10),c2 varchar(10));
insert into test values(‘1abc’,‘1abc’);
insert into test values(’’,‘2abc’);
insert into test values(‘3abc’,’’);
insert into test values(‘4abc’,null);
insert into test values(null,‘5abc’);
commit;

4.2 不同兼容性查询

4.2.1 COMPATIBLE_MODE=0（默认值）

select * from test;

select * from test where c1 !=’’;

select * from test where c1 is not null;

select * from test where c2 !=’’;

select * from test where c2 is not null;

select * from test where c1 !=’’ and c2 is not null;

select * from test where c1 is not null and c2 !=’’;

4.2.2 COMPATIBLE_MODE=2（兼容ORACLE）

select * from test where c1 !=’’; --’‘在oracle中视为NULL，可以为任何值，无法判定，所以没有结果。

select * from test where c1 is not null;

select * from test where c2 !=’’;

select * from test where c2 is not null;

select * from test where c1 !=’’ and c2 is not null;

select * from test where c1 is not null and c2 !=’’;

4.2.3 COMPATIBLE_MODE=3（兼容MS SQL SERVER）

select * from test where c1 !=’’;

select * from test where c1 is not null;

select * from test where c2 !=’’;

select * from test where c2 is not null;

select * from test where c1 !=’’ and c2 is not null;

select * from test where c1 is not null and c2 !=’’;

4.2.4 COMPATIBLE_MODE=4（兼容MySQL）

select * from test where c1 !=’’;

select * from test where c1 is not null;

select * from test where c2 !=’’;

select * from test where c2 is not null;

select * from test where c1 !=’’ and c2 is not null;

select * from test where c1 is not null and c2 !=’’;

4.2.5 COMPATIBLE_MODE=7（兼容PG）

select * from test where c1 !=’’;

select * from test where c1 is not null;

select * from test where c2 !=’’;

select * from test where c2 is not null;

select * from test where c1 !=’’ and c2 is not null;

select * from test where c1 is not null and c2 !=’’;