MySql 5.7对json_table()函数的一次变通替代

Table create table PERSON_INFO ( ID NUMBER(15) not null , ADDR_INFO VARCHAR2(1000) CONSTRAINT ADDR_INFO_JSON CHECK (ADDR_INFO IS JSON), PRIMARY KEY ( ID ) ); -- View CREATE OR REPLACE VIEW PERSON_ADDR_VIEW AS 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; -- Function CREATE 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即可：

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

难度较大的是改造函数（存储过程类似，限制更少），经一系列尝试后，用循环取值+比较的方法替代方法成功：

-- Function
DELIMITER /

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的取值范围只有六个数，且在内部唯一，终于找到了替代办法：

-- View
CREATE OR REPLACE VIEW person_addr_view
AS
SELECT ID, ADDRESS_TYPE, ADDRESS_CODE, ADDRESS_DETAIL, ADDRESS_ZIP_CODE
FROM 
   (
   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()函数的一次变通替代

标签：val   附加   版本   场景   实测   char   包括   sts   rac