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