頑張らない!でも諦めない!必死にならずにええかげん

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を実行します。

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

Variable_name Value
group_concat_max_len 1024

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

 

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

SET GLOBAL group_concat_max_len = 200000

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

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

 

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

set Session group_concat_max_len = 200000;

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

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

Variable_name Value
group_concat_max_len 200000

対処法は。

これだけです。

GLOBAL ではなく SESSION に対して変更する。

13.7.4 SET 構文

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

 

プロモーション(btm)

Google or AdMax Promotion (it)

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

皆さん、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の結果が短い気がするのは環境変数で制限されているから。 | 三度の飯とエレクトロン


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


最後までお読みいただきありがとうございました。
アルゴリズン


役に立ったと思っていただけたら、以下からシェア頂けたら幸いです^^

QRコードからもこのURLを開けます。

アイキャッチ画像に利用させて頂いております、各社ブランドのロゴやシンボルに関しては、
各社ブランドのロゴや商標等に関する帰属についてをご確認頂けますようお願い致します。
ロゴの使用等に関する苦情・ご意見等がございましたら管理者までご連絡ください。

AdMax Promotion

ブログ記事のご紹介
すべて展開 | すべて省略

Rakuten Promotion

ページ
すべて展開 | すべて省略

 - MySQL

Message

メールアドレスが公開されることはありません。

  関連記事

eye_phpmyadmin
(テーブル単位)それでもphpMyAdminでエクスポートがサーバーエラーになってしまう場合の対処法

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_phpmyadmin
phpMyAdminでMySQLデータをサーバー上にエクスポートする

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_kagoya
KAGOYA™(カゴヤ)のMySQL単独プランの利用手順

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_phpmyadmin
phpMyAdminを利用したMySQLデータのエクスポート形式一覧とデータ

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_phpmyadmin
phpMyAdminを利用したエクスポート・インポートで最も適した設定とは?

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_local_phpmyadmin
ローカル環境へのphpMyAdmin設置

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_phpmyadmin
レンタルサーバ環境へのphpMyAdmin設置

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_local_mysql
ローカル環境へのMySQL設置

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_phpmyadmin
phpMyAdminを利用したMySQL同期とWM3500Rポート解放

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_phpmyadmin
phpMyAdminを利用したMySQLデータのエクスポート

Google or AdMax Promotion(it) 禁断の機能がau公式 …