【MySQL】GROUP_CONCAT()する際のデータ型は文字列型（CHRAなど）に変換する必要あり

2018/03/15 2019/03/01

MySQLのGROUP_CONCAT句で、複数のレコードを１レコードとして取得する際、レコードのID（int型）を半角カンマで区切って取得しようとしたところ、（Windowsでは）16進数かなにか、よくわからない数値が返ってきました。

これは何なんだと30分ほど悩みましたが、よくよく調べたところ、GROUP_CONCATして返す際のデータは、文字列型である必要があるようでしたので、備忘録として書き記しておきたいと思います。

GROUP_CONCAT()で取得する際のデータはCAST()で文字列型に変換する

以下のユーザマスタ / 行き先マスタ / 出張履歴テーブルのレコードを利用します。

出張履歴を分かりやすい形で表示すると、以下の様になります。

SELECT
    t.id,
    u.name,
    p.place,
    t.date
FROM
    trips AS t
    LEFT JOIN users AS u ON (u.id = t.user_id)
    LEFT JOIN places AS p ON (p.id = t.place_id)
ORDER BY t.date ASC;

SELECT

t.id,

u.name,

p.place,

t.date

FROM

trips AS t

LEFT JOIN users AS u ON (u.id = t.user_id)

LEFT JOIN places AS p ON (p.id = t.place_id)

ORDER BY t.date ASC;

結果

GROUP_CONCAT()で、ユーザ別の出張先を取得

表題の通り、GROUP_CONCAT()で、ユーザ別の出張先をカンマ区切りのレコードとして取得してみます。
これは割りと簡単に出来ます。

SELECT
    u.name,
    GROUP_CONCAT(p.place) AS '出張先'
FROM
    trips AS t
    LEFT JOIN users AS u ON (u.id = t.user_id)
    LEFT JOIN places AS p ON (p.id = t.place_id)
GROUP BY t.user_id
ORDER BY t.date ASC;

SELECT

u.name,

GROUP_CONCAT(p.place) AS '出張先'

FROM

trips AS t

LEFT JOIN users AS u ON (u.id = t.user_id)

LEFT JOIN places AS p ON (p.id = t.place_id)

GROUP BY t.user_id

ORDER BY t.date ASC;

結果

ちなみに区切り文字のカンマは別文字でも可能。
例えば全角の「、」で区切ってみます。

SELECT
    u.name,
    GROUP_CONCAT(p.place SEPARATOR '、') AS '出張先'
FROM
    trips AS t
    LEFT JOIN users AS u ON (u.id = t.user_id)
    LEFT JOIN places AS p ON (p.id = t.place_id)
GROUP BY t.user_id
ORDER BY t.date ASC;

SELECT

u.name,

GROUP_CONCAT(p.place SEPARATOR '、') AS '出張先'

FROM

trips AS t

LEFT JOIN users AS u ON (u.id = t.user_id)

LEFT JOIN places AS p ON (p.id = t.place_id)

GROUP BY t.user_id

ORDER BY t.date ASC;

結果

ではここから本題。

「札幌」「東京」など日本語で出ている出張先を、内部IDで表示したいと思いました。

SELECT
    u.name,
    GROUP_CONCAT(p.id SEPARATOR ',') AS '出張先ID'
FROM
    trips AS t
    LEFT JOIN users AS u ON (u.id = t.user_id)
    LEFT JOIN places AS p ON (p.id = t.place_id)
GROUP BY t.user_id
ORDER BY t.date ASC;

SELECT

u.name,

GROUP_CONCAT(p.id SEPARATOR ',') AS '出張先ID'

FROM

trips AS t

LEFT JOIN users AS u ON (u.id = t.user_id)

LEFT JOIN places AS p ON (p.id = t.place_id)

GROUP BY t.user_id

ORDER BY t.date ASC;

結果

なんだこれは。

どうやらGROUP_CONCATは、INTうまく帰ってこない模様。
ということで、型を文字列型に変換してみます。

SELECT
    u.name,
    CAST(GROUP_CONCAT(p.id SEPARATOR ',') AS CHAR) AS '出張先ID'
FROM
    trips AS t
    LEFT JOIN users AS u ON (u.id = t.user_id)
    LEFT JOIN places AS p ON (p.id = t.place_id)
GROUP BY t.user_id
ORDER BY t.date ASC;

SELECT

u.name,

CAST(GROUP_CONCAT(p.id SEPARATOR ',') AS CHAR) AS '出張先ID'

FROM

trips AS t

LEFT JOIN users AS u ON (u.id = t.user_id)

LEFT JOIN places AS p ON (p.id = t.place_id)

GROUP BY t.user_id

ORDER BY t.date ASC;

CAST()関数で"CHAR型"に変換してみました。

結果

参考文献

関連ポスト

【MySQL】CONCATとGROUP_CONCATを駆使して、複数レコードの文字列を結合してみる。

いちばんやさしい SQL 入門教室

created by Rinker

ソーテック社

-MySQL
-CAST, GROUP_CONCAT, データベース, 文字列型

: 【MySQL】カラムの順番を変更する ALTER TABLE

MySQLでカラムの移動を行うSQLを備忘録としてポストします。

: 【MySQL】サブクエリを使ったUPDATEで「ERROR 1093」が出る場合の対処方法

hogeテーブルのserialカラムにある文字列「－」（全角ハイフン）を、「-」（半角ハイフン）に変換したいという場合に、同じテーブルをサブクエリ内に書くと「ERROR 1093」が出るので、その対策を備忘録としてポストします。

: 【MySQL】Select結果でUpdateするSQLの書き方

あるテーブルで、姓名カラムを2つ（姓：name_family、名：name_first）と分かれていたのが仕様変更となり、姓名のカラムを name カラム 1つに結合することになりました。

その際、既にインサートされているレコードの「姓」「名」を「姓　名」と全角スペース区切りで結合しつつアップデートする必要がありましたので、備忘録としてその方法をポストします。

: 【SQL】縦方向に保存されている進捗の様なレコードを条件づけて抽出する方法

縦方向に存在するステータスのようなレコードを文字列してとグループ化し、SELECTする方法に悩んだので備忘録としてポストしておきます。

: 【MySQL】 CSVデータをphpMyAdminからインポートする方法

phpMyAdminを使ってデータベース（MySQL）へCSVデータをインポートする際にハマりかけたので、ポイントを整理して備忘録としておきたいと思います。

PREV: 【UNIX】tarコマンドで任意のディレクトリを展開先として指定する方法
NEXT: SSLの更新をしたら、CAの浸透待ちで24時間ぐらいFirefoxでのアクセスが出来なかった件

【MySQL】GROUP_CONCAT()する際のデータ型は文字列型（CHRAなど）に変換する必要あり

GROUP_CONCAT()で取得する際のデータはCAST()で文字列型に変換する

GROUP_CONCAT()で、ユーザ別の出張先を取得

参考文献

関連ポスト

【DIY】2x4や1x4など、SPF材サイズ早見表 (564,960)

【知識】電話番号の+81の意味と固定電話・携帯電話番号の書き方 (279,678)

TPPやLCCなど、時事や普段使っている「アルファベット略語」を集めてみた (257,918)

【歴史】江戸幕府歴代将軍一覧 (143,590)

【知識】牛肉の部位「おっぱい」は本当に乳房なのかが気になったので調べてみた (133,246)

【Excel】ダブルクォーテーションでデータを囲んだCSVファイルの作成方法 (131,308)