MySQLデータベースでJSONデータフィールドを使用する方法

この記事では、MySQL 9.1でのJSON実装の詳細を調査し、SQLとNOSQLデータベースの境界が前の記事「SQL vs NoSQL：The違い」でぼやけているという議論を継続しています。特徴。 MySQL 5.7 INNODBデータベースとPostgreSQL 9.2の両方が、JSONドキュメントタイプの保存を単一のフィールドに直接サポートします。

データベースは、JSONドキュメントを単一の文字列ブロブとして保存できることに注意する必要があります。ただし、MySQLおよびPostgreSQLは、単純な文字列の代わりに、検証済みのJSONデータを実際のキー価値ペアとして保存することをサポートしています。

キーポイント

MySQL 5.7 INNODBデータベースとPostgreSQL 9.2は、JSONドキュメントタイプを直接サポートしますが、直接インデックスの制限のために注意して使用する必要があります。
JSONは、柔軟性が必要な状況のまばらなデータ、カスタムプロパティ、階層、および状況を保存するのに最適です。データを頻繁にクエリまたはインデックスを作成する正規化された列を置き換えてはなりません。
MySQLは、JSONオブジェクトを作成、検証、検索、変更するさまざまな関数を提供します。これらの関数には、json_array（）、json_object（）、json_quote（）、json_type（）、json_valid（）、json_contains（）、json_search（）、json_merge_は、path nocuments（）などを使用してjsonドキュメントを更新するためのjson_merge_関数。
MySQL 9.1は、JSONデータから派生した生成された列の関数インデックスをサポートし、特定のJSON要素の効率的なクエリを可能にします。
MySQLはJSONをサポートしていますが、それでもリレーショナルデータベースです。 JSONの過剰使用は、SQLの利点を相殺する可能性があります。

JSONドキュメントをmysql json列に保存することは、これを行う必要があるという意味ではありません。 正規化は、データベース構造を最適化するために使用されるテクノロジーです。最初の通常の式（1NF）ルールは、各列が単一の値を保持する必要があることを規定しています。マルチ値JSONドキュメントを保存することは、明らかにこのルールに違反しています。

データに明確なリレーショナルデータ要件がある場合は、適切な単一価値フィールドを使用してください。 JSONは、最後の手段として注意して使用する必要があります。 JSONバリューフィールドは直接インデックス化できないため、頻繁に更新または検索された列でそれらを使用しないでください。

JSONデータから派生した生成された列の関数インデックスを使用すると、JSONオブジェクトの特定の部分をインデックス化することができ、それによりクエリパフォーマンスが向上します。

つまり、JSONには、まばらなデータまたはカスタムプロパティのためのいくつかの良いユースケースがあります。

jsonデータ型列

を使用してテーブルを作成します

本を販売する店を検討してください。すべての本には、ID、ISBN、タイトル、出版社、ページカウント、その他の明確なリレーショナルデータがあります。 さて、各本に多くのカテゴリタグを追加したい場合。次の方法を使用してSQLに実装できます。

1. 各タグ名とその一意のIDを保存するタグテーブル 多くのレコードを含むタグマッピングテーブルは、本IDをタグIDにマッピングします。
この方法は機能しますが、二次機能の場合、面倒すぎてかなりの努力が必要です。したがって、MySQLデータベースの本表のタグのMySQL JSONフィールドを定義できます。

MySQL JSON列にはデフォルト値がない、プライマリキーとして使用することはできず、外部キーとして使用することはできず、直接インデックスを持つことはできません。

ただし、MySQL 9.1を使用すると、JSONデータから派生した生成された列に関数インデックスを作成できます。これにより、JSONドキュメントの特定の要素のインデックス付けが可能になります。これらの生成された列は、仮想または補助インデックスとして保存およびインデックス付けすることができます。

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

jsonデータを追加

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

JSONドキュメント全体を挿入または更新ステートメントに渡すことができ、ストレージと操作のためにJSONをMySQLに簡単に移動できます。

たとえば、本のタグは（文字列内）配列として渡すことができます：

次の関数を使用してJSONを作成することもできます。

JSON_ARRAY（）関数。配列の作成に使用されます。例： - return [1、2、 "abc"]]：json_array（1、2、 'abc'）;

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

オブジェクトを作成するために使用される

json_object（）関数。例： - return {"a"：1、 "b"：2}：select json_object（ 'a'、1、 'b'、2）;

json_quote（）関数。json値として文字列を参照するために使用されます。例： - return "[1、2、" abc "]"：select json_quote（ '[1、2、 "abc"]'）;
cast（anyvalue as json）関数値をjsonタイプに変換して有効性を確保する：cast（ '{"a"：1、 "b"：2}' as json）;
• json_type（）関数を使用すると、JSON値のタイプを確認できます。オブジェクト、アレイ、スカラータイプ（整数、ブール値など）、nullまたはエラーを返します。たとえば、
JSON_VALID（）関数はJSONが有効な場合に戻ります。

無効なJSONドキュメントを挿入しようとすると、エラーがスローされ、レコード全体が挿入/更新されません。

-- 返回ARRAY：
SELECT JSON_TYPE('[1, 2, "abc"]');

-- 返回OBJECT：
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- 返回错误：
SELECT JSON_TYPE('{"a": 1, "b": 2');

mysql json列でJSONドキュメントを検索

-- 返回1：
SELECT JSON_TYPE('[1, 2, "abc"]');

-- 返回1：
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- 返回0：
SELECT JSON_TYPE('{"a": 1, "b": 2');

json_contains（）関数などのmysql json関数を使用すると、JSONドキュメントに特定の値が含まれているかどうかを確認できます。一致が見つかったときに1を返します。たとえば、

json_search（）関数JSONドキュメントの値へのパスを返します。一致していないときにnullを返します。 また、「1つ」と「すべて」のフラグと検索文字列（任意の数の文字と一致し、_のような1つの文字と一致する）を渡すことで、すべてのマッチを見つける必要があるか、1つのマッチを見つける必要があるかを指定することもできます。たとえば、

JSON_TABLE（）関数はJSONデータをリレーショナル形式に変換してクエリを容易にします：

-- 所有带有“JavaScript”标签的书籍：
SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

json path

-- 所有标签以“Java”开头的书籍：
SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

JSON_EXTRACT（）関数を使用した

MySQL JSONクエリは、指定されたパスに基づいてJSONドキュメントから特定の値を取得できます。

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

すべてのパス定義は$から始まり、その後に他のセレクターが続きます：

• $ .website
などの名前が続いています
• [n]、nはゼロインデックスアレイの位置
[*]ワイルドカード評価アレイすべてのメンバー
プレフィックス**接尾辞WildCardは、名前付きプレフィックスで始まり、名前付き接尾辞で終了するすべてのパスを評価します

次の例では、次のJSONドキュメントを参照してください。

パスの例：

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

$。返品1

• $。creturn [3、4]
• $。c[1]返品4
• $。d.e return 5
• $ **。e return [5]
JSONを使用してMySQL関数を抽出して、本のテーブルから名前と最初のタグを効果的に抽出できます。

より複雑な例については、JSON構成ファイルデータを含むユーザーテーブルがあるとします。たとえば、

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

JSONパスを使用して、Twitter名を抽出できます。たとえば、

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

where句のJSONパスを使用して、Twitterアカウントを持つユーザーのみを返すことができます：

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

JSONドキュメントの一部を変更します

PATH表記を使用してJSONドキュメントの一部を変更できるMYSQL関数がいくつかあります。これらの機能には次のものが含まれます

json_set（doc、path、val [、path、val]…）：ドキュメントにデータを挿入または更新します。 Book set tags = json_set（tags、 '$ [0]'、 'updated tag'）;

を更新します

json_insert（doc、path、val [、path、val]…）：既存の値を上書きせずにドキュメントにデータを挿入します。 Book set tags = json_insert（tags、 '$ [0]'、 'new Tag'）;
• を更新します
json_replace（doc、path、val [、path、val]…）：ドキュメント内のデータを置き換えます。 Book Set Tags = json_replace（tags、 '$ [0]'、 '' ched of tag '）;
• を更新します
json_merge_patch（doc、doc [、doc]…）：2つ以上のJSONドキュメントをマージし、既存のキーを後続のドキュメントの値に置き換えます。 book set tags = json_merge_patch（tags、 '["technical"]'）を更新するjson_search（tags、 'one'、 'javascript'）はnull;
json_array_append（doc、path、val [、path、val]…）：配列の最後に値を追加します。ブックセットタグ= json_array_append（tags、 '$'、 'new Tag'）;
• を更新します
json_array_insert（doc、path、val [、path、val]…）：jsonアレイの特定の場所に値を挿入します。 Book set tags = json_array_insert（tags、 '$ [0]'、 '挿入タグ'）;
• を更新します
json_remove（doc、path [、path]…）：ドキュメントからデータを削除します。ブックセットタグ= json_remove（tags、 '$ [1]'）;
• を更新します
json_pretty（val）：JSON文書を美しくして、読みやすくします。 select json_pretty（ '{"name"： "sitepoint"、 "tags"：["mysql"、 "json"]}'）;
たとえば、
• 既に「javascript」タグを持っている本に「テクニカル」タグを追加する場合は、json_merge_patch（）関数を使用できます：

詳細情報

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

MySQLドキュメントでは、MySQL JSONデータ型および関連するJSON関数に関する詳細情報を提供します。

もう一度思い出させてください、絶対に必要でない限り、JSONを使用しないでください。 MySQLのドキュメント指向NOSQLデータベース全体をシミュレートできますが、これによりSQLの多くの利点が相殺され、実際のNOSQLシステムに直接切り替えることもできます。

つまり、SQLアプリケーションのより曖昧なデータ要件の場合、JSONデータ型はいくらかの努力を節約する可能性があります。

mysql

でJSONデータの使用に関するよくある質問 mysqlでJSONを使用できますか？

MySQLは、JSONデータ型を提供することによりJSONをサポートします。これは、列にJSON形式のデータを保存するために使用されます。 MySQL 5.7.8から始めて、JSON列を使用してテーブルを作成して、SQLを使用してJSONデータを挿入、更新、およびクエリすることができます。 MySQLは、これらの列のJSONデータを処理するための一連のJSON関数を提供するため、JSONデータを抽出、変更、操作できます。 さらに、SQLクエリでJSONデータを使用して、必要に応じてjson_tableなどの関数を使用してリレーショナルデータに変換できます。ただし、MySQLは基本的に、本格的なNOSQL JSONデータベースになるのではなく、リレーショナルコンテキストでJSONデータの処理を促進するように設計されたJSONデータ型サポートを備えたリレーショナルデータベースであることを理解することが重要です。

上記のように、JSONを保存できるからといって、これを行う必要があるという意味ではありません。正規化はデータベース構造を最適化するための手法です。最初の通常のフォーミュラ（1NF）ルールは、各列が単一の値を保持する必要があることを示しています。そして、多値のJSONドキュメントを保存するとこのルールに違反します。

JSONをMySQLに保管しても大丈夫ですか？

mysqlにJSONを保存することは、以下を覚えておいてください。

半構造化または動的データ、厳格なパターンには適していません。

カスタムプロパティ。関係デザインが非効率的です。
• ペイロードまたはログを保存するためにJSON APIと統合されています。
• ただし、JSONは、構造化されたクエリデータの正規化されたリレーショナルストレージを置き換えるべきではありません。 MySQL 9.1は、関数インデックスやjson_tableなどの機能を備えたJSON機能を改善しますが、JSON操作は、大きなデータセットまたは複雑なクエリのオーバーヘッドを引き起こす可能性があります。
• MySQLクエリでJSONを使用する方法は？

MySQLのJSON関数を使用して、MySQLクエリでJSONを使用できます。これらの機能により、JSON列またはデータベースのJSON形式の文字列に保存されているJSONデータを抽出、操作、およびクエリすることができます。 JSON列にJSONデータにアクセスするには、＆gtを使用します。

json_extract、json_set、およびjson_objectaggおよびその他のJSON関数を使用すると、JSONデータをフィルタリング、変更、集約、および処理できます。 Where句を使用して、JSON値に基づいて行をフィルタリングすることもできます。 MySQLのJSON機能は、データベースクエリでJSONオブジェクトを直接相互作用および操作する柔軟な方法を提供します。

JSONをいつMySQLで使用する必要がありますか？

次の状況では、MySQLでJSONを使用する必要があります。

半構造化データ：カスタムプロパティなどの予測不可能なフィールドまたはスパースフィールドを処理するときにJSONを使用します。

ダイナミックモード：JSONは、データ要件が頻繁に変更されると柔軟性を提供します。

ハイドラフトまたはネストされたデータ：JSONは、親子関係または配列でデータをサポートしています。

1. API統合：ペイロード、応答、またはログをJSON文書として保存します。
2. ただし、
jsonは次の場合は使用しないでください
インデックスを必要とする頻繁に照会されたフィールド（関数インデックス作成は役立ちますが、通常は関係の設計が高速です）。
4. 標準化された厳密な関係データが必要です。
JSONパスの複雑なクエリは、パフォーマンスを低下させる可能性があります。

JSONデータをMySQLに保存する方法は？
• JSONデータをMySQLに保存するには、2つの主なオプションがあります。まず、MySQLで導入されたJSONデータ型を使用して、JSON列のあるテーブルを作成できます。この方法は、JSONデータの構造化されたストレージとより良いクエリパフォーマンスを提供します。

または、JSONデータを通常のVarcharまたはテキスト列にテキストとして保存できます。この方法は、複雑なデータベース操作なしでJSONデータを保存および取得するために主に必要な場合に適用されます。

MySQLでJSONデータをインデックス化する方法は？

JSON列を直接インデックス化することはできませんが、MySQLを使用すると、JSON値から派生した生成された列に関数インデックスを作成できます。

たとえば、

JSONアレイの最初の要素をインデックスするには：

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

この方法により、頻繁にアクセスされるJSONパスのクエリパフォーマンスが向上します。

JSONデータの場合、MySQLまたはNOSQLデータベースを使用する必要がありますか？

プロジェクトの要件に依存します：

• リレーショナルストレージが必要な場合は、リレーショナルモデルで半構造化データ、カスタムプロパティ、または階層データを処理することがある場合は、mysqlを選択します。
• プロジェクトには、主要なユースケースとして多くのJSONストレージ、柔軟なスキーマ、ドキュメントベースの操作が含まれる場合は、NOSQLデータベース（MongoDBなど）を選択します。

MySQLのJSONサポートは、ハイブリッドワークロードに最適ですが、ドキュメントストレージ専用のNOSQLデータベースの完全な代替品ではありません。

MySQL JSONフィールドから特定の値を抽出する方法は？

MySQL JSONフィールドから特定の値を抽出するには、json_extract（）関数または略語を使用します。

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

MySQL JSONフィールドでデータをクエリしてフィルタリングする方法は？

MySQL JSONフィールドに保存されているデータをクエリおよびフィルタリングするには、json_contains（）やjson_search（）などの関数を使用できます。 json_extract（）を使用して、さらにフィルタリングするために特定の値を取得することもできます。

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

以上がMySQLデータベースでJSONデータフィールドを使用する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。