GROUP_CONCATを利用して、列名を列挙したSQLを生成する

MYSQLには、GROUP_CONCATというすばらしい関数があるのですが、ような複数のレコードのとある値を、一行にまとめるということができる関数で、そりゃーもう便利です！！今回はこのGROUP_CONCATを利用して、SQLからSQLを生成するなんていうサンプルを作ってみました

RAVPower ソーラーチャージャー ソーラー充電器 16W 2ポート iPhone Android各種対応 RP-PC008

GROUP_CONCATを利用して、列名を列挙したSQLを生成する(SQLで文字列遊び）

よく、TRIGGERの生成で、Aテーブルのレコードが削除された時に、削除前のレコードを、Bテーブルに退避するなんていうSQLをトリガーに記載するさいに、

DROP TRIGGER IF EXISTS users_del ;

DELIMITER ;;
/*!50003 CREATE TRIGGER test.users_del AFTER DELETE ON test.users
FOR EACH ROW
BEGIN

  INSERT INTO arc_test.users (id,name) VALUES (OLD.id,OLD,name) ;

END */;;
DELIMITER ;

なんて書いて、SQLを一個一個組み立てるわけなのですが、列数が多いとだんだん面倒になってきて、ついでにミスも出してしまうので、私は、SQLで上記のようなSQL文を生成するようにしております。 その時、役に立つのが、GROUP_CONCATという関数です。GROUP_CONCATの説明はざっくり説明するレベルでは、該当のカラムを横倒する関数ですのですが、説明してもわかんないっすよね。

ちょっとここの説明は読み飛ばしてもらって結構です

# mysql -N -uroot -prootpasswd << '_EOT_' > /tmp/trigger_insert.sql
SET @schema='test' ;
SET @table='users' ;

SELECT 
  'INSERT INTO arc_' || TABLE_SCHEMA || '.' || TABLE_NAME || ' ' || 
  '(' || GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION ASC) || ') VALUES '
  '(' || GROUP_CONCAT('OLD.' || COLUMN_NAME ORDER BY ORDINAL_POSITION ASC) || ') ;'  AS STRSQL
FROM 
  INFORMATION_SCHEMA.columns 
WHERE
  TABLE_SCHEMA= @schema AND TABLE_NAME = @table 
;
_EOT_

如何でしょうか？ちなみにこんな結果になりました

# cat /tmp/trigger_insert.sql
INSERT INTO arc_test.users (id,seq_id,name) VALUES (OLD.id,OLD.seq_id,OLD.name) ;

簡単に説明しますと テーブル定義（カラム定義）などは、INFORMATION_SCHEMA.columns にあります。ここから、

• 対象のスキーマと
• 対象のテーブル名

で抜き出して。

カラム名を、ORDINAL_POSITION順に昇順にソートした順番で、GROUP_CONCATしているSQLを生成するSQLになります。 今回は削除トリガで、該当レコードが消された場合、消される前のレコードを別スキーマに退避する処理をしているため、このような例になりましたが、応用はなんにでも効きますので、ぜひお試しください

追記

group_concat_max_len の値が小さいと、列挙しているカラムがぶった切られます。SET GLOBAL でここの値を大きめに設定しておいたほうがいいです

MariaDB [(none)]> show global variables like '%concat%' ;                                                                              
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 1024  |
+----------------------+-------+

いかがでしょうか？