MySQL で列名付き CSV ファイルを出力する

ありそうでなかったので記事にしてみました。
もしかすると「車輪の再発明」をしている可能性も大ですが、自分に対する備忘録も兼ねて記録を残します。
お気づきの点などがございましたら、コメントいただけるとありがたいです。

MySQL では INTO OUTFILE で列名付きのCSVファイルが出力できない

MySQL では SELECT 文に INTO OUTFILE 句をつけることで CSV ファイルを出力することができます。（作成されるCSVファイルは新規作成のみで追記や上書きは不可なので、定期的に実行するような処理では出力先ファイルがある場合にはクエリ実行前にファイルを削除する処理を別途作成する必要があります）

CSVファイル出力記述例

SELECT COLUMN1,COLUMN2
INTO OUTFILE '/tmp/outputfile.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM TABLE_NAME;

しかし INTO OUTFILE で出力される内容は SELECT 文で抽出されたデータ部分のみであり、CSVファイルの先頭行に列名は出力されていません。Excel などに取り込む場合には列名があると便利なので列名を出力するオプションがありそうなのですが、どうも無いようです。

列名付きのCSVファイルを出力する一般的？な方法

ネットを検索してみると列名付きの CSV を出力したい時には、列名の羅列を作成しておき、それと UNION して列名付きCSVファイルを作成する、といった事例がヒットします。

一般的な列名付きCSVファイルの出力方法

SELECT *
INTO OUTFILE '/tmp/outputfile.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM (
SELECT 'COLUMN1','COLUMN2'
UNION
SELECT COLUMN1,COLUMN2
FROM TABLE_NAME
) AAA;

列名が少ない場合は上記のようなクエリを記述しても大した手間ではないのですが、列名が多くなると面倒そうですし、列名と値がずれてしまうようなミスを発生させてしまうかもしれません。

列名を自動的に取得して設定する方法を考えてみた

そこで、なるべく手作業なしに列名付きの CSV を出力する方法を考えてみました。

クエリ内容

SET SESSION group_concat_max_len=100000;

SET @TARGET_SCHEMA:='dbname';
SET @TARGET_TABLE:='tablename';
SET @OUT_FILE:='/tmp/hogehoge.csv';

SELECT CONCAT("'",GROUP_CONCAT(COLUMN_NAME SEPARATOR "','"),"'") INTO @HEADER FROM (
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA=@TARGET_SCHEMA AND TABLE_NAME=@TARGET_TABLE
ORDER BY ORDINAL_POSITION
) AAA;
SET @SQL:=CONCAT("SELECT * INTO OUTFILE '",@OUT_FILE,"' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' FROM (SELECT ",@HEADER," UNION SELECT * FROM ",@TARGET_SCHEMA,".",@TARGET_TABLE,") AAA");
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

@TARGET_SCHEMA にデータベース名、@TARGET_TABLE にテーブル名、@OUT_FILE に出力ファイル名を指定してクエリを実行すると、列名付きCSVファイルを出力することができます。（上記クエリ内容をファイル化しておき、source コマンドなどで呼び出して実行するのが良いかと思います）

（処理のかんたんな説明）

• 対象のテーブルの列名は INFORMATION_SCHEMA.COLUMNS テーブルより取得しています。
• 列名の羅列の作成には GROUP_CONCAT() を使用しています。GROUP_CONCAT() で生成される文字列長には制限があるため、実行前に group_concat_max_len の値を変更して制限を緩和しています。（制限を緩和しておかないと GROUP_CONCAT() で作成される列名の羅列の文字列が長くなった時に「Warnings:Row XX was cut by GROUP_CONCAT()」が発生して一部の列が出力されない悲しい結果になります）
• GROUP_CONCAT() の区切り文字のデフォルトはカンマ(,)のみになっていて、そのままでは列見出しとしては使えないので、カンマ(,)の前後にシングルクォートがついたもの(',')に置換してさらに文字列の前後にシングルクォート(')を加えることで、列見出しとして利用可能なシングルクォートで囲まれた列名の羅列を生成しています。
• INTO OUTFILE で指定する出力先ファイル名に変数を指定できない等の理由のため、動的 SQL を使ってクエリを実行しています。

テーブルの一部の列・一部の行だけを出力するには

クエリをカスタマイズして対応するよりは、CREATE VIEW で条件に合うビューを作成しておいて、上記クエリの @TARGET_TABLE にビュー名を指定するのがメンテナンスの観点からも良いような気がします。