最近在一个技术群里，有人碰到从MySQL迁移数据到PG系的数据库，供实时数仓进行分析，其中日期类型的字段内容为空值，在使用工具进行同步复制过程中，报错了，其中报错内容如下所示：
SQL 错误 [22007]: ERROR: invalid input syntax for type timestamp: "null"位置：843Error position: line: 126 pos: 842
我就根据报错的信息，在postgresql数据库上稍微做了一下测试，可以确认，往postgresql的timestamp类型的字段写入空值（即null）是可以的，不会报错，至于上述报错问题，还需确认数据源和传输工具上。上面提示，明显是往postgresql的timestamp类型的这个字段写入字符串内容，这跟字段定义的数据类型是不吻合的。后面提出问题的群友说知道原因了，还感谢我的指引，也帮了一个很大的忙。
对于我来说，只是举手之劳，问题也不是很难。作为技术人，也乐于分享自己所知道的技术内容。以前在ITpub博客写了很多技术文章，就是为了把自己所掌握的分享出来，共大家一起学习或者探讨。后面自己懒下来了，很少再往那边的博客写数据库的文章了。其实很有一个主要原因是，我的免密登录ITpub博客的电脑是我之前用的，不在我身边，还有就是之前用北京号注册的，早些年也把北京号的卡注销了，导致我在现在没有办法在我现在用的电脑登录博客。现在主要将阵地转移到了微信公众号这边，后面也把技术分享重点放到了这边的公众号了。
在我们熟悉的关系型数据库中，例如Oracle、MySQL、SQL Server和PostgreSQL,空值 null不是虚拟值，它是有意义的。另外null与“''”是有区别的，null是表示空值，它适合在各种数据类型中表现，而“''”它是空字符串，首先它是属于字符数据类型，它只能存放在字符数据类型的字段，它也不是空格。
通过以下的测试，我稍微总结了几个关于null值的小知识点：
MySQL和PG数据库中，除了字符数据类型char（varchar）等允许“''”插入，其他数据类型不允许插入，因为“''”是空字符，不是空置；
MySQL数据库的字段数据类型定义为timestamp类型时候，默认值不作其他定义的情况下，该字段的默认为CURRENT_TIMESTAMP的值，也即now()函数的取值；
MySQL数据库的字段数据类型定义为timestamp类型时候，当往该字段写入null值，则最终写入为CURRENT_TIMESTAMP的值；
在查询中显示方面，MySQL将字段的空值显示为“NULL”，而Oracle和PG无任何内容展示，或者无任何标识符；
Oracle数据库中，可以将“''”空字符转化为null空值，所以可以将“''”写入任何数据类型的字段中，并无报错，最终存储结果和null表现一致。
在MySQL和PG数据库中，“''”空字符是有长度的，长度为0，而在Oracle中，将“''”转化为null，即为空值，字符非空约束下，前两者允许有空字符。
详细的测试过程，见以下内容。
1、三种数据库在timestamp数据类型处理空值
create table nullable_test(id int,create_dtime timestamp);insert into nullable_test values(1,'');insert into nullable_test values(2,null);commit;
--mysql
mysql> create table nullable_test(id int,create_dtime timestamp);Query OK, 0 rows affected (0.00 sec)mysql> desc nullable_test;+--------------+-----------+------+-----+-------------------+-----------------------------+| Field | Type | Null | Key | Default | Extra |+--------------+-----------+------+-----+-------------------+-----------------------------+| id | int(11) | YES | | NULL | || create_dtime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |+--------------+-----------+------+-----+-------------------+-----------------------------+2 rows in set (0.00 sec)mysql> insert into nullable_test values(1,'');ERROR 1292 (22007): Incorrect datetime value: '' for column 'create_dtime' at row 1mysql> insert into nullable_test values(2,null);Query OK, 1 row affected (0.00 sec)mysql> select * from nullable_test;+------+---------------------+| id | create_dtime |+------+---------------------+| 2 | 2023-05-15 15:09:59 |+------+---------------------+1 row in set (0.00 sec)mysql> select now();+---------------------+| now() |+---------------------+| 2023-05-15 15:12:24 |+---------------------+1 row in set (0.00 sec)mysql> insert into nullable_test(id) values(3);Query OK, 1 row affected (0.00 sec)mysql> select * from nullable_test;+------+---------------------+| id | create_dtime |+------+---------------------+| 2 | 2023-05-15 15:09:59 || 3 | 2023-05-15 15:52:56 |+------+---------------------+2 rows in set (0.00 sec)
--postgresql
postgres=# create table nullable_test(id int,create_dtime timestamp);CREATE TABLEpostgres=#postgres=# \d nullable_test;Table "public.nullable_test"Column | Type | Collation | Nullable | Default--------------+-----------------------------+-----------+----------+---------id | integer | | |create_dtime | timestamp without time zone | | |postgres=#postgres=# insert into nullable_test values(1,'');2023-05-16 06:10:10.644 CST [2762] ERROR: invalid input syntax for type timestamp: "" at character 362023-05-16 06:10:10.644 CST [2762] STATEMENT: insert into nullable_test values(1,'');ERROR: invalid input syntax for type timestamp: ""LINE 1: insert into nullable_test values(1,'');^postgres=# insert into nullable_test values(2,null);INSERT 0 1postgres=# select * from nullable_test;id | create_dtime----+--------------2 |(1 row)postgres=# insert into nullable_test(id) values(3);INSERT 0 1postgres=# select * from nullable_test;id | create_dtime----+--------------2 |3 |(2 rows)
--oracle
07:07:24 sys@MYSOURCE > create table nullable_test(id int,create_dtime timestamp);Table created.07:49:51 sys@MYSOURCE > desc nullable_test;Name Null? Type----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------ID NUMBER(38)CREATE_DTIME TIMESTAMP(6)07:10:20 sys@MYSOURCE > insert into nullable_test values(1,'');1 row created.Elapsed: 00:00:00.0007:10:20 sys@MYSOURCE > insert into nullable_test values(2,null);1 row created.07:14:42 sys@MYSOURCE > select * from nullable_test;ID CREATE_DTIME-------------------- ---------------------------------------------------------------------------12Elapsed: 00:00:00.00
2、三种数据库在date（或datetime）数据类型处理空值
--table for mysql
create table nullable_test00(id int,create_dtime datetime);insert into nullable_test00 values(1,'');insert into nullable_test00 values(2,null);
--table for postgresql and oracle
create table nullable_test00(id int,create_dtime date);insert into nullable_test00 values(1,'');insert into nullable_test00 values(2,null);commit;
--mysql
mysql> create table nullable_test00(id int,create_dtime datetime);Query OK, 0 rows affected (0.01 sec)mysql>mysql> desc nullable_test00;+--------------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------------+----------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || create_dtime | datetime | YES | | NULL | |+--------------+----------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> insert into nullable_test00 values(1,'');ERROR 1292 (22007): Incorrect datetime value: '' for column 'create_dtime' at row 1mysql> insert into nullable_test00 values(2,null);Query OK, 1 row affected (0.00 sec)mysql>mysql> select * from nullable_test00;+------+--------------+| id | create_dtime |+------+--------------+| 2 | NULL |+------+--------------+1 row in set (0.01 sec)
--postgresql
postgres=# create table nullable_test00(id int,create_dtime date);CREATE TABLEpostgres=# \d nullable_test00;Table "public.nullable_test00"Column | Type | Collation | Nullable | Default--------------+---------+-----------+----------+---------id | integer | | |create_dtime | date | | |postgres=# insert into nullable_test00 values(1,'');2023-05-16 06:20:23.992 CST [2762] ERROR: invalid input syntax for type date: "" at character 382023-05-16 06:20:23.992 CST [2762] STATEMENT: insert into nullable_test00 values(1,'');ERROR: invalid input syntax for type date: ""LINE 1: insert into nullable_test00 values(1,'');^postgres=# insert into nullable_test00 values(2,null);INSERT 0 1postgres=#postgres=# select * from nullable_test00;id | create_dtime----+--------------2 |(1 row)
--oracle
07:19:26 sys@MYSOURCE > create table nullable_test00(id int,create_dtime date);Table created.Elapsed: 00:00:00.0107:19:27 sys@MYSOURCE > desc nullable_test00;Name Null? Type----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------ID NUMBER(38)CREATE_DTIME DATE07:19:34 sys@MYSOURCE >07:20:34 sys@MYSOURCE > insert into nullable_test00 values(1,'');1 row created.Elapsed: 00:00:00.0107:20:34 sys@MYSOURCE > insert into nullable_test00 values(2,null);1 row created.Elapsed: 00:00:00.0007:20:35 sys@MYSOURCE > commit;Commit complete.Elapsed: 00:00:00.0007:22:26 sys@MYSOURCE > select * from nullable_test00;ID CREATE_DTIME-------------------- -------------------12Elapsed: 00:00:00.00
3、三种数据库在数字（int或number）数据类型处理空值
create table nullable_test01(id int,age int);insert into nullable_test01 values(1,'');insert into nullable_test01 values(2,null);commit;
--mysql
mysql> create table nullable_test01(id int,age int);Query OK, 0 rows affected (0.00 sec)mysql> desc nullable_test01;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || age | int(11) | YES | | NULL | |+-------+---------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> insert into nullable_test01 values(1,'');ERROR 1366 (HY000): Incorrect integer value: '' for column 'age' at row 1mysql> insert into nullable_test01 values(2,null);Query OK, 1 row affected (0.00 sec)mysql>mysql> select * from nullable_test01;+------+------+| id | age |+------+------+| 2 | NULL |+------+------+1 row in set (0.00 sec)--默认值测试mysql> insert into nullable_test01(id) values(3);Query OK, 1 row affected (0.00 sec)mysql> select * from nullable_test01;+------+------+| id | age |+------+------+| 2 | NULL || 3 | NULL |+------+------+2 rows in set (0.00 sec)
--postgresql
postgres=# create table nullable_test01(id int,age int);CREATE TABLEpostgres=# \d nullable_test01;Table "public.nullable_test01"Column | Type | Collation | Nullable | Default--------+---------+-----------+----------+---------id | integer | | |age | integer | | |postgres=#postgres=# insert into nullable_test01 values(1,'');2023-05-16 06:27:31.368 CST [2762] ERROR: invalid input syntax for integer: "" at character 382023-05-16 06:27:31.368 CST [2762] STATEMENT: insert into nullable_test01 values(1,'');ERROR: invalid input syntax for integer: ""LINE 1: insert into nullable_test01 values(1,'');^postgres=# insert into nullable_test01 values(2,null);INSERT 0 1postgres=#postgres=# select * from nullable_test01;id | age----+-----2 |(1 row)--默认值测试postgres=# insert into nullable_test01(id) values(3);INSERT 0 1postgres=# select * from nullable_test01;id | age----+-----2 |3 |(2 rows)
--oracle
07:24:46 sys@MYSOURCE > create table nullable_test01(id int,age int);Table created.Elapsed: 00:00:00.0107:24:48 sys@MYSOURCE >07:27:01 sys@MYSOURCE > desc nullable_test01;Name Null? Type----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------ID NUMBER(38)AGE NUMBER(38)07:27:56 sys@MYSOURCE > insert into nullable_test01 values(1,'');1 row created.Elapsed: 00:00:00.0007:27:56 sys@MYSOURCE > insert into nullable_test01 values(2,null);1 row created.Elapsed: 00:00:00.0007:27:58 sys@MYSOURCE > commit;Commit complete.Elapsed: 00:00:00.0007:28:01 sys@MYSOURCE > select * from nullable_test01;ID AGE-------------------- --------------------12Elapsed: 00:00:00.0107:28:59 sys@MYSOURCE >
4、三种数据库在字符（char或varchar）数据类型处理空值
create table nullable_test02(id int,name char(10));insert into nullable_test02 values(1,'');insert into nullable_test02 values(2,null);commit;
--mysql
mysql> create table nullable_test02(id int,name char(10));Query OK, 0 rows affected (0.01 sec)mysql>mysql> desc nullable_test02;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || name | char(10) | YES | | NULL | |+-------+----------+------+-----+---------+-------+2 rows in set (0.01 sec)mysql> insert into nullable_test02 values(1,'');Query OK, 1 row affected (0.00 sec)mysql> insert into nullable_test02 values(2,null);Query OK, 1 row affected (0.00 sec)mysql> select * from nullable_test02;+------+------+| id | name |+------+------+| 1 | || 2 | NULL |+------+------+2 rows in set (0.00 sec)
--postgresql
postgres=# create table nullable_test02(id int,name char(10));CREATE TABLEpostgres=#postgres=# \d nullable_test02;Table "public.nullable_test02"Column | Type | Collation | Nullable | Default--------+---------------+-----------+----------+---------id | integer | | |name | character(10) | | |postgres=# insert into nullable_test02 values(1,'');INSERT 0 1postgres=# insert into nullable_test02 values(2,null);INSERT 0 1postgres=# select * from nullable_test02;id | name----+------------1 |2 |(2 rows)
--oracle
07:28:59 sys@MYSOURCE > create table nullable_test02(id int,name char(10));Table created.Elapsed: 00:00:00.0207:34:49 sys@MYSOURCE >07:35:11 sys@MYSOURCE > desc nullable_test02;Name Null? Type----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------ID NUMBER(38)NAME CHAR(10)07:35:13 sys@MYSOURCE > insert into nullable_test02 values(1,'');1 row created.Elapsed: 00:00:00.0007:37:52 sys@MYSOURCE > insert into nullable_test02 values(2,null);1 row created.Elapsed: 00:00:00.0007:37:53 sys@MYSOURCE > commit;Commit complete.Elapsed: 00:00:00.0007:38:01 sys@MYSOURCE > select * from nullable_test02;ID NAME-------------------- ------------------------------------------------------------12
5、三种数据库在boolean数据类型处理空值
--table for mysql and postgresql
create table nullable_test03(id int,is_commited boolean);insert into nullable_test03 values(1,'');insert into nullable_test03 values(2,null);commit;
--mysql
mysql> create table nullable_test03(id int,is_commited boolean);Query OK, 0 rows affected (0.01 sec)mysql>mysql> insert into nullable_test03 values(1,'');ERROR 1366 (HY000): Incorrect integer value: '' for column 'is_commited' at row 1mysql> insert into nullable_test03 values(2,null);Query OK, 1 row affected (0.00 sec)mysql> desc nullable_test03;+-------------+------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------------+------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || is_commited | tinyint(1) | YES | | NULL | |+-------------+------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql>mysql> select * from nullable_test03;+------+-------------+| id | is_commited |+------+-------------+| 2 | NULL |+------+-------------+1 row in set (0.00 sec)
--postgresql
postgres=# create table nullable_test03(id int,is_commited boolean);CREATE TABLEpostgres=#postgres=# insert into nullable_test03 values(1,'');2023-05-16 06:45:54.893 CST [2762] ERROR: invalid input syntax for type boolean: "" at character 382023-05-16 06:45:54.893 CST [2762] STATEMENT: insert into nullable_test03 values(1,'');ERROR: invalid input syntax for type boolean: ""LINE 1: insert into nullable_test03 values(1,'');^postgres=# insert into nullable_test03 values(2,null);INSERT 0 1postgres=# \d nullable_test03;Table "public.nullable_test03"Column | Type | Collation | Nullable | Default-------------+---------+-----------+----------+---------id | integer | | |is_commited | boolean | | |postgres=# select * from nullable_test03;id | is_commited----+-------------2 |(1 row)
--oracle
07:42:07 sys@MYSOURCE > create table nullable_test03(id int,is_commited boolean);create table nullable_test03(id int,is_commited boolean)*ERROR at line 1:ORA-00902: invalid datatype
##Oracle 11.2版本中未支持Boolean数据类型，所以在建表过程中会报错，但在Oracle 23C中已经支持该数据类型。
6、三种数据库在字符数据类型与非空约束下处理空值
create table nullable_test04(id int,name char(10) not null);insert into nullable_test04 values(1,'');insert into nullable_test04 values(2,null);insert into nullable_test04(id) values(3);commit;
--mysql
mysql> create table nullable_test04(id int,name char(10) not null);Query OK, 0 rows affected (0.05 sec)mysql> desc nullable_test04;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || name | char(10) | NO | | NULL | |+-------+----------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> insert into nullable_test04 values(1,'');Query OK, 1 row affected (0.01 sec)mysql> insert into nullable_test04 values(2,null);ERROR 1048 (23000): Column 'name' cannot be nullmysql> insert into nullable_test04(id) values(3);ERROR 1364 (HY000): Field 'name' doesn't have a default valuemysql>mysql> select * from nullable_test04;+------+------+| id | name |+------+------+| 1 | |+------+------+1 row in set (0.00 sec)
--postgresql
postgres=# create table nullable_test04(id int,name char(10) not null);CREATE TABLEpostgres=#postgres=#postgres=# \d nullable_test04;Table "public.nullable_test04"Column | Type | Collation | Nullable | Default--------+---------------+-----------+----------+---------id | integer | | |name | character(10) | | not null |postgres=#postgres=# insert into nullable_test04 values(1,'');INSERT 0 1postgres=# insert into nullable_test04 values(2,null);ERROR: null value in column "name" violates not-null constraintDETAIL: Failing row contains (2, null).postgres=#postgres=# insert into nullable_test04(id) values(3);ERROR: null value in column "name" violates not-null constraintDETAIL: Failing row contains (3, null).postgres=#postgres=#postgres=# select * from nullable_test04;id | name----+------------1 |(1 row)
--oracle
00:55:30 sys@MYSOURCE > create table nullable_test05(id int,name varchar(10));19:24:20 sys@MYSOURCE > desc nullable_test04;Name Null? Type----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------------------------------------------------------ID NUMBER(38)NAME NOT NULL CHAR(10)19:26:23 sys@MYSOURCE > insert into nullable_test04 values(1,'');insert into nullable_test04 values(1,'')*ERROR at line 1:ORA-01400: cannot insert NULL into ("SYS"."NULLABLE_TEST04"."NAME")Elapsed: 00:00:00.0019:26:24 sys@MYSOURCE > insert into nullable_test04 values(2,null);insert into nullable_test04 values(2,null)*ERROR at line 1:ORA-01400: cannot insert NULL into ("SYS"."NULLABLE_TEST04"."NAME")Elapsed: 00:00:00.0019:26:39 sys@MYSOURCE > commit;Commit complete.Elapsed: 00:00:00.0019:28:27 sys@MYSOURCE > insert into nullable_test04(id) values(3);insert into nullable_test04(id) values(3)*ERROR at line 1:ORA-01400: cannot insert NULL into ("SYS"."NULLABLE_TEST04"."NAME")Elapsed: 00:00:00.0019:28:29 sys@MYSOURCE >19:29:56 sys@MYSOURCE > select * from nullable_test04;no rows selected
接下来的一篇，将讲述null在排序中的处理情况，敬请期待。
（文毕）
文章转载自skylines，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
