【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;