MySql 5.7對json_table()函數的一次變通替代

標籤：val   附加   版本   情境   實測   char   包括   sts   rac   

一、前言

目前項目使用的資料庫是Oracle 12c，選擇該版本的的主要原因之一是支援json。某日，某變態客戶說要轉向雲平台，而雲平台卻沒有Oracle只有MySql及其它開源產品。在把建庫指令碼（包括表、視圖、索引、觸發器、預存程序等等）由oracle版轉為mysql版的過程中，最煩人的一個難關是如何?json_table()函數的替代。

Oracle版指令碼在多處使用了json_value()函數，少數幾處使用了json_table()。轉為MySql版時，json_value()可以替換為json_extract()，但json_table()……眾所周知，MySql的函數不能返回表類型的變數，因此根本沒有對應函數，也沒提供對應的思路。曾經通過Google搜尋到一段很複雜的樣本sql代碼，可當時的確看不明白，也就談不上改造，而現在連Google也上不去，只能另尋出路。

二、Oracle指令碼

去除不必要的內容，與json_table()相關的Oracle版指令碼如下：

-- Tablecreate table PERSON_INFO (   ID          NUMBER(15)           not null ,   ADDR_INFO   VARCHAR2(1000) CONSTRAINT ADDR_INFO_JSON CHECK (ADDR_INFO IS JSON),   PRIMARY KEY ( ID ));-- ViewCREATE OR REPLACE VIEW PERSON_ADDR_VIEWAS SELECT   PI.ID ID,   TEMP_TAB.ADDRESS_CODE,   TEMP_TAB.ADDRESS_DETAIL,   TEMP_TAB.ADDRESS_TYPE,   TEMP_TAB.ADDRESS_ZIP_CODE FROM PERSON_INFO PI,   JSON_TABLE(PI.ADDR_INFO, ‘$[*]‘ COLUMNS (ADDRESS_CODE VARCHAR2 PATH ‘$.AddressCode‘, ADDRESS_DETAIL VARCHAR2 PATH ‘$.AddressDetail‘,             ADDRESS_TYPE VARCHAR2 PATH ‘$.AddressType‘, ADDRESS_ZIP_CODE VARCHAR2 PATH ‘$.AddressZipCode‘)) TEMP_TAB;-- FunctionCREATE OR REPLACE FUNCTION GET_ADDR_NAME_BY_TYPE (P_ADDR_INFO IN VARCHAR2, P_ADDR_TYPE IN NUMBER)RETURN VARCHAR2 AS RET_ADDR_NAME VARCHAR2(200);BEGIN  IF P_ADDR_INFO IS NULL THEN RETURN ‘0‘ ;END IF;  SELECT T.ADDRESS_NAME INTO RET_ADDR_NAME 
    FROM JSON_TABLE(P_ADDR_INFO, ‘$[*]‘ COLUMNS (ADDRESS_NAME VARCHAR2 PATH ‘$.AddressDetail‘, ADDRESS_TYPE VARCHAR2 PATH ‘$.AddressType‘)) T  
    WHERE T.ADDRESS_TYPE = P_ADDR_TYPE;  RETURN  RET_ADDR_NAME;END HS_GET_ADDR_NAME_BY_TYPE;/

json欄位的一個樣本：

[{"AddressType":1, "AdrressCode":"Code 1", "AdreessDetail":"aaaa", "AddressZipCode":"100010"}, {"AddressType":2, "AdrressCode":"Code 2", "AdreessDetail":"bbbb", "AddressZipCode":"200020"}, {"AddressType":5, "AdrressCode":"Code 1", "AdreessDetail":"xxxx", "AddressZipCode":"500050"}]

三、MySql指令碼

最簡單的是改造表，直接將欄位類型改為JSON即可：

-- Tablecreate table PERSON_INFO (   ID          DECIMAL(15)           not null ,   ADDR_INFO   JSON,   PRIMARY KEY ( ID ));

難度較大的是改造函數（預存程序類似，限制更少），經一系列嘗試後，用迴圈取值+比較的方法替代方法成功：

-- FunctionDELIMITER /DROP FUNCTION IF EXISTS GET_ADDR_NAME_BY_TYPE/CREATE FUNCTION GET_ADDR_NAME_BY_TYPE (P_ADDR_INFO VARCHAR(1000), P_ADDR_TYPE DECIMAL)RETURNS VARCHAR(200)BEGIN   DECLARE RET_ADDR_NAME VARCHAR(200);   DECLARE RET_ADDR_TYPE int;   DECLARE n int;   DECLARE i int;   IF P_ADDR_INFO IS NULL THEN RETURN ‘0‘ ;END IF;   -- SELECT T.ADDRESS_NAME INTO RET_ADDR_NAME FROM JSON_TABLE(P_ADDR_INFO, ‘$[*]‘ COLUMNS (ADDRESS_NAME VARCHAR2 PATH ‘$.AddressDetail‘, ADDRESS_TYPE VARCHAR2 PATH ‘$.AddressType‘)) T  WHERE T.ADDRESS_TYPE = P_ADDR_TYPE;   SELECT json_length(P_ADDR_INFO) into n;   set i = 0;   WHILE i<n DO      SELECT json_extract(P_ADDR_INFO, concat(‘$[‘, i, ‘].AddressDetail‘)),              json_extract(P_ADDR_INFO, concat(‘$[‘, i, ‘].AddressType‘))       INTO RET_ADDR_NAME, RET_ADDR_TYPE FROM DUAL;            IF RET_ADDR_TYPE=P_ADDR_TYPE THEN return RET_ADDR_NAME; END if;      set i = i+1;   END WHILE;   RETURN ‘0‘;END;/DELIMITER ;

效率低一些，但在多數場合也都適用。

最難纏的是改造視圖，MySql不支援表函數，VIEW定義裡又不能有附加操作（比如轉存到暫存資料表），一開始真實一籌莫展……

後來瞭解到每個json裡的AddressType的取值範圍只有六個數，且在內部唯一，終於找到了替代辦法：

-- ViewCREATE OR REPLACE VIEW person_addr_viewASSELECT ID, ADDRESS_TYPE, ADDRESS_CODE, ADDRESS_DETAIL, ADDRESS_ZIP_CODEFROM    (   SELECT      PI.ID ID,       json_extract(PI.ADDR_INFO, ‘$[0].AddressType‘) ADDRESS_TYPE,       json_extract(PI.ADDR_INFO, ‘$[0].AddressCode‘) ADDRESS_CODE,       json_extract(PI.ADDR_INFO, ‘$[0].AddressDetail‘) ADDRESS_DETAIL,      json_extract(PI.ADDR_INFO, ‘$[0].AddressZipType‘) ADDRESS_ZIP_CODE   FROM MIS_PERSON_TEXT_INFO PI   UNION ALL   SELECT      PI.ID ID,       json_extract(PI.ADDR_INFO, ‘$[1].AddressType‘) ADDRESS_TYPE,       json_extract(PI.ADDR_INFO, ‘$[1].AddressCode‘) ADDRESS_CODE,       json_extract(PI.ADDR_INFO, ‘$[1].AddressDetail‘) ADDRESS_DETAIL,      json_extract(PI.ADDR_INFO, ‘$[1].AddressZipType‘) ADDRESS_ZIP_CODE   FROM MIS_PERSON_TEXT_INFO PI   UNION ALL   SELECT      PI.ID ID,       json_extract(PI.ADDR_INFO, ‘$[2].AddressType‘) ADDRESS_TYPE,       json_extract(PI.ADDR_INFO, ‘$[2].AddressCode‘) ADDRESS_CODE,       json_extract(PI.ADDR_INFO, ‘$[2].AddressDetail‘) ADDRESS_DETAIL,      json_extract(PI.ADDR_INFO, ‘$[2].AddressZipType‘) ADDRESS_ZIP_CODE   FROM MIS_PERSON_TEXT_INFO PI   UNION ALL   SELECT      PI.ID ID,       json_extract(PI.ADDR_INFO, ‘$[3].AddressType‘) ADDRESS_TYPE,       json_extract(PI.ADDR_INFO, ‘$[3].AddressCode‘) ADDRESS_CODE,       json_extract(PI.ADDR_INFO, ‘$[3].AddressDetail‘) ADDRESS_DETAIL,      json_extract(PI.ADDR_INFO, ‘$[3].AddressZipType‘) ADDRESS_ZIP_CODE   FROM MIS_PERSON_TEXT_INFO PI   UNION ALL   SELECT      PI.ID ID,       json_extract(PI.ADDR_INFO, ‘$[4].AddressType‘) ADDRESS_TYPE,       json_extract(PI.ADDR_INFO, ‘$[4].AddressCode‘) ADDRESS_CODE,       json_extract(PI.ADDR_INFO, ‘$[4].AddressDetail‘) ADDRESS_DETAIL,      json_extract(PI.ADDR_INFO, ‘$[4].AddressZipType‘) ADDRESS_ZIP_CODE   FROM MIS_PERSON_TEXT_INFO PI   UNION ALL   SELECT      PI.ID ID,       json_extract(PI.ADDR_INFO, ‘$[5].AddressType‘) ADDRESS_TYPE,       json_extract(PI.ADDR_INFO, ‘$[5].AddressCode‘) ADDRESS_CODE,       json_extract(PI.ADDR_INFO, ‘$[5].AddressDetail‘) ADDRESS_DETAIL,      json_extract(PI.ADDR_INFO, ‘$[5].AddressZipType‘) ADDRESS_ZIP_CODE   FROM PERSON_INFO PI   ) union_tab   WHERE ADDRESS_TYPE IS NOT NULL; 

最後的WHERE條件是防止出現全NULL行，對於‘$[n].‘裡的n，如果大於等於json_length()的值，json_extract()返回NULL。

這效能低得連自己都覺得不好意思，至於適用範圍更是有限，換個情境就很難說能適用。但畢竟項目可以使用，不必對前台代碼傷筋動骨（一般修改仍不可避免）。

四、備忘

• 以上代碼都已通過實測，由於測試環境資料量小，效能資料誤差大，這裡不給出；
• MySql 從5.7開始支援json，Oracle 從12c開始支援json；
• MySql 8（直接跳過6和7）將支援json_table()，以及其它表函數。

MySql 5.7對json_table()函數的一次變通替代