LoginSignup
7
9

More than 3 years have passed since last update.

@1chitaro

【MySQL入門】MySQLを使ったJSON形式の保存とデータ取得について

Posted at

目的

・MySQLでJSON形式での保存など操作する際の備忘録
・主にテーブル作成、データ保存、更新、削除、置換の記述例を掲載

環境

・Ubuntu 19.04
・MySQL 5.7.27

作業

  1. テスト用テーブル作成
  2. INSERTを使ったデータ登録例
  3. UPDATEを使ったデータ更新例
  4. JSON_REMOVEを使ったJSONデータの操作
  5. JSON_REPLACEを使ったJSONデータの操作

内容

1. テスト用テーブル作成

  • 以降、各種データを保存する為のテスト用テーブル作成
  • カラムは３つ（id, title, tags）
  • tags をJSON形式で保存していく

▼テーブルイメージ（テーブル名：test_book）

カラム名 タイプ サイズ その他
id INT 11 NOT NULL / AUTO INCREMENT / PRIMARY KEY
title VARCHAR 200 NOT NULL
tags JSON NULL

▼テーブル生成例

> CREATE TABLE `test_book` (
  ->  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  ->  `title` VARCHAR(200) NOT NULL,
  ->  `tags` JSON DEFAULT NULL,
  ->  PRIMARY KEY (`id`)
  ->  ) ENGINE=InnoDB;

2. INSERTを使ったデータ登録例

  • INSERTのみを使ったJSON形式の保存方法
  • JSON_OBJECTを使った保存方法
  • JSON_ARRAYとJSON_OBJECTを使った保存方法

▼登録方法１

> INSERT INTO `test_book` ( `title`, `tags` ) VALUES (
  -> 'This is Test1',
  -> '[ "Javascript", "ES2015", "JSON" ]',
  -> );

結果：配列として保存される

tags
["Javascript", "ES2015", "JSON"]

▼登録方法２

> INSERT INTO `test_book` ( `title`, `tags` ) VALUES (
  -> 'This is Test2',
  -> JSON_OBJECT( 'name', 'MySQL', 'version', '5.7', 'os', 'Linux' )
  -> );

結果：連想配列として保存される

tags
{"os" : "Linux", "name" : "MySQL", "version" : "5.7"}

▼登録方法３

> INSERT INTO `test_book` ( `title`, `tags` ) VALUES (
  -> 'This is Test3',
  -> JSON_ARRAY (
  ->   JSON_OBJECT( 'name', '火鼻', 'price', 2000 ),
  ->   JSON_OBJECT( 'name', 'IQ60', 'price', 1000 )
  ->  )
  -> );

結果：配列の中に連想配列を２つ保存

tags
[{"name" : "火鼻", "price" : 2000 }, {"name" : "IQ60", "price" : 1000 }]

3. UPDATEを使ったデータ更新例

  • JSON_MERGEを使った更新方法
  • JSON_MERGEとJSON_OBJECTを使った更新方法
  • JSON_MERGEとJSON_ARRAYとJSON_OBJECTを使った更新方法

JSON_MERGEを使った更新方法

▼更新方法１※登録方法１レコード利用

> UPDATE `test_book` SET tags = JSON_MERGE( tags, '[ "technical"]' ) WHERE id = 1;

結果：配列の末尾に値が挿入され更新

tags（更新前）
["JavaScript", "ES2015", "JSON"]
tags（更新後）
["JavaScript", "ES2015", "JSON", "technical"]

▼更新方法２※登録方法２レコード利用

> UPDATE `test_book` SET tags = JSON_MERGE( tags, '[ "technical"]' ) WHERE id = 2;

結果：配列の末尾に値が挿入され更新

tags（更新前）
{"os": "Linux", "name": "MySQL", "version": "5.7"}
tags（更新後）
[{"os": "Linux", "name": "MySQL", "version": "5.7"}, "technical"]

▼更新方法３※登録方法３レコード利用

> UPDATE `test_book` SET tags = JSON_MERGE( tags, '["technical"]' ) WHERE id = 3;

結果：配列の末尾に値が挿入され更新

tags（更新前）
[{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}]
tags（更新後）
[{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}, "technical"]

所感

  • JSON_MERGEの操作は、配列の末尾に値を追加する結果となる

JSON_MERGEとJSON_OBJECTを使った更新方法

▼更新方法１※登録方法１レコード利用

> UPDATE `test_book` SET tags = JSON_MERGE( tags, JSON_OBJECT( "name", "初変" ) ) WHERE id = 1;

結果：配列の末尾に連想配列が挿入され更新

tags（更新前）
["JavaScript", "ES2015", "JSON"]
tags（更新後）
["JavaScript", "ES2015", "JSON", {"name": "初変"}]

▼更新方法２※登録方法２レコード利用

> UPDATE `test_book` SET tags = JSON_MERGE( tags, JSON_OBJECT( "name", "初変" ) ) WHERE id = 2;

結果：配列の末尾に連想配列が挿入され更新

tags（更新前）
[{"os": "Linux", "name": "MySQL", "version": "5.7"}]
tags（更新後）
[{"os": "Linux", "name": "MySQL", "version": "5.7"}, {"name": "初変"}]

▼更新方法３※登録方法３レコード利用

> UPDATE `test_book` SET tags = JSON_MERGE( tags, JSON_OBJECT( "name", "初変" ) ) WHERE id = 3;

結果：配列の末尾に連想配列が挿入され更新

tags（更新前）
[{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}]
tags（更新後）
[{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}, {"name": "初変"}"]

所感

  • JSON_MERGEとJSON_OBJECTの操作は、配列の末尾に連想配列を追加する結果となる

JSON_MERGEとJSON_ARRAYとJSON_OBJECTを使った更新方法

▼更新方法１※登録方法１レコード利用

> UPDATE `test_book` SET tags = JSON_MERGE( tags, JSON_ARRAY( JSON_OBJECT( "name", "初変" ) ) ) WHERE id = 1;

結果：配列の末尾に連想配列が挿入され更新

tags（更新前）
["JavaScript", "ES2015", "JSON"]
tags（更新後）
["JavaScript", "ES2015", "JSON", {"name": "初変"}]

▼更新方法２※登録方法２レコード利用

> UPDATE `test_book` SET tags = JSON_MERGE( tags, JSON_ARRAY( JSON_OBJECT( "name", "初変" ) ) ) WHERE id = 2;

結果：配列の末尾に連想配列が挿入され更新

tags（更新前）
[{"os": "Linux", "name": "MySQL", "version": "5.7"}]
tags（更新後）
[{"os": "Linux", "name": "MySQL", "version": "5.7"}, {"name": "初変"}]

▼更新方法３※登録方法３レコード利用

> UPDATE `test_book` SET tags = JSON_MERGE( tags, JSON_ARRAY( JSON_OBJECT( "name", "kameda" ) ) ) WHERE id = 3;

結果：配列の末尾に連想配列が挿入され更新

tags（更新前）
[{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}]
tags（更新後）
[{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}, {"name": "初変"}"]

所感

  • JSON_MERGEとJSON_OBJECTで検証した結果と出力される結果は変わらない
  • 文字数を考えれば、JSON_MERGEとJSON_OBJECTの組み合わせで十分と思われる

4. JSON_REMOVEを使ったJSONデータの操作

  • UPDATE操作時に作成したレコードを対象して検証する
  • DELETEではなくJSON_REMOVEを利用して検証する

▼REMOVE操作１※JSON_MERGEを使った更新方法１のレコードを利用

> UPDATE `test_book` SET tags = JSON_REMOVE( tags, '$[3]' ) WHERE id = 1;

結果：tagsカラムの配列[3]の値を削除 ( [0, 1, 2, 3] )

tags（更新前）
["JavaScript", "ES2015", "JSON", "technical"]
tags（更新後）
["JavaScript", "ES2015", "JSON"]

▼REMOVE操作２※JSON_MERGEを使った更新方法２のレコードを利用

> UPDATE `test_book` SET tags = JSON_REMOVE( tags, '$[1]' ) WHERE id = 2;

結果：tagsカラムの配列[1]の値を削除 ( [0, 1] )

tags（更新前）
[{"os": "Linux", "name": "MySQL", "version": "5.7"}, "technical"]
tags（更新後）
[{"os": "Linux", "name": "MySQL", "version": "5.7"}]

▼REMOVE操作３※JSON_MERGEを使った更新方法３のレコードを利用

> UPDATE `test_book` SET tags = JSON_REMOVE( tags, '$[2]' ) WHERE id = 3;

結果：tagsカラムの配列[2]の値を削除 ( [0, 1, 2] )

tags（更新前）
[{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}, "technical"]
tags（更新後）
[{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}]

▼REMOVE操作１※JSON_MERGEとJSON_OBJECTを使った更新方法１のレコードを利用

> UPDATE `test_book` SET tags = JSON_REMOVE( tags, '$[3].name' ) WHERE id = 1;

結果：tagsカラムの配列[3]の要素（name）の値を削除 ( [0, 1, 2, 3] )

tags（更新前）
["JavaScript", "ES2015", "JSON", {“name” : “初変”}]
tags（更新後）
["JavaScript", "ES2015", "JSON", {}]

▼REMOVE操作３※JSON_MERGEとJSON_OBJECTを使った更新方法３のレコードを利用

> UPDATE `test_book` SET tags = JSON_REMOVE( tags, '$[2].name' ) WHERE id = 3;

結果：tagsカラムの配列[2]の要素（name）の値を削除 ( [0, 1, 2] )

tags（更新前）
[{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}, {"name": "初変"}"]
tags（更新後）
[{"name": "火鼻", "price": 2000}, {"name": "IQ60", "price": 1000}, {}"]

5. JSON_REPLACEを使ったJSONデータの操作

  • JSON型カラムの値の任意の要素値を変更する
  • JSON_REPLACEを使用

▼REPLACE操作２※JSON_MERGEとJSON_OBJECTを使った更新方法２のレコードを利用

> UPDATE `test_book` SET tags = JSON_REPLACE( tags, '$[0].os', 'windows10', '$[0].version', '10.10' ) WHERE id = 2;

結果：tagsカラムの配列[0]の要素（os, version）の値を置換 ( [0, 1] )

tags（更新前）
[{"os": "Linux", "name": "MySQL", "version": "5.7"}, {"name": "初変"}]
tags（更新後）
[{"os": "windows10", "name": "MySQL", "version": "10.10"}, {"name": "初変"}]

References

『12.17.4 Functions That Modify JSON Values』
『MySQLでJSON型を使う（基本編）』
『JSON型の使い方（検索、抽出、挿入、更新、置換）』
『もう知ってた？MySQL5.7でNoSQLっぽくJSONデータを扱う方法』
『MySQLでJSON』

7
9
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
7
9