mysqlでJSON配列を行データに変換する

26001 ワード

文書ディレクトリ

一、背景

二、基本知識

三、実現原理

一、背景
mysqlテーブルには、JSON形式の配列が格納された文字列タイプのフィールドがあります.mysql単一フィールドの長さは限られているため、JSON配列が長すぎると長さオーバーフローの異常が発生しやすいため、このフィールドを独立したmysqlテーブルに変換します.
この文書は既知のJSON配列の最大長のシーンに適用され、一般的には文字列の長さに換算して配列の最大長を得ることができます.
二、基本知識
mysqlは5.7からJSON関数を追加し、文字列のJSONフォーマット変換をサポートします.ここでは主に2つの関数が用いられる:JSON_EXTRACT・JSON_UNQUOTE.

JSON_EXTRACT(json_doc, path[, path] ...)・json_docからJSONドキュメントを解析し、pathパラメータ指定データを返します.いずれかのパラメータがNULLであれば、戻り値もNULLです.json_doc正当なJSONデータでない場合、またはpath正当なパラメータでない場合、例外が放出されます.複数のpathパラメータが指定されている場合、返される結果は自動的に配列にカプセル化され、指定されたパラメータ順にデータがカプセル化されます.pathパラメータが1つしかない場合、返されるデータは1つだけです.例:

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 )
SELECT
    id,
    application_id,
    JSON_UNQUOTE(JSON_EXTRACT(query_id_str , CONCAT('$[', idx, ']'))) AS query_id
FROM application_info
--      ，  JSON     
JOIN (
    SELECT  0  AS idx UNION
    SELECT  1  AS idx UNION
    SELECT  2  AS idx UNION
    SELECT  3  AS idx UNION
    SELECT  4  AS idx UNION
    SELECT  5  AS idx UNION
    SELECT  6  AS idx UNION
    SELECT  7  AS idx UNION
    SELECT  8  AS idx UNION
    SELECT  9  AS idx UNION
    SELECT  10 AS idx UNION
    SELECT  11 AS idx UNION
    SELECT  12 AS idx UNION
    SELECT  13 AS idx UNION
    SELECT  14 AS idx UNION
    SELECT  15 AS idx UNION
    SELECT  16 AS idx UNION
    SELECT  17 AS idx UNION
    SELECT  18 AS idx UNION
    SELECT  19 AS idx UNION
    SELECT  20 AS idx UNION
    SELECT  21 AS idx UNION
    SELECT  22 AS idx UNION
    SELECT  23 AS idx UNION
    SELECT  24 AS idx UNION
    SELECT  25 AS idx UNION
    SELECT  26 AS idx UNION
    SELECT  27 AS idx UNION
    SELECT  28 AS idx UNION
    SELECT  29 AS idx UNION
    SELECT  30 AS idx UNION
    SELECT  31 AS idx UNION
    SELECT  32
    -- query_id_str（1024）    33 query_id（31）
) AS indexes
--      
WHERE JSON_EXTRACT(query_id_str, CONCAT('$[', idx, ']')) IS NOT NULL
ORDER BY id;

React（JSX）をES6で書くときに気をつけること

反射の3つの作成方法