今天同事在数据库中插入数据时碰到了问题，于是有了下面的测试：
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字段中
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字段中
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字段中
结论
Oracle和MogDB一样，都不可以插入''和null值到not null字段中
MySQL和Postgres一样，可以插入''值到not null字段中，不可以插入null到not null字段中
把学到的东西分享出来就是一种快乐，大家一起成长进步~
