GROUP_CONCATのgroup_concat_max_len()が有効にならない対処法

MySQLでGROUP_CONCATを使ったカラムの連結処理はとても便利ですが、
ローカルでは動いているのに、レンタルサーバで実行すると、
途中で切れてしまう事もあるのではないでしょうか。
そんな時には「group_concat_max_len」でより大きな値を設定して変更する事ができます。

GROUP_CONCATのgroup_concat_max_len()が有効にならない対処法

とてもありがたい「group_concat_max_len」による一時的な設定変更ですが、
レンタルサーバーでは有効にならない場合があります。

「group_concat_max_len」の設定方法としては以下のような記述です。

このようなSQLを私はPHP側から実行してローカルでテストしていました。
どうせ、サーバー上は直接こういう設定変更はできないだろうし、
常に拡張しておきたいわけでもないからと、

その都度、必要なプログラムのみ、指定するという使い方です。

しかし、このままではそもそもレンタルサーバーでは実行しても有効にはなりませんでした。
PHPでやっている時には、
サーバーからのメッセージを受け取っていなかったので気づかなかったのですが。。。

phpMyAdminで実行してみると分かります。

実行後に表示されるメッセージです。

「Access denied;」ですので権限がない・・・と。

ローカルはroot権限ですから、気づくわけもない訳です。

ちなみに私はXSERVER環境でこの壁に当たりましたが、
他のレンタルサーバーでも、あまり大きな値は設定していないと思います。

現在の「group_concat_max_len」サイズを確認するには、
以下のSQLを実行します。

返答は以下のようになります。

lenですので、1024文字(1024 byte）が設定されていました。

諦めそうになった時ですが、何でダメなんだろうと考え気づいたのがココです。

SET GLOBAL group_concat_max_len = 200000

グローバル変数に対しての設定変更ですので、
MySQLサーバー全体に有効になる設定を変更しようとしているのです。

サーバー内に同居している他のユーザーさんに対しても、
この設定が有効になってしまうという事は変更できなくて当然です。

そこで、以下のように変更します。

set Session group_concat_max_len = 200000;

これでちゃんと設定がこのセッションに限って有効になります。
実際にphpMyAdminから実行して試します。

返答は以下のようになります。

対処法は。

これだけです。

13.7.4 SET 構文

MySQL

SET variable_assignment [, variable_assignment] ... variable_assignment: user_var_name = expr | [GLOBAL | SESSION] system_var_name = expr | [@@global. | @@session. | @@]system_var_name = expr

1 2 3 4 5 6

SET variable_assignment [, variable_assignment] ...   variable_assignment:       user_var_name = expr     | [GLOBAL | SESSION] system_var_name = expr     | [@@global. | @@session. | @@]system_var_name = expr

（引用）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.4 SET 構文

何をしようと思ったのか？

皆さん、GROUP_CONCATが使いたいシーンは、
やっぱり結構無理がかかっている処理をしたいときだろうなと思うのです。

DISTINCTも使えばデータはユニークになりますしとても便利です。

私の場合、別件で利用していたテーブルデータを、
集計した結果を保持している側に、
元の集計元のデータが追えるようにGROUP_CONCATで、
データを連結して保持していました。

こんな感じでバー分割のCSV形式でした。

arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin

事情があって、このようなデータを持っているレコード同士をまとめて、
最終的にはユニーク化して表示したい。

ある長い部分をつなげてみると、このくらいのデータになっています。

lacnic|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|ripencc|ripencc|ripencc|apnic|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin|arin

もう何がなんだか・・・って感じですよね。

でも値が取れてしまえば、配列に分割(explode)して、
重複削除(array_unique)をしてしまえば、すっきりと関係した情報のリストが取れると。

無茶な方法を取っているのですが、表示したいだけなのでむしろ助かるのがこういう乱暴なアプローチ。

実際には、以下のようなリストを作っています。
以下のリンク先の表で「RIR」列に表示している値がそれです。

IPアドレスの「11.0.0.0/8 (011/8)」などの範囲に対して、
グローバルIPアドレスを各国に割当てているRIRはどこが含まれているのか？です。
（たぶん大半の人にはどうでもいい事でしょう）

そんなこんなで、GROUP_CONCATの値を拡張するには、
共用サーバーでは、Session　で設定しよう。

参考

ありがとう。

（参考）MySQLのgroup_concatの結果が短い気がするのは環境変数で制限されているから。 | 三度の飯とエレクトロン

当サイト内のコンテンツおよび画像を含むすべてにおいて、管理人が著作権を保持しております。
当サイトでご紹介しております写真につきましては著作権の放棄はしませんが、
ライセンスフリーでご利用いただいて構いません。

コンテンツを有益であると感じていただけましたら非常に光栄です。
ありがとうございます。
サイト内コンテンツを引用される際には、出典元として当サイト(個別記事)へのリンクをお願いいたします。
申し訳ございませんが、無断転載、複製をお断りさせて頂いております。

*本ページ内に記載されされた各社ブランド、サービス名、商標、登録商標については、
各社ブランドのロゴや商標等に関する帰属についてをご確認ください

最後までお読みいただきありがとうございました。

お勧めさせて頂きたい投稿です

• 【巧妙な仕組】Google+へAndroidの写真が勝手にアップロードされてしまう流れは設定ミスではない

• JetPackサイト統計データが消えた場合に復活する手順(してもらう)

• 【待望】WALLET ポイントがauID(au電話番号)間で相互にやりとりできるようになりました

• 禁断の機能がau公式解禁！WALLETポイントの現金化がついに開放。

• 何もしないと、auポイントが2014/05月分以降は携帯利用料から付与されなくなります

• au WALLET™カードはauを解約してもプリペイドとして使い続けられるが、有効期限には注意

• じぶん銀行が動いた！コンビニATM利用が無料・他行振込無料（回数制限なし）で使える

• 【旬】「白すぎる」と賛否両論？姫路城見るなら今秋がおすすめ！その理由は？｜castlehimeji.com

• Google+の投稿が勝手に自動でブログになっていくPluCialが便利



アイキャッチ画像に利用させて頂いております、各社ブランドのロゴやシンボルに関しては、
各社ブランドのロゴや商標等に関する帰属についてをご確認頂けますようお願い致します。

AdMax Promotion

ブログ記事のご紹介

すべて展開 | すべて省略

Rakuten Promotion

ページ

すべて展開 | すべて省略