山本ゆうごです。データベースから大量のテキストを出力する際のTipsをご紹介します。

SELECT ... INTO OUTFILE の使いづらさ

mysqlからCSV出力する際のサンプルとして SELECT ... INTO OUTFILE が紹介されるケースがありますが、このOUTFILEの出力先はDBが動いてるサーバ上のディレクトリでしかないので扱いづらいです。DBが動いてるマシンに直接ファイルアクセスできる環境というのが限られていますし、またmysqlが動いてる権限で自由にファイルが書き出せるというのも難しいです。

LOAD DATA LOCAL INFILE のようにSELECT ... INTO LOCAL OUTFILE みたいなオプションなどがあればいいのですが、それもないです。

mysqlコマンドはリダイレクトするとタブ区切りテキスト出力モードになる

mysqlコマンドは標準出力とリダイレクトでは違う結果を出します。

標準出力で出力した結果

$ mysql -e "select 'aa' as fld1,'bb'as fld2;"
+------+------+
| fld1 | fld2 |
+------+------+
| aa   | bb   |
+------+------+

よくみる罫線つきの結果です。

パイプやリダイレクトした結果

$ mysql -e "select 'aa' as fld1,'bb'as fld2;" | less -U
fld1^Ifld2
aa^Ibb

less -U は制御記号を表示するオプションで、^I はタブを意味します。

つまり標準出力とリダイレクトで挙動が変わります。このリダイレクトのタブ区切りが非常に便利です。上記は別コマンドにパイプしていますがファイルへのリダイレクトでもタブ区切りになります。

フィールド内改行はどうなるのさ？

エクセル方式CSVではフィールド内改行も改行コードとして入るため、１レコードが複数行になります。mysqlタブ区切りではどう表現されるでしょう。

標準出力

$ mysql -e "select 'a\na' as fld1,'bb'as fld2;"
+------+------+
| fld1 | fld2 |
+------+------+
| a
a  | bb   |
+------+------+

改行は生改行として扱われます。

パイプ・リダイレクト

$ mysql -e "select 'a\na' as fld1,'bb'as fld2;" | less -U
fld1^Ifld2
a\na^Ibb

フィールド内改行は\n にエスケープされます。多くの言語の文字列リテラルと同じです。 つまりこれで「１行１レコード」が保持されるので非常に扱いやすいです。

フィールド内タブはどうなるのさ？

標準出力

$ mysql -e "select 'a\ta' as fld1,'bb'as fld2;"
+------+------+
| fld1 | fld2 |
+------+------+
| a     a  | bb   |
+------+------+

生のタブで出力されます

パイプ・リダイレクト

$ mysql -e "select 'a\ta' as fld1,'bb'as fld2;" | less -U
fld1^Ifld2
a\ta^Ibb

よくある文字列リテラルと同じように\tでエスケープされます。

ここまで来るとあとは分かりますね。バックスラッシュは\\ でエスケープされます。 大半のプログラミング言語のバックスラッシュでエスケープするタイプの文字列リテラルと同じです。 とても扱いやすい形式です。

大量のテキストだしても大丈夫？

この手のテキストは大きなデータを出力する事が多いです。遅かったら話になりません。 国税庁の500万行の法人データベースをmysql（mariadb）にほりこんでるので、それで実験してみましょう。

1行だけ取り出すとこういうデータベースです。

$ mysql to_csv_test -e 'select *  from companies limit 1\G;'
*************************** 1. row ***************************
                        id: 1
          corporate_number: 1000012160153
                   process: 01
                   correct: 1
               update_date: 2018-04-02
               change_date: 2015-10-05
                      name: 釧路検察審査会
             name_image_id:
                      kind: 101
           prefecture_name: 北海道
                 city_name: 釧路市
             street_number: 柏木町４－７
          address_image_id:
           prefecture_code: 01
                 city_code: 206
                 post_code: 0850824
           address_outside:
  address_outside_image_id:
                close_date: 0000-00-00
               close_cause:
successor_corporate_number: 0
              change_cause:
           assignment_date: 2015-10-05
                    latest: 1
                   en_name: Kushiro Committee for the Inquest of Prosecution
        en_prefecture_name: Hokkaido
              en_city_name: 4-7, Kashiwagicho, Kushiro shi
        en_address_outside:
                  furigana: クシロケンサツシンサカイ
                   hihyoji: 0
                created_at: 2021-05-30 13:23:15
                updated_at: 2021-05-30 13:23:15

これをファイルにリダイレクトしてタブ区切りテキストにします。

$ time mysql to_csv_test -s -e 'select *
from companies' > out.tab
Killed

あらダメですね。Killedになっちゃいました。これはデフォルト状態ではmysqlが結果セットをメモリに溜め込んでしまうためです。3Gのメモリの非力なマシンではプロセスそのものが維持できません。

ストリームとして結果セットを扱いたいときには --quick オプションを使いましょう。

$ time mysql to_csv_test --quick -e 'sele
ct * from companies' > out.tab

real    0m33.545s
user    0m21.302s
sys     0m1.279s

$ wc -l out.tab
5015296 out.tab

$ ls -hl out.tab
-rw-r--r-- 1 root root 1.2G Aug 12 10:46 out.tab

このように、500万行、1.2Gバイトのタブ区切りテキストが33秒で出力できました。 じゃぁこの33秒が速いのか遅いのかを比較対象としてmysqldumpでテーブルごとダンプしてみましょう。

$ time mysqldump  to_csv_test companies -
-single-transaction > out.dump

real    0m26.246s
user    0m14.950s
sys     0m1.672s
$ ls -lh out.dump
-rw-r--r-- 1 root root 1.5G Aug 12 10:52 out.dump

26秒で1.5Gのファイルが出力されました。sqlの方がタブ区切りよりも冗長になるのでテキストファイルが大きくなるのは想定どおりです。

まとめ：mysqlコマンドのタブ区切り出力は使える！

mysqlダンプした結果を別のプログラムで処理をするのは難しいですが、タブ区切りであればtabでsplitするだけでファイルが加工できるため、非常に扱いやすいです。

mysqldumpでもtabオプションはありますが、カラム指定などはできないため、自由にsqlを記述できるmysqlコマンドの方が汎用性があります。

またエクセル方式のCSVがほしい場合にはこの「バックスラッシュでエスケープされたタブ区切りテキスト」をCSVフォーマットに変換する必要があります。「tab to csv」のシンプルスクリプトに関しては別の記事にします。