一、前言
目前项目使用的数据库是Oracle 12c,选择该版本的的主要原因之一是支持json。某日,某变态客户说要转向云平台,而云平台却没有Oracle只有MySql及其它开源产品。在把建库脚本(包括表、视图、索引、触发器、存储过程等等)由oracle版转为mysql版的过程中,最烦人的一个难关是如何实现json_table()函数的替代。
Oracle版脚本在多处使用了json_value()函数,少数几处使用了json_table()。转为MySql版时,json_value()可以替换为json_extract(),但json_table()……众所周知,MySql的函数不能返回表类型的变量,因此根本没有对应函数,也没提供对应的思路。曾经通过谷歌搜索到一段很复杂的示例sql代码,可当时的确看不明白,也就谈不上改造,而现在连谷歌也上不去,只能另寻出路。
二、Oracle脚本
去除不必要的内容,与json_table()相关的Oracle版脚本如下:
--Table
create tablePERSON_INFO
(
IDNUMBER(15) not null,
ADDR_INFOVARCHAR2(1000) CONSTRAINT ADDR_INFO_JSON CHECK (ADDR_INFO ISJSON),PRIMARY KEY( ID )
);--View
CREATE OR REPLACE VIEWPERSON_ADDR_VIEWAS
SELECT
PI.ID ID,
TEMP_TAB.ADDRESS_CODE,
TEMP_TAB.ADDRESS_DETAIL,
TEMP_TAB.ADDRESS_TYPE,
TEMP_TAB.ADDRESS_ZIP_CODEFROM PERSON_INFO PI,
JSON_TABLE(PI.ADDR_INFO, ‘$[*]‘ COLUMNS (ADDRESS_CODE VARCHAR2 PATH ‘$.AddressCode‘, ADDRESS_DETAIL VARCHAR2 PATH ‘$.AddressDetail‘,
ADDRESS_TYPEVARCHAR2 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 ASRET_ADDR_NAMEVARCHAR2(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;RETURNRET_ADDR_NAME;ENDHS_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 tablePERSON_INFO
(
IDDECIMAL(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) inton;set i = 0;WHILE i
json_extract(P_ADDR_INFO, concat(‘$[‘, i, ‘].AddressType‘))INTO RET_ADDR_NAME, RET_ADDR_TYPE FROMDUAL;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 VIEWperson_addr_viewAS
SELECTID, ADDRESS_TYPE, ADDRESS_CODE, ADDRESS_DETAIL, ADDRESS_ZIP_CODEFROM(SELECTPI.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_CODEFROMMIS_PERSON_TEXT_INFO PIUNION ALL
SELECTPI.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_CODEFROMMIS_PERSON_TEXT_INFO PIUNION ALL
SELECTPI.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_CODEFROMMIS_PERSON_TEXT_INFO PIUNION ALL
SELECTPI.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_CODEFROMMIS_PERSON_TEXT_INFO PIUNION ALL
SELECTPI.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_CODEFROMMIS_PERSON_TEXT_INFO PIUNION ALL
SELECTPI.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_CODEFROMPERSON_INFO PI
) union_tabWHERE 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(),以及其它表函数。