MySQL 5.7 における基本的な JSON 操作ガイド

序文

プロジェクトのニーズにより、ストレージフィールドは JSON 形式で保存されます。プロジェクトでは、クエリされた値は Jackson を介して対応する Bean に変換されて処理されますが、これは十分にシンプルで便利ではありません。

MySQL はバージョン 5.7 以降で JSON 形式のデータをサポートしており、操作が非常に便利になっています。

テーブルを作成する

新しいテーブルを作成するときに、フィールドタイプを json タイプに直接設定できます。たとえば、テーブルを作成します。

mysql> テーブル `test_user`(`id` INT PRIMARY KEY AUTO_INCREMENT、`name` VARCHAR(50) NOT NULL、`info` JSON) を作成します。

JSON型フィールドはNULLにできる

データを挿入:

mysql> INSERT INTO test_user(`name`, `info`) VALUES('xiaoming','{"sex": 1, "age": 18, "nick_name": "小萌"}');

json 型のフィールドは有効な json 文字列である必要があります

JSON_OBJECT()関数を使用して json オブジェクトを構築できます。

mysql> test_user(`name`, `info`) に VALUES('xiaohua', JSON_OBJECT("sex", 0, "age", 17) を挿入します。

JSON_ARRAY()関数を使用して JSON 配列を構築します。

mysql> test_user(`name`, `info`) に INSERT INTO VALUES('xiaozhang', JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3,5,90)));

次に、test_user テーブルのデータを表示します。

mysql> test_user から * を選択します。 
+----+-----------+--------------------------------------------+ 
| ID | 名前 | 情報 |
+----+-----------+--------------------------------------------+ 
| 1 | xiaoming | {"age": 18, "sex": 1, "nick_name": "小萌"} | 
| 2 | xiaohua | {"年齢": 17, "性別": 0} |
| 3 | xiaozhang | {"年齢": 19, "性別": 1, "タグ": [3, 5, 90]} | 
+----+-----------+--------------------------------------------+
3 行セット (0.04 秒)

クエリ

式: オブジェクトは json 列 -> '$.key'、配列は json 列 -> '$.key[index]'

mysql> test_user から name、info->'$.nick_name'、info->'$.sex'、info->'$.tag[0]' を選択します。 
+-----------+---------------------+---------------+------------------+ 
| 名前 | 情報->'$.nick_name' | 情報->'$.sex' | 情報->'$.tag[0]' | 
+-----------+---------------------+---------------+------------------+ 
| xiaoming | 「シャオミン」 | 1 | NULL | 
| シャオホア | NULL | 0 | NULL | 
| xiaozhang | NULL | 1 | 3 | 
+-----------+---------------------+---------------+------------------+ 
3 行セット (0.04 秒)

同等: オブジェクトの場合はJSON_EXTRACT(json列, '$.鍵') JSON_EXTRACT(json列, '$.鍵[index]')

mysql> test_user から name、JSON_EXTRACT(info、'$.nick_name')、JSON_EXTRACT(info、'$.sex')、JSON_EXTRACT(info、'$.tag[0]') を選択します。
 +-----------+----------------------------------+-----------------------------+--------------------------------+ 
| 名前 | JSON_EXTRACT(情報、'$.ニックネーム') | JSON_EXTRACT(情報、'$.性別') | JSON_EXTRACT(情報、'$.タグ[0]') 
|+-----------+-----------------------------------+-----------------------------+--------------------------------+ 
| xiaoming | 「シャオミン」 | 1 | NULL |
| シャオホア | NULL | 0 | NULL | 
| xiaozhang | NULL | 1 | 3 | 
+-----------+----------------------------------+-----------------------------+--------------------------------+ 
3 行セット (0.04 秒)

ただし、「小萌」は二重引用符で囲まれていますが、これは望ましくありません。JSON_UNQUOTE 関数を使用して二重引用符を削除できます。

mysql> test_user から name、JSON_UNQUOTE(info->'$.nick_name') を選択します。name='xiaoming' です。 
+----------+-----------------------------------+ 
| 名前 | JSON_UNQUOTE(info->'$.nick_name') | 
+----------+-----------------------------------+ 
| シャオミン | シャオミン| 
+----------+-----------------------------------+ 
セット内の1行（0.05秒）

演算子->>を直接使用することもできます

mysql> name、info->>'$.nick_name' を test_user から選択します。name='xiaoming';
+----------+----------------------+ 
| 名前 | 情報->>'$.nick_name' | 
+----------+----------------------+ 
| シャオミン | シャオミン| 
+----------+----------------------+ 
セット内の1行（0.06秒）

もちろん、属性はクエリ条件としても使用できます。

mysql> test_user から name, info->>'$.nick_name' を選択します。ここで、info->'$.nick_name'='Xiao Meng'; 
+----------+----------------------+ 
| 名前 | 情報->>'$.nick_name' | 
+----------+----------------------+ 
| シャオミン | シャオミン| 
+----------+----------------------+ 
セット内の1行（0.05秒）

仮想列を使用すると、JSON 型の指定された属性をすばやくクエリできることは注目に値します。

仮想列を作成します。

mysql> ALTER TABLE `test_user` ADD `nick_name` VARCHAR(50) GENERATED ALWAYS AS (info->>'$.nick_name') VIRTUAL;

演算子 ->> の使用に注意してください。

使用方法は通常の列クエリと同じです。

mysql> select name,nick_name from test_user where nick_name='小萌'; 
+----------+------------+ 
| 名前 | ニックネーム | 
+----------+------------+ 
| シャオミン | シャオミン| 
+----------+------------+ 
セット内の1行（0.05秒）

更新する

既存の値を上書きせずに新しい値を挿入するにはJSON_INSERT()を使用します

mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 1, '$.nick_name', '小花') where id=2;

結果を見てください

mysql> id=2 の test_user から * を選択します。 
+----+---------+------------------------------------------+-----------+ 
| ID | 名前 | 情報 | ニックネーム | 
+----+---------+------------------------------------------+-----------+ 
| 2 | xiaohua | {"age": 17, "sex": 0, "nick_name": "小花"} | xiaohua| 
+----+---------+------------------------------------------+-----------+ 
セット内の1行（0.06秒）

JSON_SET()を使用して新しい値を挿入し、既存の値を上書きします

mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 0, '$.nick_name', '小张') where id=3;

結果を見てください

mysql> test_user から * を選択します (id=3); 
+----+-----------+--------------------------------------------------------------------------+ 
| ID | 名前 | 情報 | ニックネーム | 
+----+-----------+--------------------------------------------------------------------------+ 
| 3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90], "nick_name": "小张"} | xiaozhang| 
+----+-----------+--------------------------------------------------------------------------+ 
セット内の1行（0.06秒）

既存の値のみを置き換えるにはJSON_REPLACE()を使用します

mysql> UPDATE test_user SET info = JSON_REPLACE(info, '$.sex', 1, '$.tag', '[1,2,3]') ここで id=2;

結果を見てください

mysql> id=2 の test_user から * を選択します。 
+----+---------+------------------------------------------+-----------+ 
| ID | 名前 | 情報 | ニックネーム | 
+----+---------+------------------------------------------+-----------+ 
| 2 | xiaohua | {"age": 17, "sex": 1, "nick_name": "小花"} | xiaohua| 
+----+---------+------------------------------------------+-----------+ 
セット内の1行（0.06秒）

タグが更新されていないことがわかります。

消去

JSON_REMOVE()を使用して JSON 要素を削除する

mysql> UPDATE test_user SET info = JSON_REMOVE(info, '$.sex', '$.tag') where id=1;

結果を見てください

mysql> id=1 の test_user から * を選択します。 
+----+----------+----------------------------------+-----------+ 
| ID | 名前 | 情報 | ニックネーム | 
+----+----------+----------------------------------+-----------+ 
| 1 | xiaoming | {"age": 18, "nick_name": "Xiao Meng"} | Xiao Meng | 
+----+----------+----------------------------------+-----------+ 
セット内の1行（0.05秒）

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。

以下もご興味があるかもしれません: