今天同事在数据库中插入数据时碰到了问题，于是有了下面的测试：

Oracle数据库

[oracle@node1 ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 16 01:28:57 2024
Version 19.21.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0

#创建表b，name字段not null
SQL> CREATE TABLE b(id int,name varchar2(255)  not null);

Table created.

SQL> insert into b values(1,'');
insert into b values(1,'')
                       *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."B"."NAME")

SQL> insert into b values(1,null);
insert into b values(1,null);
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."B"."NAME")

测试结果：oracle中不可以插入''和null值到not null字段中

MySQL数据库

[root@node1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.4.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.4.0     |
+-----------+
1 row in set (0.00 sec)
mysql>  CREATE TABLE b(id int,name varchar(255)  not null);
Query OK, 0 rows affected (0.04 sec)

mysql>  insert into b values(1,'');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into b values(1,null);
ERROR 1048 (23000): Column 'name' cannot be null

测试结果：MySQL中可以插入''值到not null字段中，不可以插入null到not null字段中

PostgreSQL数据库

[postgres@node1 ~]$ psql
psql (14.8 [By gg])
Type "help" for help.

postgres=# CREATE TABLE b(id int,name varchar(255)  not null);
CREATE TABLE
postgres=# drop table b;
DROP TABLE
postgres=# select version();
                                                     version                                                     
-----------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.8 [By gg] on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=# CREATE TABLE b(id int,name varchar(255)  not null);
CREATE TABLE
postgres=# insert into b values(1,'');
INSERT 0 1
postgres=# insert into b values(1,null);
ERROR:  null value in column "name" of relation "b" violates not-null constraint
DETAIL:  Failing row contains (1, null).

测试结果：PostgreSQL中可以插入''值到not null字段中，不可以插入null到not null字段中

MogDB数据库

root@modb:~# su - omm
omm@modb:~$ gsql -d postgres -r
gsql ((MogDB 5.0.0 build 503a9ef7) compiled at 2023-06-26 16:30:46 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=#select version();
                                                                        version                                 
                                       
----------------------------------------------------------------------------------------------------------------
---------------------------------------
 (MogDB 5.0.0 build 503a9ef7) compiled at 2023-06-26 16:30:46 commit 0 last mr 1804  on aarch64-unknown-linux-gn
u, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

MogDB=# CREATE TABLE b(id int,name varchar2(255)  not null);
CREATE TABLE
MogDB=#insert into b values(1,null);
ERROR:  null value in column "name" violates not-null constraint
DETAIL:  Failing row contains (1, null).

MogDB=#insert into b values(1,'');
ERROR:  null value in column "name" violates not-null constraint
DETAIL:  Failing row contains (1, null).

测试结果：MogDB中不可以插入''和null值到not null字段中

磐维数据库

在磐维数据库中，字段not null约束的用法依赖于安装数据库时所选的兼容模式，兼容模式不同，not null的用法就不太相同。dbcompatibility取值如下：
dbcompatibility=A，兼容oracle，
dbcompatibility=B，兼容mysql，
dbcompatibility=C，兼容Teradata，
dbcompatibility=PG，兼容PG，
dbcompatibility=MSSQL，兼容sql server，

结论

Oracle和MogDB一样，都不可以插入''和null值到not null字段中
MySQL和Postgres一样，可以插入''值到not null字段中，不可以插入null到not null字段中
磐维数据库:依赖于安装数据库时所选的兼容模式，兼容模式不同，not null的用法就不太相同。

把学到的东西分享出来就是一种快乐，大家一起成长进步~