当前位置：Gxlcms > 数据库问题 > MySql 5.7对json_table()函数的一次变通替代

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

时间：2021-07-01 10:21:17 帮助过：141人阅读

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字段的一个示例：

  1. <span style="color: #000000">[{"AddressType":1, "AdrressCode":"Code 1", "AdreessDetail":"aaaa", "AddressZipCode":"100010"},
  2.  {"AddressType":2, "AdrressCode":"Code 2", "AdreessDetail":"bbbb", "AddressZipCode":"200020"},
  3.  {"AddressType":5, "AdrressCode":"Code 1", "AdreessDetail":"xxxx", "AddressZipCode":"500050"}
  4. ]</span>

三、MySql脚本

最简单的是改造表，直接将字段类型改为JSON即可：

  1. <span style="color: #008080">--</span><span style="color: #008080"> Table</span>
  2. <span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span><span style="color: #000000"> PERSON_INFO 
  3. (
  4.    ID          </span><span style="color: #0000ff">DECIMAL</span>(<span style="color: #800000; font-weight: bold">15</span>)           <span style="color: #808080">not</span> <span style="color: #0000ff">null</span><span style="color: #000000"> ,
  5.    ADDR_INFO   JSON,
  6.    </span><span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000"> ( ID )
  7. );</span>

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

  1. <span style="color: #008080">--</span><span style="color: #008080"> Function</span>
  2. DELIMITER <span style="color: #808080">/</span>
  3. <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">FUNCTION</span> <span style="color: #0000ff">IF</span> <span style="color: #808080">EXISTS</span> GET_ADDR_NAME_BY_TYPE<span style="color: #808080">/</span>
  4. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">FUNCTION</span> GET_ADDR_NAME_BY_TYPE (P_ADDR_INFO <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">1000</span>), P_ADDR_TYPE <span style="color: #0000ff">DECIMAL</span><span style="color: #000000">)
  5. </span><span style="color: #0000ff">RETURNS</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">200</span><span style="color: #000000">)
  6. </span><span style="color: #0000ff">BEGIN</span>
  7.    <span style="color: #0000ff">DECLARE</span> RET_ADDR_NAME <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">200</span><span style="color: #000000">);
  8.    </span><span style="color: #0000ff">DECLARE</span> RET_ADDR_TYPE <span style="color: #0000ff">int</span><span style="color: #000000">;
  9.    </span><span style="color: #0000ff">DECLARE</span> n <span style="color: #0000ff">int</span><span style="color: #000000">;
  10.    </span><span style="color: #0000ff">DECLARE</span> i <span style="color: #0000ff">int</span><span style="color: #000000">;
  11.    </span><span style="color: #0000ff">IF</span> P_ADDR_INFO <span style="color: #0000ff">IS</span> <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">THEN</span> <span style="color: #0000ff">RETURN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">0</span><span style="color: #ff0000">‘</span> ;<span style="color: #0000ff">END</span> <span style="color: #0000ff">IF</span><span style="color: #000000">;
  12.    </span><span style="color: #008080">--</span><span style="color: #008080"> 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;</span>
  13.    <span style="color: #0000ff">SELECT</span> json_length(P_ADDR_INFO) <span style="color: #0000ff">into</span><span style="color: #000000"> n;
  14.    </span><span style="color: #0000ff">set</span> i <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">;
  15.    </span><span style="color: #0000ff">WHILE</span> i<span style="color: #808080"><</span><span style="color: #000000">n DO
  16.       </span><span style="color: #0000ff">SELECT</span> json_extract(P_ADDR_INFO, concat(<span style="color: #ff0000">‘</span><span style="color: #ff0000">$[</span><span style="color: #ff0000">‘</span>, i, <span style="color: #ff0000">‘</span><span style="color: #ff0000">].AddressDetail</span><span style="color: #ff0000">‘</span><span style="color: #000000">)), 
  17.              json_extract(P_ADDR_INFO, concat(</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[</span><span style="color: #ff0000">‘</span>, i, <span style="color: #ff0000">‘</span><span style="color: #ff0000">].AddressType</span><span style="color: #ff0000">‘</span><span style="color: #000000">)) 
  18.       </span><span style="color: #0000ff">INTO</span> RET_ADDR_NAME, RET_ADDR_TYPE <span style="color: #0000ff">FROM</span><span style="color: #000000"> DUAL;
  19.       
  20.       </span><span style="color: #0000ff">IF</span> RET_ADDR_TYPE<span style="color: #808080">=</span>P_ADDR_TYPE <span style="color: #0000ff">THEN</span> <span style="color: #0000ff">return</span> RET_ADDR_NAME; <span style="color: #0000ff">END</span> <span style="color: #0000ff">if</span><span style="color: #000000">;
  21.       </span><span style="color: #0000ff">set</span> i <span style="color: #808080">=</span> i<span style="color: #808080">+</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">;
  22.    </span><span style="color: #0000ff">END</span> <span style="color: #0000ff">WHILE</span><span style="color: #000000">;
  23.    </span><span style="color: #0000ff">RETURN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">0</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
  24. </span><span style="color: #0000ff">END</span><span style="color: #000000">;
  25. </span><span style="color: #808080">/</span><span style="color: #000000">
  26. DELIMITER ;</span>

效率低一些，但在多数场合也都适用。

最难缠的是改造视图，MySql不支持表函数，VIEW定义里又不能有附加操作（比如转存到临时表），一开始真实一筹莫展……

后来了解到每个json里的AddressType的取值范围只有六个数，且在内部唯一，终于找到了替代办法：

  1. <span style="color: #008080">--</span><span style="color: #008080"> View</span>
  2. <span style="color: #0000ff">CREATE</span> <span style="color: #808080">OR</span> <span style="color: #ff00ff">REPLACE</span> <span style="color: #0000ff">VIEW</span><span style="color: #000000"> person_addr_view
  3. </span><span style="color: #0000ff">AS</span>
  4. <span style="color: #0000ff">SELECT</span><span style="color: #000000"> ID, ADDRESS_TYPE, ADDRESS_CODE, ADDRESS_DETAIL, ADDRESS_ZIP_CODE
  5. </span><span style="color: #0000ff">FROM</span><span style="color: #000000"> 
  6.    (
  7.    </span><span style="color: #0000ff">SELECT</span><span style="color: #000000">
  8.       PI.ID ID, 
  9.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[0].AddressType</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_TYPE, 
  10.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[0].AddressCode</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_CODE, 
  11.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[0].AddressDetail</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_DETAIL,
  12.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[0].AddressZipType</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_ZIP_CODE
  13.    </span><span style="color: #0000ff">FROM</span><span style="color: #000000"> MIS_PERSON_TEXT_INFO PI
  14.    </span><span style="color: #0000ff">UNION</span> <span style="color: #808080">ALL</span>
  15.    <span style="color: #0000ff">SELECT</span><span style="color: #000000">
  16.       PI.ID ID, 
  17.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[1].AddressType</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_TYPE, 
  18.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[1].AddressCode</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_CODE, 
  19.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[1].AddressDetail</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_DETAIL,
  20.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[1].AddressZipType</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_ZIP_CODE
  21.    </span><span style="color: #0000ff">FROM</span><span style="color: #000000"> MIS_PERSON_TEXT_INFO PI
  22.    </span><span style="color: #0000ff">UNION</span> <span style="color: #808080">ALL</span>
  23.    <span style="color: #0000ff">SELECT</span><span style="color: #000000">
  24.       PI.ID ID, 
  25.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[2].AddressType</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_TYPE, 
  26.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[2].AddressCode</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_CODE, 
  27.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[2].AddressDetail</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_DETAIL,
  28.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[2].AddressZipType</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_ZIP_CODE
  29.    </span><span style="color: #0000ff">FROM</span><span style="color: #000000"> MIS_PERSON_TEXT_INFO PI
  30.    </span><span style="color: #0000ff">UNION</span> <span style="color: #808080">ALL</span>
  31.    <span style="color: #0000ff">SELECT</span><span style="color: #000000">
  32.       PI.ID ID, 
  33.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[3].AddressType</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_TYPE, 
  34.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[3].AddressCode</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_CODE, 
  35.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[3].AddressDetail</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_DETAIL,
  36.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[3].AddressZipType</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_ZIP_CODE
  37.    </span><span style="color: #0000ff">FROM</span><span style="color: #000000"> MIS_PERSON_TEXT_INFO PI
  38.    </span><span style="color: #0000ff">UNION</span> <span style="color: #808080">ALL</span>
  39.    <span style="color: #0000ff">SELECT</span><span style="color: #000000">
  40.       PI.ID ID, 
  41.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[4].AddressType</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_TYPE, 
  42.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[4].AddressCode</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_CODE, 
  43.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[4].AddressDetail</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_DETAIL,
  44.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[4].AddressZipType</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_ZIP_CODE
  45.    </span><span style="color: #0000ff">FROM</span><span style="color: #000000"> MIS_PERSON_TEXT_INFO PI
  46.    </span><span style="color: #0000ff">UNION</span> <span style="color: #808080">ALL</span>
  47.    <span style="color: #0000ff">SELECT</span><span style="color: #000000">
  48.       PI.ID ID, 
  49.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[5].AddressType</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_TYPE, 
  50.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[5].AddressCode</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_CODE, 
  51.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[5].AddressDetail</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_DETAIL,
  52.       json_extract(PI.ADDR_INFO, </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">$[5].AddressZipType</span><span style="color: #ff0000">‘</span><span style="color: #000000">) ADDRESS_ZIP_CODE
  53.    </span><span style="color: #0000ff">FROM</span><span style="color: #000000"> PERSON_INFO PI
  54.    ) union_tab
  55.    </span><span style="color: #0000ff">WHERE</span> ADDRESS_TYPE <span style="color: #0000ff">IS</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span>;

最后的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   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！