MySQL での配列の保存方法

1. MySQL で配列を格納するための関係としてのモック配列
2. 異なる SQL データ型の中に配列を格納する

このチュートリアルでは、MySQL でフィールドとして配列を格納したり、シミュレーションしたりする方法を紹介します。

SQL は独自の言語ではデータ型として明示的に配列をサポートしていませんが、リレーショナル・データベースであるため、これを可能にするための多くの回避策が用意されています。

SQL のようなリレーショナルデータベースは、リレーションとキーを使って動作します。ほとんどの場合、複数の値を関連する 1つの行に接続するために、これらのリレーションシップを利用します。

リレーションは配列の非連結版であり、SQL の性質と設計とうまく機能します（正規化を参照してください）。SQL に配列データ型が存在しないのは、リレーションが存在するために配列が必要ないことが多いからです。

データベース内の配列は、使い方や操作を誤ると危険な場合があります。配列を使用せずにデータベースを実装することができ、非常に最適化されたデータベースを手に入れることができます。

本当に配列を格納したい、または格納する必要がある場合は、以下にその方法をいくつか紹介します。

MySQL で配列を格納するための関係としてのモック配列

SQL の性質と慣習に従うのであれば、配列は関係として扱われるべきです。

例えば、レストランで複数の order を持つ customer がいるとしましょう。

まず、customer と order のテーブルを作成します。

CREATE TABLE customer (
`id` INT NOT NULL PRIMARY KEY,
`name` VARCHAR(50)
);

CREATE TABLE order (
`order_id` INT NOT NULL PRIMARY KEY,
`order` VARCHAR(40),
`price` DOUBLE
);

customer とその order は一対多の関係を持っています。これを実装するためには、2つのテーブルを接続してお互いに関連付けるための連想エンティティが必要です。

CREATE TABLE customer_order (
`customer_id` INT NOT NULL,
`order_id` INT NOT NULL,
PRIMARY KEY(`customer_id`、`order_id`)
);

ここでは、テーブルがこのように配置されていることを想像してみましょう。

• customer

• order

• customer_order

これらのテーブルから、John Doe がホタテ、ロブスター、チーズケーキを注文していることがわかります。一方、Jane Doe はステーキとチーズケーキを注文しました。

John Doe が注文したものをすべて取得したい場合は、単に SELECT JOIN クエリを使用します。

SELECT c.name, o.order
FROM customer c
INNER JOIN customer_order co
ON co.customer_id = c.customer_id
INNER JOIN order o
ON o.order_id = co.order_id
WHERE name = 'John Doe'

このクエリはこの結果を生成します。

基本的に、リレーションは配列の SQL 版です。ですから、良い設計を維持したいのであれば、リレーションの概念を理解し、可能な限り使用してください。

異なる SQL データ型の中に配列を格納する

MySQL 5.7 以降のアップデートでは、データ型として JSON をサポートするようになりました。JSON は、配列、セット、マップ、辞書などの複雑なデータ型を格納するのに便利な方法を SQL に提供します。

SQL 内で配列をモックする最速の方法は、配列を文字列として格納することです。

テーブル customer と order を作成します。

CREATE TABLE customer (
`id` INT NOT NULL PRIMARY KEY,
`name` VARCHAR(50),
`order` VARCHAR(999)
);

CREATE TABLE order (
`id` INT NOT NULL PRIMARY KEY,
`order` VARCHAR(50),
`price` DOUBLE
);

顧客が注文を行うたびに、GROUP_CONCAT() を使用して顧客の order フィールドに新しいエントリを格納します。

UPDATE customer
SET order = CONCAT(order, ', Cheesecake');

UPDATE customer
SET order = CONCAT(order, ', Ravioli');

顧客に注文を問い合わせると、その結果が表示されます。

このメソッドは基本的に配列型を単一のカラムに格納したものです。しかし、これはリレーショナルデータベースの慣習に従っていないことを意味します。

これは、SQL 内で配列を格納するための効果的な方法です。それでも、配列を新しいテーブルに簡単に格納でき、リレーションや制約を設定できるのに、リレーショナルデータベースに配列を強制的に格納するのは、悪い設計と考えられます。