聊聊不同数据库对空串空值处理方式的异同与业务侧的应对方法

1.问题现象

随着信创的持续推进，我们对接的数据库类型越来越多，而不同的数据库，对空值和空串的处理方式不同，所以在跨数据库类型进行数据同步时，往往会因为这些差异而报错，比如某客户的信创环境，在使用 datax 推送数据到 oceanbase（oracle 模式）时，就遇到了如下报错：ORA-01400: cannot insert Null into ‘(columnA)’.

2.问题原因

该场景的数据链路如下：上游微服务A 将数据落地都数据库A中，该数据库A 在非信创场景是 Oracle，在信创场景是 GoldenDB，然后下游某数据分析平台B 使用 datax 将数据库A中的数据采集到大数据HIVE并进行加工处理，最后再使用 datax 将大数据 HIVE 中的数据同步到下游的数据库B，该数据库B使用的是 oceanbase（oracle 模式）。 整个链路上，有几个技术要点如下：

• 不同数据库对空串和空值的处理方式不同，有的认为空串就是空值，有的则认为空串和空值不同；
• ORACLE派系，比如oracle/ob-oracle/tdh-inceptor，认为空串就是空值：当用户通过SQL插入空值或空串时，数据库计算层会将空串和空值都视为空值，最终保存到存储层的文件系统中的内容，空值和空串是相同的；
• ORALCE外的其它派系，比如mysql/pg/apache hive，认为空串和空值不同：当用户通过SQL插入空值或空串时，数据库计算层不会将空串视为空值，最终保存在存储层的文件系统中的内容，空值和空串是不同的；
• 业务上声明表结构时，限制了所有字段包括该 columnA 字段非空(null)；
• 大多数数据库都有自己的空值处理函数，该函数在 ORACLE中是 nvl(), mysql中是 ifnull(),postgresql中是coalesce();
• 对于认为空串就是空值的ORACLE派系，其自身的空值处理函数nvl/ifnull/coalesce，可以有效地将空串和空值，都转换为指定的值比如空格SPACE;
• 对于认为空串和空值不同的ORACLE外的其它派系，其自身的空值处理函数nvl/ifnull/coalesce，只能将空值转换为指定的值比如空格SPACE，而无法有效地对空串进行转换；

在信创环境下,微服务A使用的数据库A是 goldendb，而数据分析平台B使用的数据库B是 oceanbase（oracle 模式），此时会出现上述问题，其原因是：

• 微服务A将数据写入数据库A GoldenDB 时，虽然使用了数据库空值处理函数ifnull(), 但由于 goldendb认为空串（””）不是空值（null），所以实际插入到 goldendb 中的 columnA 字段值也可能包含空串；
• 此后数据分析平台B 经过 datax 采集和 HIVE SQL 处理后，会尝试通过 datax 将columnA字段的空串（””）同步到数据库B即 ob-oracle, 而此时由于 ob-oralce 认为空串（””）就是空值(null)，又由于业务中声明表结构时限制了该columnA 字段非空(null)，所以此时 ob-oracle数据库就报错了； 非信创环境下数据库A 使用 oracle，不会出现上述问题，其原因是：
• 微服务A 将数据写入数据库A 即 ORACLE 时,使用了数据库空值处理函数nvl(), 由于oracle 认为空串（””）就是空值(null)，所以当 columnA 字段包含空串时，实际插入到 oracle 中的 columnA 字段值，也被转换为了空格SPACE，此后数据分析平台B 经过datax采集和HIVE SQL 处理后，columnA 字段值仍是空格SPACE，所以最终通过 datax同步到数据分析平台B库即 ob-oracle 时，由于字段columnA 的值是空格SPACE，而不是空串（””）也不是空值(null)，ob-oracle自然也就不会报错了；
• 概括起来，该问题的根本原因是：不同数据库对空串和空值的处理方式不同，ORACLE派系认为空串就是空值，非ORACLE派系认为空串不是空值，当数据链路上混合使用不同数据库时，单纯通过数据库自身的空值处理函数 nvl/ifnull/coalesce，不足以有效将空串也处理为空格SPACE，当业务声明表结构时限制字段非空(null)，如果上游使用的是非ORACLE派系的数据库且包含空串，此时将空串插入到下游ORACLE派系的数据库时，就会报上述 “ORA-01400: cannot insert Null“ 的错误。

3.解决方案：数据分析应用侧的改动或上游微服务侧的改动

• 数据分析平台B 使用 datax 将数据库A中的数据采集到大数据HIVE后，在HIVE中使用SQL进行加工处理时，仅仅使用数据库自身的空值处理函数 nvl/ifnull/coalesce 对空值进行处理（HIVE 中的空值处理函数是 nvl()），不能有效将空串也处理为空格 SPACE，所以可以通过 length() 函数来判断字段内容是否为空串，并将空串也转换为空格SPACE，也就是说，数据分析平台B 在 HIVE SQL 中使用 nvl()和length()进行双重保护，将上游的空值和空串都处理为空格了，此后插入下游ORACLE派系数据库时，就没有问题了；
• 该问题更优的解决方案应该是，在数据链路的上层，由微服务A进行处理，避免空串向下游流转，从而在源头侧，从根本上避免空串带来的问题，其具体实现方式，跟 HIVE SQL 类似，使用数据库自身的空值处理函数 nvl/ifnull/coalesce，结合 length() 函数，对空值和空串进行双重保护，将上游的空值和空串都处理为其它值比如 SPACE 空格,此后插入下游ORACLE派系数据库时，就没有问题了；

4.技术背景

• 不同数据库对空串和空值的处理方式不同，有的认为空串就是空值，有的则认为空串和空值不同；
• ORACLE派系，比如oracle/ob-oracle/tdh-inceptor，认为空串就是空值：当用户通过SQL插入空值或空串时，数据库计算层会将空串和空值都视为空值，最终保存到存储层的文件系统中的内容，空值和空串是相同的；
• ORALCE外的其它派系，比如mysql/pg/apache hive，认为空串和空值不同：当用户通过SQL插入空值或空串时，数据库计算层不会将空串视为空值，最终保存在存储层的文件系统中的内容，空值和空串是不同的；
• 对于认为空串就是空值的ORACLE派系，其自身的空值处理函数nvl/ifnull/coalesce，可以有效地将空串和空值，都转换为指定的值比如空格SPACE;
• 对于认为空串和空值不同的ORACLE外的其它派系，其自身的空值处理函数nvl/ifnull/coalesce，只能将空值转换为指定的值比如空格SPACE，而无法有效地对空串进行转换；
• 上述数据库空值处理函数，ORACLE中是 nvl(), mysql中是 ifnull(),postgresql中是coalesce();
• 当数据链路上混合使用不同数据库，跨数据库进行数据同步时，由于不同数据库对空串和空值的处理方式不同，ORACLE派系认为空串就是空值，非ORACLE派系认为空串不是空值，所以当业务上声明表结构时限制字段非空(null)，如果上游使用的是非ORACLE派系的数据库且包含空串，此时将空串插入到下游ORACLE派系的数据库时，就会报上述 “ORA-01400: cannot insert Null“ 的错误；
• 为确保跨数据库进行数据同步的健壮性，仅通过数据库空值处理函数 nvl/ifnull/coalesce 对空值进行保护是不足够的，还需要通过 length() == 0 对空串进行保护：即使用 nvl/ifnull/coalesce 和length() 对空值和空串进行双重保护，将空值和空串都转换为空格SPACE;
• 在大数据领域，由于TDH inceptor/apache hive 是存算分离的，情况更为复杂：应用既可以通过SQL对表数据进行增删改查，也可以绕过 SQL直接使用底层文件系统中特定文件格式的API, 比如 apache ORC 的API，直接读写操作数据库底层存储系统中的 ORC 文件；通过这两种方式插入空串时，底层实际存储到文件系统如 ORC 文件中的数据内容，可能并不一致；通过这两种方式插入空值时，底层实际存储到文件系统如 ORC 文件中的数据内容，也可能并不一致；必要时需要通过 SQL 和 hive --orcfiledump等命令，对比通过SQL查询层查询表数据，和通过 orc api或命令直接查询底层存储系统中文件数据，二者的差异。

5 相关语句

//相关 JAVA 语句
PreparedStatement pstmt = conn.prepareStatement(sql))
pstmt.setNull(2, java.sql.Types.VARCHAR);	//通过 pstmt.setNUll 方法赋空值
pstmt.setString(2, ""); //插入空字符串 - 直接赋值 ""
String s1 = null; pstmt.setString(2, s1);//插入空值-通过JAVA 语句，对对象赋值 null
String s2 = "";pstmt.setString(2, s2);//插入空字符串-通过JAVA 语句，对对象赋值 ""
//相关 SQL 语句
INSERT INTO test1118 VALUES (' ');
INSERT INTO test1118 VALUES ('');
INSERT INTO test1118 VALUES (NULL);
select '',null,length(''),length(null) from dual; //oracle
select nvl(NULL,'b') from dual; //oracle
select IFNULL(NULL,'b') //mysql
SELECT COALESCE(NULL,'a') //pg
SELECT s1,length(s1),nvl(s1,'a') FROM test1118;//oracle
SELECT s1,length(s1),IFNULL(s1,'a') FROM test1118;//mysql
SELECT s1,length(s1),COALESCE(s1,'a') FROM test1118;//pg

图片

oracle

mysql

postgresql