Posted inMySQL

MySQL JSON データ型：スキーマ変更不要で柔軟なデータ保存

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

この話は数年前に携わったEコマースプロジェクトから始まります。システムでは商品ごとに「属性」を保存する必要がありました：シャツにはサイズ・色・素材、靴にはサイズ番号・色・ソールの種類。商品カテゴリによって属性が完全に異なります。

当時の解決策はEAV（Entity-Attribute-Value）パターンでした。product_attributesテーブルにentity_id、attribute_name、valueの3カラムを用意しました。動きはしましたが、クエリのたびに5〜6回のJOINが必要で、EXPLAINの実行計画を見るたびに目を覆いたくなっていました。MySQL 5.7.8からネイティブのJSON データ型が登場し、もっと早く知っておけばよかったと感じた機能です。

JSON データ型とは？なぜTEXTではダメなのか

誰もが最初に聞く質問：「TEXT で保存してアプリケーション層でパースすれば同じじゃないの？」——確かにできます。でも TEXT と JSON は別物です。MySQL の JSON 型には TEXT にはできない3つの特徴があります：

  • 自動バリデーション：有効なJSON形式でない場合、MySQLはINSERTを拒否します。TEXTは黙って不正なデータをデータベースに受け入れてしまいます。
  • バイナリストレージ：データは最適化されたバイナリ形式で保存され、サブフィールドへのアクセスがTEXTのような文字列全体のパースより高速です。
  • ネイティブ関数：JSON_EXTRACT、JSON_SET、JSON_CONTAINSなどをSQL内で直接使用できます。処理をアプリケーション層に押し上げる必要がありません。

最大サイズはmax_allowed_packetによって制限されており、デフォルトは16MBです。実際のプロジェクトではこの上限に達することはほとんどありません。

テーブル作成とデータの挿入

attributesカラムをJSON型で持つproductsテーブルを作成します：

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2),
    attributes JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT の構文は通常の文字列を保存する場合と変わりません——有効なJSON形式であることを確認するだけです：

INSERT INTO products (name, price, attributes) VALUES
(
    'ベーシックTシャツ',
    299000,
    '{"size": ["S", "M", "L", "XL"], "color": "ホワイト", "material": "cotton", "in_stock": true}'
),
(
    'ランニングシューズ Pro',
    1850000,
    '{"size": [39, 40, 41, 42, 43], "color": "ブラック", "waterproof": false, "weight_g": 320}'
);

不正なJSON構文でINSERTを試してみましょう——アプリケーション層を待たず、MySQLが即座に拒否します：

-- この行はrejectされます：
INSERT INTO products (name, attributes) VALUES ('Test', '{invalid json}');
-- ERROR 3140: Invalid JSON text at position 1...

実践：JSONデータのクエリ

-> と ->> でサブフィールドを読み取る

この2つの演算子は毎日最もよく使うものです：

-- -> はJSONのvalue（ダブルクォート付き文字列）を返します
SELECT name, attributes->'$.color' AS color FROM products;
-- 結果: "ホワイト"

-- ->> はプレーンテキスト（クォートなし文字列）を返します
SELECT name, attributes->>'$.color' AS color FROM products;
-- 結果: ホワイト

-- 配列要素へのアクセス（インデックスは0始まり）
SELECT name, attributes->>'$.size[0]' AS smallest_size FROM products;

JSON条件でのフィルタリング

-- cotton素材の商品を検索
SELECT id, name, price
FROM products
WHERE attributes->>'$.material' = 'cotton';

-- JSON_CONTAINS：配列に値が含まれているか確認
SELECT id, name
FROM products
WHERE JSON_CONTAINS(attributes->'$.size', '"M"');

-- 複数条件の組み合わせ
SELECT id, name, price
FROM products
WHERE JSON_EXTRACT(attributes, '$.in_stock') = true
  AND price < 500000;

JSON内の値の更新

-- JSON_SET：フィールドの追加または更新
UPDATE products
SET attributes = JSON_SET(attributes, '$.discount_pct', 15)
WHERE id = 1;

-- JSON_REMOVE：フィールドの削除
UPDATE products
SET attributes = JSON_REMOVE(attributes, '$.discount_pct')
WHERE id = 1;

-- JSON_MERGE_PATCH：オブジェクトのマージ（重複キーは上書き）
UPDATE products
SET attributes = JSON_MERGE_PATCH(attributes, '{"new_arrival": true, "season": "summer"}')
WHERE id = 2;

パフォーマンスとインデックス——最も間違いやすいポイント

MySQLはJSONカラムに直接インデックスを作成できません。これは典型的な落とし穴です——数十万行のテーブルでJSONフィールドによるクエリを実行すると、コードを見ても問題がわからないのに実行時間が2〜3秒に跳ね上がります。EXPLAINを実行して初めて「Full table scan」と表示されて気づくのです。

最もシンプルな回避策はGenerated Columnを使う方法です：JSONフィールドから自動生成される仮想カラムを作成し、そのカラムにインデックスを作成します：

-- JSONフィールドからvirtual generated columnを追加
ALTER TABLE products
ADD COLUMN material VARCHAR(50)
    GENERATED ALWAYS AS (attributes->>'$.material') VIRTUAL;

-- generated columnにインデックスを作成
CREATE INDEX idx_material ON products (material);

-- これ以降はfull scanの代わりにインデックスを使用
SELECT id, name FROM products WHERE material = 'cotton';
-- EXPLAINで "Full table scan" の代わりに "Using index" と表示される

VIRTUALの代わりにSTOREDを使うと値がディスクに物理的に保存されます——読み取り速度はわずかに向上しますが、その分ストレージを消費します。フィルタリングによく使うbooleanフィールドの場合：

ALTER TABLE products
ADD COLUMN in_stock BOOLEAN
    GENERATED ALWAYS AS (JSON_EXTRACT(attributes, '$.in_stock')) STORED,
ADD INDEX idx_in_stock (in_stock);

JSON_TABLE——JSON配列を複数行に展開する（MySQL 8.0+）

JSONの配列をテーブル形式で処理するために複数行に「展開」したい場合があります。JSON_TABLEはこれをSQL内で直接実現します——データをアプリケーション層に引き上げてループ処理する必要がありません：

-- 各商品のサイズを個別の行に展開
SELECT
    p.id,
    p.name,
    s.size_value
FROM products p,
JSON_TABLE(
    p.attributes,
    '$.size[*]'
    COLUMNS (size_value VARCHAR(10) PATH '$')
) AS s
WHERE p.id = 1;

結果：

+----+--------------------+------------+
| id | name               | size_value |
+----+--------------------+------------+
|  1 | ベーシックTシャツ   | S          |
|  1 | ベーシックTシャツ   | M          |
|  1 | ベーシックTシャツ   | L          |
|  1 | ベーシックTシャツ   | XL         |
+----+--------------------+------------+

使うべき場面、使うべきでない場面

実際に何度か実装した経験から、かなり明確な線引きができました。JSONカラムが適しているケース：

  • スキーマが柔軟で頻繁に変わる場合：商品属性、メタデータ、ユーザーごとの設定、フィーチャーフラグ
  • オブジェクト全体として読み取ることが多いデータ：サブフィールドでのフィルタリングが少ない場合
  • 素早いプロトタイピング：スキーマが確定していない段階で、毎回マイグレーションなしにイテレーションしたい場合

使うべきでないケース：

  • JSONの複数フィールドによる複雑なクエリが頻繁に必要な場合——別テーブルに正規化した方がSQLが明確になります
  • 外部キー制約が必要なデータ——JSONカラムはサポートしていません
  • チームがMySQL JSON関数に慣れていない場合——インデックスを使わないクエリを知らずに書いてしまいがちです

まとめ

MySQL JSON型は万能薬ではありませんが、適切なユースケースではスキーマの柔軟性という問題をシンプルに解決してくれます——複雑なEAVパターンも追加の拡張機能も不要です。よく使うパターン：頻繁にフィルタリングやソートする重要なフィールドは個別のカラムに正規化してインデックスを作成し、補助的なメタデータはJSONカラムに格納するという方法です。

MySQL 5.7+と8.0ではJSON型が標準搭載されており、追加インストールは不要です。ターミナルを開いてテスト用テーブルを作成し、上記の例を実行してみてください——アプリケーション層でJSONをパースしてフィールドの有無を確認するやり方とは全く違う感覚が得られるはずです。

Share:
Tags: