mysql如何將JSON數(shù)組轉(zhuǎn)換為行數(shù)據(jù)

本篇內(nèi)容介紹了“mysql如何將JSON數(shù)組轉(zhuǎn)換為行數(shù)據(jù)”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！

文章目錄

• 一、背景

• 二、基本知識

• 三、實現(xiàn)原理

一、背景

在mysql表中有一個字符串類型的字段，里面儲存了JSON格式的數(shù)組。
由于mysql單個字段的長度是有限的，若JSON數(shù)組太長，容易出現(xiàn)長度溢出的異常，所以要將該字段轉(zhuǎn)換為一張獨立的mysql表。

此文檔適用于已知JSON數(shù)組最大長度的場景，一般情況下可以通過字符串的長度換算得到數(shù)組的最大長度。

二、基本知識

mysql從5.7開始，增加了JSON函數(shù)，支持對字符串進行JSON格式轉(zhuǎn)換。這里主要用到兩個函數(shù)：JSON_EXTRACT、JSON_UNQUOTE。

• JSON_EXTRACT(json_doc, path[, path] ...)

  從json_doc中解析JSON文檔，返回path參數(shù)指定的數(shù)據(jù)。如果任一參數(shù)為NULL，那么返回值也是NULL。如果json_doc不是合法的JSON數(shù)據(jù)，或者path不是合法的參數(shù)，都會拋出異常。
  如果提供了多個path參數(shù)，返回結(jié)果會自動封裝為數(shù)組，按照提供的參數(shù)順序封裝數(shù)據(jù)。如果只有一個path參數(shù)，返回結(jié)果就只有一個數(shù)據(jù)。

  示例：

  mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');+--------------------------------------------+| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |+--------------------------------------------+| 20                                         |+--------------------------------------------+mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');+----------------------------------------------------+| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |+----------------------------------------------------+| [20, 10]                                           |+----------------------------------------------------+mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');+-----------------------------------------------+| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |+-----------------------------------------------+| [30, 40]                                      |+-----------------------------------------------+

  
  

• JSON_UNQUOTE(json_val)

  反引文JSON數(shù)據(jù)，返回一個utf8mb4編碼的字符串。如果JSON數(shù)據(jù)為NULL，返回也是NULL。

  針對普通字符串，該函數(shù)相當于去掉字符串的雙引號。針對特殊字符串，則會根據(jù)sql_mode進行轉(zhuǎn)換。此文檔不作詳細介紹。

  示例：

  mysql> SET @j = '"abc"';mysql> SELECT @j, JSON_UNQUOTE(@j);+-------+------------------+| @j    | JSON_UNQUOTE(@j) |+-------+------------------+| "abc" | abc              |+-------+------------------+mysql> SET @j = '[1, 2, 3]';mysql> SELECT @j, JSON_UNQUOTE(@j);+-----------+------------------+| @j        | JSON_UNQUOTE(@j) |+-----------+------------------+| [1, 2, 3] | [1, 2, 3]        |+-----------+------------------+

   

  mysql> SELECT @@sql_mode;+------------+| @@sql_mode |+------------+|            |+------------+mysql> SELECT JSON_UNQUOTE('"\\t\\u0032"');+------------------------------+| JSON_UNQUOTE('"\\t\\u0032"') |+------------------------------+|       2                           |+------------------------------+mysql> SET @@sql_mode = 'NO_BACKSLASH_ESCAPES';mysql> SELECT JSON_UNQUOTE('"\\t\\u0032"');+------------------------------+| JSON_UNQUOTE('"\\t\\u0032"') |+------------------------------+| \t\u0032                     |+------------------------------+mysql> SELECT JSON_UNQUOTE('"\t\u0032"');+----------------------------+| JSON_UNQUOTE('"\t\u0032"') |+----------------------------+|       2                         |+----------------------------+

  
  

• 特殊字符串

• 普通字符串

三、實現(xiàn)原理

為了將JSON數(shù)組轉(zhuǎn)換為行，需要遍歷數(shù)組的所有元素。

• 通過枚舉下標的方式，與JSON數(shù)組進行聯(lián)合查詢，獲得所有數(shù)組元素。

• 過濾所有空數(shù)據(jù)

3.1 數(shù)據(jù)準備

-- 創(chuàng)建原始表CREATE TABLE `application_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `application_id` varchar(100) NOT NULL COMMENT '在執(zhí)行引擎上的任務(wù)ID，如Presto作業(yè)ID，YARN的applicationId',
  `query_id_str` VARCHAR(1024) COMMENT 'JSON數(shù)組' ,
  PRIMARY KEY (`id`),) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='作業(yè)信息表'-- 插入數(shù)據(jù)INSERT INTO `application_info` VALUES ('application_01','[\"20200520_072820_00012_syrpv\",\"20200520_072820_00013_syrpv\"]');-- 創(chuàng)建拆分表CREATE TABLE `application_job_id_of_engine` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  `application_info_id` bigint(20) NOT NULL COMMENT '任務(wù)主鍵',
  `application_id` varchar(100) NOT NULL COMMENT '任務(wù)ID。該字段是冗余字段，方便排查問題時，快速查看DS的任務(wù)ID',
  `job_id` varchar(100) NOT NULL COMMENT '任務(wù)在執(zhí)行引擎中的唯一標識。Presto - query_id；YARN - application_id',
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任務(wù)在執(zhí)行引擎中的唯一標識。Presto - query_id；YARN - application_id'

3.2 遷移數(shù)據(jù)

INSERT INTO application_job_id_of_engine (application_info_id ,application_id , job_id )SELECTid,application_id,JSON_UNQUOTE(JSON_EXTRACT(query_id_str , CONCAT('$[', idx, ']'))) AS query_idFROM application_info-- 內(nèi)嵌關(guān)聯(lián)表，生成JSON數(shù)組的下標JOIN (SELECT  0  AS idx UNIONSELECT  1  AS idx UNIONSELECT  2  AS idx UNIONSELECT  3  AS idx UNIONSELECT  4  AS idx UNIONSELECT  5  AS idx UNIONSELECT  6  AS idx UNIONSELECT  7  AS idx UNIONSELECT  8  AS idx UNIONSELECT  9  AS idx UNIONSELECT  10 AS idx UNIONSELECT  11 AS idx UNIONSELECT  12 AS idx UNIONSELECT  13 AS idx UNIONSELECT  14 AS idx UNIONSELECT  15 AS idx UNIONSELECT  16 AS idx UNIONSELECT  17 AS idx UNIONSELECT  18 AS idx UNIONSELECT  19 AS idx UNIONSELECT  20 AS idx UNIONSELECT  21 AS idx UNIONSELECT  22 AS idx UNIONSELECT  23 AS idx UNIONSELECT  24 AS idx UNIONSELECT  25 AS idx UNIONSELECT  26 AS idx UNIONSELECT  27 AS idx UNIONSELECT  28 AS idx UNIONSELECT  29 AS idx UNIONSELECT  30 AS idx UNIONSELECT  31 AS idx UNIONSELECT  32-- query_id_str（1024）最多存儲33個query_id（31）) AS indexes-- 過濾空數(shù)據(jù)WHERE JSON_EXTRACT(query_id_str, CONCAT('$[', idx, ']')) IS NOT NULLORDER BY id;

“mysql如何將JSON數(shù)組轉(zhuǎn)換為行數(shù)據(jù)”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！