MySQL5.7 JSON型の使い方メモ

MySQL5.7から実装されたJSON型 使い方メモ

JSON型のテーブルを作成する

CREATE TABLE `blocks` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(120) NOT NULL,
  `content` varchar(200) NOT NULL,
  `meta` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

JSON型のデータを追加する

INSERT INTO `blocks` (`title`, `content`, `meta`)
VALUES (
  'qiita',
  'ruby',
  '{"ruby":2.3,"rails":5,}'
);

引数のデータを確認する

SELECT JSON_TYPE('["ruby", "rails", "jquery"]');
return ARRAY

オブジェクトに変換し返す

SELECT JSON_OBJECT('ruby', 2.3, 'rails', 5);
return {"ruby": 2.3, "rails": 5}

データの結合を行う

SELECT JSON_MERGE('["ruby", 2.3]', '{"rails": "5"}');
return ["ruby", 2.3, {"rails": "5"}]

値を検索する
$.key名で値を探し、値が存在しない場合にはNULLが返ってきます。

SELECT JSON_EXTRACT('{"ruby": 2.3, "rails": "5"}', '$.rails');
return "5"

SELECT JSON_EXTRACT('{"ruby": 2.3, "rails": "5"}', '$.php');
return NULL

WHERE句でJSON_EXTRACTを使うことでJSON型の中身で一致たした結果のレコード返す
LIKE文も使えるので便利
metaカラム全体にたいしてLIKE検索をかけることももちろんできる

SELECT * FROM blocks WHERE JSON_EXTRACT(meta, '$.ruby') = "2.3";

SELECT COUNT(id) FROM blocks WHERE JSON_EXTRACT(meta, '$.key') LIKE "%value%";

SELECT id, meta, created_at FROM blocks WHERE meta LIKE "%rai%" ORDER BY created_at DESC;

JSON型は使えるようになれば便利ではあるなと思いました。