本篇內(nèi)容介紹了“MySQL如何將JSON數(shù)組轉(zhuǎn)換為行數(shù)據(jù)”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！
創(chuàng)新互聯(lián)公司堅持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都做網(wǎng)站、成都網(wǎng)站設(shè)計、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時代的興安盟烏蘭浩特網(wǎng)站設(shè)計、移動媒體設(shè)計的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！
一、背景
二、基本知識
三、實現(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ù)相當(dāng)于去掉字符串的雙引號。針對特殊字符串，則會根據(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 |+----------------------------+特殊字符串
普通字符串
為了將JSON數(shù)組轉(zhuǎn)換為行，需要遍歷數(shù)組的所有元素。
通過枚舉下標(biāo)的方式，與JSON數(shù)組進行聯(lián)合查詢，獲得所有數(shù)組元素。
過濾所有空數(shù)據(jù)
3.1 數(shù)據(jù)準(zhǔn)備
-- 創(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í)行引擎中的唯一標(biāo)識。Presto - query_id；YARN - application_id',
PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任務(wù)在執(zhí)行引擎中的唯一標(biāo)識。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ù)組的下標(biāo)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)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！
