注文ごとに買った商品を「りんご,みかん,ぶどう」のように1行で見たいときは、MySQL の集約関数 GROUP_CONCAT を使います。GROUP BY でまとめた行の値を、区切り文字でつないで1つの文字列にする関数です。区切り文字を変える SEPARATOR、並び順を決める ORDER BY、重複を消す DISTINCT の書き方を順に説明します。後半では、NULL の行が結果から抜ける動きと、結果が1024バイトで切れる落とし穴、その直し方も扱います。
以下の結果は MySQL 8.0 で実際に実行したものです。
注文明細のテーブルで考える
ネットショップの注文明細 order_items を例にします。1行が注文の中の商品1つで、同じ order_id の行が1回分の注文です。注文1002と1003には、商品名が登録されていない行（product が NULL）をわざと入れています。
CREATE TABLE order_items (
id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
customer VARCHAR(20) NOT NULL,
product VARCHAR(30),
price INT NOT NULL
);
INSERT INTO order_items (order_id, customer, product, price) VALUES
(1001, '佐藤', 'りんご', 300),
(1001, '佐藤', 'みかん', 200),
(1001, '佐藤', 'ぶどう', 500),
(1002, '鈴木', 'みかん', 200),
(1002, '鈴木', NULL, 150),
(1003, '田中', NULL, 400),
(1004, '佐藤', 'りんご', 300),
(1004, '佐藤', 'バナナ', 180);佐藤さんは注文1001と1004の2回とも、りんごを買っています。この重なりは DISTINCT の説明で使います。
GROUP_CONCATの基本と区切り文字（SEPARATOR）
GROUP_CONCAT は GROUP BY と組み合わせて使います。グループの中の値を、何も指定しなければカンマ（,）でつないで返します。
SELECT order_id, GROUP_CONCAT(product) AS products
FROM order_items
GROUP BY order_id;+----------+-------------------------------+
| order_id | products |
+----------+-------------------------------+
| 1001 | りんご,みかん,ぶどう |
| 1002 | みかん |
| 1003 | NULL |
| 1004 | りんご,バナナ |
+----------+-------------------------------+4つの注文が4行になり、商品名が1つの文字列にまとまりました。注文1003が NULL になる理由は、あとの落とし穴の節で説明します。GROUP BY でグループを作るしくみは「GROUP BYとHAVINGの違いとWHERE句との使い分け」で説明しています。
区切り文字は SEPARATOR で変えます。並び順は、関数のかっこの中に ORDER BY を書いて決めます。次の例は値段の高い順に並べ、「 / 」で区切ります。
SELECT order_id,
GROUP_CONCAT(product ORDER BY price DESC SEPARATOR ' / ') AS products
FROM order_items
GROUP BY order_id;+----------+-----------------------------------+
| order_id | products |
+----------+-----------------------------------+
| 1001 | ぶどう / りんご / みかん |
| 1002 | みかん |
| 1003 | NULL |
| 1004 | りんご / バナナ |
+----------+-----------------------------------+ORDER BY を書かないと、つなぐ順番は決まりません。最初の例はたまたま id の順に並びましたが、データの量やインデックスしだいで変わります。画面や CSV に出す一覧には、いつも ORDER BY を書いておくと安心です。
かっこの中に書けるものを表にまとめます。書く順番は「DISTINCT 式 ORDER BY 〜 SEPARATOR 〜」で固定です。
| 書き方 | はたらき | 例 |
|---|---|---|
| DISTINCT | 同じ値を1回だけにする | DISTINCT product |
| ORDER BY 列 [DESC] | つなぐ順番を決める | ORDER BY price DESC |
| SEPARATOR ‘文字’ | 区切り文字を変える。” なら区切りなし | SEPARATOR ‘ / ‘ |
DISTINCTで重複を消す・複数の列をつなぐ
同じ客が別の注文で同じ商品を買っていると、そのままつなぐと名前が2回出ます。DISTINCT を付けると、同じ値は1回だけになります。
SELECT customer,
GROUP_CONCAT(product ORDER BY id) AS all_products,
GROUP_CONCAT(DISTINCT product ORDER BY product) AS uniq_products
FROM order_items
GROUP BY customer;+----------+---------------------------------------------------+-----------------------------------------+
| customer | all_products | uniq_products |
+----------+---------------------------------------------------+-----------------------------------------+
| 佐藤 | りんご,みかん,ぶどう,りんご,バナナ | バナナ,ぶどう,みかん,りんご |
| 田中 | NULL | NULL |
| 鈴木 | みかん | みかん |
+----------+---------------------------------------------------+-----------------------------------------+all_products ではりんごが2回出ていますが、uniq_products では1回です。DISTINCT と ORDER BY は一緒に使えます。並び順は列の照合順序に従うので、カタカナのバナナも「ば」の位置に入り、五十音順になりました。
引数には式をいくつも書けます。カンマで並べた式は、区切り文字なしでつながって1つの要素になります。商品名と値段をセットで見せたいときに使えます。
SELECT order_id,
GROUP_CONCAT(product, '(', price, '円)' ORDER BY id SEPARATOR '、') AS detail
FROM order_items
GROUP BY order_id;+----------+-----------------------------------------------------------+
| order_id | detail |
+----------+-----------------------------------------------------------+
| 1001 | りんご(300円)、みかん(200円)、ぶどう(500円) |
| 1002 | みかん(200円) |
| 1003 | NULL |
| 1004 | りんご(300円)、バナナ(180円) |
+----------+-----------------------------------------------------------+注文1002には150円の行もありますが、結果には出ていません。式のうち1つでも NULL だと、その行はまるごと抜けます。CONCAT 関数が NULL を1つ含むと NULL を返すのと同じ動きです。
よくある落とし穴
NULLは抜け、全部NULLなら結果もNULL
GROUP_CONCAT は NULL を飛ばし、残りの値だけをつなぎます。グループの値がすべて NULL のときは、空の文字列ではなく NULL を返します。抜けた行に気づくには、件数と見比べるのが確実です。
SELECT order_id,
COALESCE(GROUP_CONCAT(product ORDER BY id), '(商品名なし)') AS products,
COUNT(*) AS items,
COUNT(product) AS named_items
FROM order_items
GROUP BY order_id;+----------+-------------------------------+-------+-------------+
| order_id | products | items | named_items |
+----------+-------------------------------+-------+-------------+
| 1001 | りんご,みかん,ぶどう | 3 | 3 |
| 1002 | みかん | 2 | 1 |
| 1003 | (商品名なし) | 1 | 0 |
| 1004 | りんご,バナナ | 2 | 2 |
+----------+-------------------------------+-------+-------------+注文1002は2行あるのに、products には1つしかありません。NULL の行も「不明」として並べたいときは、関数の中で COALESCE を使って置きかえます。
SELECT order_id,
GROUP_CONCAT(COALESCE(product, '不明') ORDER BY id) AS products
FROM order_items
GROUP BY order_id;+----------+-------------------------------+
| order_id | products |
+----------+-------------------------------+
| 1001 | りんご,みかん,ぶどう |
| 1002 | みかん,不明 |
| 1003 | 不明 |
| 1004 | りんご,バナナ |
+----------+-------------------------------+1024バイトを超えると途中で切れる
結果の長さの上限は、システム変数 group_concat_max_len で決まります。初めの値は1024バイトで、超えた分は切り捨てられます。エラーにはならず警告が出るだけなので、途中で切れた一覧をそのまま使ってしまいがちです。
切れるようすを見るため、上限を20バイトにして試します。
SET SESSION group_concat_max_len = 20;
SELECT customer, GROUP_CONCAT(product ORDER BY id) AS products
FROM order_items
WHERE customer = '佐藤'
GROUP BY customer;
SHOW WARNINGS;+----------+----------------------+
| customer | products |
+----------+----------------------+
| 佐藤 | りんご,みかん, |
+----------+----------------------+
+---------+------+---------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------+
| Warning | 1260 | Row 3 was cut by GROUP_CONCAT() |
+---------+------+---------------------------------+5つあるはずの商品が「りんご,みかん,」で止まりました。上限は文字数ではなくバイト数で数えます。utf8mb4 の日本語は1文字3バイトなので、1024バイトに入るのは日本語で340文字ほどです。
直すときは、セッションの上限を必要な長さまで上げます。次の例では65535バイトにしてから、切れずに出た結果の長さを確かめています。
SET SESSION group_concat_max_len = 65535;
SELECT customer,
LENGTH(GROUP_CONCAT(product ORDER BY id)) AS bytes,
CHAR_LENGTH(GROUP_CONCAT(product ORDER BY id)) AS chars
FROM order_items
WHERE customer = '佐藤'
GROUP BY customer;
SELECT @@SESSION.group_concat_max_len, @@GLOBAL.group_concat_max_len;+----------+-------+-------+
| customer | bytes | chars |
+----------+-------+-------+
| 佐藤 | 49 | 19 |
+----------+-------+-------+
+--------------------------------+-------------------------------+
| @@SESSION.group_concat_max_len | @@GLOBAL.group_concat_max_len |
+--------------------------------+-------------------------------+
| 65535 | 1024 |
+--------------------------------+-------------------------------+19文字で49バイトでした。SET SESSION で変えた値はその接続の中だけで効き、サーバー全体（GLOBAL）は1024のままです。いつも大きくしておきたいなら、設定ファイル（my.cnf）に group_concat_max_len を書きます。公式リファレンスでは、上限を必要以上に大きくすると性能が落ちたりメモリが足りなくなったりするとして、要る長さを見積もってから決めるよう書かれています。実際の上限は max_allowed_packet にも縛られます。
ウィンドウ関数としては使えない
SUM や COUNT と違い、GROUP_CONCAT には OVER 句を付けられません。MySQL 8.0 で試すと、次のエラーになります。
SELECT customer, GROUP_CONCAT(product ORDER BY id) OVER (PARTITION BY customer) AS x
FROM order_items;ERROR 1235 (42000): This version of MySQL doesn't yet support 'group_concat as window function'明細の各行に一覧を付けたいときは、GROUP BY で作った結果をサブクエリにして、もとのテーブルと JOIN します。
JSON_ARRAYAGGとPostgreSQLのSTRING_AGG
アプリで受け取ってから1つずつ分けるなら、JSON の配列で返す JSON_ARRAYAGG も選べます。商品名にカンマが入っていても区切りと混ざりません。
SELECT order_id, JSON_ARRAYAGG(product) AS products
FROM order_items
GROUP BY order_id;+----------+-----------------------------------------+
| order_id | products |
+----------+-----------------------------------------+
| 1001 | ["りんご", "みかん", "ぶどう"] |
| 1002 | ["みかん", null] |
| 1003 | [null] |
| 1004 | ["りんご", "バナナ"] |
+----------+-----------------------------------------+GROUP_CONCAT と違って、NULL も null として配列に残ります。JSON の値の取り出し方は「MySQLのJSON型入門：JSON_EXTRACTとパス記法」で説明しています。
PostgreSQL には GROUP_CONCAT がなく、STRING_AGG(product, ‘,’ ORDER BY id) のように書きます。区切り文字は省略できません。
| 関数 | 使えるDB | 返す形 | NULLの行 | 並び順の指定 |
|---|---|---|---|---|
| GROUP_CONCAT | MySQL | 区切り文字でつないだ文字列 | 飛ばす | できる |
| JSON_ARRAYAGG | MySQL 8.0 | JSON の配列 | null で残す | できない |
| STRING_AGG | PostgreSQL | 区切り文字でつないだ文字列 | 飛ばす | できる |
まとめ
GROUP_CONCAT は、GROUP BY でまとめた行の値を1つの文字列にします。並び順は関数の中の ORDER BY、区切り文字は SEPARATOR で決め、重複は DISTINCT で消せます。NULL の行は黙って飛ばされ、全部 NULL なら結果も NULL です。結果は初めの設定だと1024バイトまでで、日本語なら340文字ほどで切れます。長い一覧を作る前に group_concat_max_len を上げておけば、途中で切れた結果を使わずに済みます。
もっと学ぶなら【PR】
- MySQL徹底入門 第5版：MySQL の機能を広く解説した本。日付関数などの細かい動きを調べたいときに
- スッキリわかるSQL入門 第4版：練習問題がたくさんある入門書。手を動かして基本を固めたい人向け
※Amazonのアソシエイトとして、当サイトは適格販売により収入を得ています。
参考リンク
- MySQL 8.0 Reference Manual: Aggregate Function Descriptions（GROUP_CONCAT）
- MySQL 8.0 Reference Manual: group_concat_max_len
- PostgreSQL Documentation: Aggregate Functions（string_agg）
アイキャッチ画像: Photo by Dave Hoefler on Unsplash
