MySQLでGROUP_CONCATしたフィールドに対して疑似的にLIMITを実現する方法

MySQLでGROUP_CONCATしたフィールドに対して疑似的にLIMITを実現するには、GROUP_CONCATで生成された文字列に対して、SUBSTRING_INDEXを使って文字列を切り出す方法が簡単です。

# 下記はid, codeをカラムに持つテーブルで、codeカラムでGROUP BYして、codeごとにidをlimitで取得する例です。
SELECT
 code
,SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY id DESC), ',', :limit) # :limitの部分に取得したい件数を指定。
FROM table
GROUP BY code

長所は、下記のように条件を指定して、LIMIT句で取得件数を指定したクエリを何度も発行する必要がないところです。
特に、一回あたりクエリの発行コストが高い場合は、GROUP_CONCATを使って一度に取得したほうが最終的な実行時間をかなり節約することができます。

SELECT
 id
FROM table
WHERE code = 'A'
ORDER BY id DESC
LIMIT :limit;

ただし、短所も多いので、使用する際は、これらの短所について十分に考慮したうえで使ってください。

• GROUP_CONCATで生成された文字列に対して、SUBSTRING_INDEXを使って文字列を切り出すという文字列処理なので、無駄が多い。
  • 特にGROUP_CONCATで生成された元の文字列が長い場合。
• GROUP_CONCATの区切り文字が、GROUP_CONCATされる元の文字列に含まれていると正しくLIMITされない。
  • 例:GROUP_CONCATされる元の文字列にカンマが含まれているのに、カンマを区切り文字で指定している場合。
• GROUP_CONCATの最大文字数制限を超えた場合は、機能しない。
  • MySQLのGROUP_CONCATの最大文字数制限は、「SHOW VARIABLES LIKE '%group_concat%';」で調べられます。デフォルト値は1024のようです。
  • 実行中のセッションでGROUP_CONCATの最大文字数を変更するには、「SET SESSION group_concat_max_len = 1000000;」を実行すればOKです。
  • 単純に1024文字で、区切り文字はカンマ1文字、単純な整数のIDで考えると、31桁のID x 32個 + 区切り文字1文字 x 31個 まではLIMITが正しく動作します。

最後にGROUP_CONCATで取得したフィールドを、PHP側でarrayに展開するちょっとしたコードを下記に紹介します。

<?php

// $rows: 取得した行
// $field: GROUP_CONCATされたカラム
// $delimiter: GROUP_CONCATで利用した区切り文字
function extractGroupConcatField($rows, $field, $delimier=',')
{
    $values = [];
    foreach($rows as $row)
    {
        if(isset($row[$field]))
        {
            foreach (explode($delimiter, $row[$field]) as $value)
            {
                $values[] = trim($value);
            }
        }
    }
    return $values;
}