MySql 5.7对json_table()函数的一次变通替代
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySql 5.7对json_table()函数的一次变通替代,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5559字,纯文字阅读大概需要8分钟。
内容图文
![MySql 5.7对json_table()函数的一次变通替代](/upload/InfoBanner/zyjiaocheng/493/79fe8b359cb349f288d7c315ad815b55.jpg)
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
本文系统来源:http://www.cnblogs.com/wggj/p/7729909.html
内容总结
以上是互联网集市为您收集整理的MySql 5.7对json_table()函数的一次变通替代全部内容,希望文章能够帮你解决MySql 5.7对json_table()函数的一次变通替代所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。