Oracle、MySQL、PostGreSQL、SQL Server-null value
最近几年数据库市场百花齐放,在做跨数据库迁移的数据库选型时,除了性能、稳定、安全、运维、功能、可扩展外,像开发中对于值的处理往往容易被人忽视, 之前写过一篇关于PG区别Oracle在SQL解析缓存的笔记《PostgreSQL 12 : Prepare statement和plan_cache_mode 参数》,这里记录一下null 值在这几个数据库中的区别。
软件版本:Oracle 21c 、SQL Server 2019 、MySQL 8.0 、Mariadb 10.6 、PostGreSQL 13、OpenGauss 2.0
创建测试用例表
CREATE TABLE tab_null(id int, name char(10));
INSERT INTO tab_null VALUES(1,'anbob');
INSERT INTO tab_null VALUES(2,NULL);
测试数据过滤
# oracle
SQL> select * from tab_null where name is null;
ID NAME
---------- ----------
2
SQL> select * from tab_null where name is not null;
ID NAME
---------- ----------
1 anbob
SQL> select * from tab_null where name=null;
no rows selected
SQL> select * from tab_null where null=null;
no rows selected
# postgresql
postgres=# select * from tab_null where name is null;
id | name
----+------
2 |
(1 row)
postgres=# select * from tab_null where name is not null;
id | name
----+------------
1 | anbob
(1 row)
postgres=# select * from tab_null where name=null;
id | name
----+------
(0 rows)
postgres=# select * from tab_null where null=null;
id | name
----+------
(0 rows)
# MySQL/MariaDB
mysql> select * from tab_null where name is null;
+------+------+
| id | name |
+------+------+
| 2 | NULL |
+------+------+
1 row in set (0.00 sec)
mysql> select * from tab_null where name is not null;
+------+-------+
| id | name |
+------+-------+
| 1 | anbob |
+------+-------+
1 row in set (0.00 sec)
mysql> select * from tab_null where name=null;
Empty set (0.00 sec)
mysql> select * from tab_null where null=null;
Empty set (0.00 sec)
# SQL Server
select * from tab_null where name is null;
ID NAME
---------- ----------
2
select * from tab_null where name is not null;
ID NAME
---------- ----------</