【MySQL】JSONデータの処理

詳細
ビジネスには柔軟なデータ構造が必要
通常、MySQLのようなリレーショナル・データベースを使用する場合は、いくつかのガイドラインに従ってテーブル構造を設計します.
しかし、実際のシーンの適用は、「厳格な単一のガイドライン」とは異なります.実際の状況では多方面のバランスを考慮して妥協する必要があるからだ.
例えば、データベースの原理を学んだばかりの頃は、BCパターンを満たすテーブル構造を設計したり、少なくとも第3パターンを満たすテーブル構造を設計したりする傾向があります.
しかし、実際の工事問題を解決する際に、これらのモデルの要求を満たすことができない表構造設計の決議を下す可能性があります.これらのデザインは当時、良い選択だったかもしれません(後で私たちは自分を大いに批判するかもしれません).
たとえば、start_time、end_time、elapsedの3つのフィールドが共存し、elapsed=end_time - start_timeは計算量を減らす.これは第3のパターンを満たしていません
たとえば、「user_id」と「user_name」の2つのフィールドをtaskレコードに共存させて、連結テーブルクエリを減らすことができます.これは第2のパターンを満たしていません
私たちが今お話しするJSONタイプのフィールドは、「表中の表」を招き、第1のパターンを満たしていません.
 
SQL(Structured Query Language、構造化クエリー言語)式のデータ操作方式が比較的固化しているため、現実的な応用では柔軟性の需要がしばしば現れる.
このような問題を解決するためにさまざまなNoSQLデータベースがありますが、各方面のコストを考慮すると、MySQLにデータを存在させることがあります.すなわち、一部のデータにはそれぞれ1つのフィールド固化が割り当てられ、1つのフィールドには他のデータが複合された値が格納される.
例えば、Taskテーブルに対して、「id」、「name」、「type」などのデータをそれぞれ1つのフィールドに割り当てて硬化することができます.
一方、「arg」の構造は「type」によって異なるため、Taskの各パラメータが複合したデータを格納する「arg」フィールドを設定することができます.
これらの複合データフィールドの解析規則(すなわち、シーケンス化と逆シーケンス化)をカスタマイズできます.もちろん、このようなフィールドのデータ構造基準としてJSONを選択することが多い.
 
MySQL JSONタイプフィールド
以前は、このような複合データフィールドのタイプとしてMySQLのVARCHARやTEXTなどが一般的でした.
5.7.8から、MySQLはJSONを標準のフィールドタイプの一つとしています.
JSON形式のテキストフィールドと比較して、JSONタイプのフィールドは以下の利点があります.

自動チェックJSON形式.追加されたデータがJSON仕様に合致しない場合は、エラーが発生します.

注意:MySQLの合法的なJSON文字列フォーマットは、通常処理されているJSONデータとは異なる場合があります.あるシーンではJSON文字列をオブジェクトに解析することに慣れています({...})または配列([...])、単一の値を考慮しない場合(例:1).

ストレージフォーマットが最適化されました.JSONコンテンツ項目の読み取り速度が速い.

MySQLが提供する内部データ構造により、他のデータを処理することなく、コンテンツアイテムのkeyまたはindexを介してターゲットデータに直接アクセスできます.従来の上位アプリケーションよりもブロック全体のコンテンツを読み出して再解析する方法が速い.

注意:

JSONフィールドに格納できるデータ量は大きいがmax_allowed_packetの制限

JSONフィールドのデフォルト値を指定することはできません.すなわち、JSONフィールドのデフォルト値はNULL

です.

JSON経由は可能ですがEXTRACTメソッドはGenerated Columnフィールドを作成し、このフィールドからインデックスを作成します.

しかし、この方法の意味は議論に値する.このフィールドの情報をJSONフィールドに入れる以上、Generated Columnであっても「汚染」が重すぎることを意味する可能性があるからだ.

例
テーブルの作成
 

CREATE TABLE `t1` (
  `id` INT NOT NULL,
  `f1` VARCHAR(45) NULL,
  `f2` JSON NULL,
  PRIMARY KEY (`id`));

 
 
JSONフィールドデータの作成

方式1:シーケンス化されたJSONテキストをフィールド

に直接格納する.

insert into t1 values (1, 'alpha', '{"a":1, "b":"two"}');

 
 

方式2:JSONを使用OBJECT、JSON_ARRAY、JSON_MERGE等の方法によるオブジェクト作成

insert into t1 values (1, 'alpha', JSON_OBJECT("a", 1, "b", "two"));

insert into t1 values (2, 'beta', JSON_ARRAY("i1", 2, 3.4));

insert into t1 values (
  1,
  'alpha',
  JSON_MERGE(
    '{"a":1}',
    '{"b":"two"}'
  )
);

insert into t1 values (
  1,
  'alpha',
  JSON_MERGE(
    JSON_OBJECT("a", 1),
    JSON_OBJECT("b", "two")
  )
);

insert into t1 values (
  1,
  'alpha',
  JSON_MERGE(
    JSON_OBJECT("a", 1),
    '{"b": "two"}'
  )
);

 
 
注意:

MySQL 5.7.22より、JSON_MERGE被JSON_MERGE_PRESERVE代替

JSONフィールド内部の業務データ構造を計画し、自分で

を混乱させないでください.

またJSON_TYPEメソッドJSONフィールドのタイプ

を表示

select f2, JSON_TYPE(f2) from t1;

+----------------------+---------------+
| f2                   | json_type(f2) |
+----------------------+---------------+
| {"a": 1, "b": "two"} | OBJECT        |
| ["1", 2, 3.4]        | ARRAY         |
+----------------------+---------------+

 
JSONフィールドの内容項目を読み込む

JSON経由可能EXTRACTメソッドJSONフィールドの一部のデータを取得する

 

select f2 from t1 where json_extract(f2, '$.b') = 'two';

+----------------------+
| f2                   |
+----------------------+
| {"a": 1, "b": "two"} |
+----------------------+

 

またはJSONを使用するEXTRACTメソッドの簡略化形式'->

 

select f2 from t1 where f2->'$[1]' = 2;

+---------------+
| f2            |
+---------------+
| ["1", 2, 3.4] |
+---------------+

 
 
JSONフィールドの内容項目の変更
シーケンス化されたJSONテキストを直接フィールドに格納するほか、JSON_INSERT、JSON_REPLACE、JSON_SET、JSON_ARRAY_INSERTなどの方法で異なるニーズを満たす
 

update t1 set f2 = JSON_INSERT(f2, '$.c', '3') where id=1;

update t1 set f2 = JSON_REPLACE(f2, '$.c', '1+1+1') where id=1;

update t1 set f2 = JSON_SET(f2, '$.c', '1+2') where id=1;

 

JSON_INSERT:コンテンツ項目を追加する;コンテンツ項目(key)が既に存在する場合、

は変更されません.

JSON_REPLACE:コンテンツ項目を置換する;コンテンツ項目(key)が存在しない場合、

は変更されません.

JSON_SET:コンテンツ項目の設定;コンテンツ項目(key)がすでに存在する場合は、元の値を置き換えます.コンテンツ項目(key)が存在しない場合、そのコンテンツ項目

を追加する
JSONフィールドの元の値がNULL(JSONフィールドのデフォルト値はNULL)である場合があるため、上記の方法は無効になります.この場合、COALESCEメソッドを使用して初期値を指定できます.
 

update t1 set f2 = JSON_SET(COALESCE(f2, '{}'), '$.a', '1') where id=3;

 
 
JSONフィールドの内容項目を削除
JSON経由REMOVEメソッドコンテンツアイテムの削除
 

update t1 set f2 = JSON_REMOVE(f2, '$.c') where id=1;

 
*一部変更
MySQL 8は、JSONフィールドの一部の内容の変更操作を最適化します.これは、新しいフィールド値全体を作成するのではなく、本当に一部の内容項目だけを変更します.
 
しかし、条件は厳しいです.

で更新されたフィールドはJSONタイプ

でなければなりません.

JSONしか通れませんSET、JSON_REPLACE、JSON_REMOVEの3つの方法はフィールドに

を付与する.

であり、これらのメソッドの入力フィールドは、更新するフィールド

である必要がある.

更新操作は既存のコンテンツ項目のみを操作することができ、コンテンツ項目

を追加することはできない.

新しいフィールド値が占有する記憶領域は、元の値よりも多くてはならない(ターゲットフィールドの元に残っている空間が新しい空間の需要を満たすのに十分であれば、最適化条件にも合致する)

.
システム変数binlog_をrow_value_optionsをPARTIAL_に設定JSONでは、これらの部分的な内容修正の操作もBinary Logに記録されます.
 
 
 
詳細MySQL JSON方法:More
JSON値の比較とソート:Comparison and Ordering of JSON Value(CASTメソッドを補助として使用することが考えられる)