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型は使えるようになれば便利ではあるなと思いました。