mysql如何將JSON數組轉換為行數據

本篇內容介紹了“mysql如何將JSON數組轉換為行數據”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

文章目錄

• 一、背景

• 二、基本知識

• 三、實現原理

一、背景

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

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

二、基本知識

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

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

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

  示例：

  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數據，返回一個utf8mb4編碼的字符串。如果JSON數據為NULL，返回也是NULL。

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

  示例：

  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                         |+----------------------------+

  
  

• 特殊字符串

• 普通字符串

三、實現原理

為了將JSON數組轉換為行，需要遍歷數組的所有元素。

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

• 過濾所有空數據

3.1 數據準備

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

3.2 遷移數據

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-- 內嵌關聯表，生成JSON數組的下標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-- 過濾空數據WHERE JSON_EXTRACT(query_id_str, CONCAT('$[', idx, ']')) IS NOT NULLORDER BY id;

“mysql如何將JSON數組轉換為行數據”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！