MySQL から「改行」「カンマ」「ダブルクォート」「シングルクォート」を含むフィールドをエクスポートして他のデータベースにインポートする方法

MySQL からのエクスポート

やたら長い標題になってしまいましたが、MySQL からエクスポートしたデータに以下のような内容が含まれていた際に、その情報を失わずにいかに他のデータベース(や Excel*1 など)にインポートするか、ということです。なお、ここでは CSV(Character Separated Values)で吐き出した場合を考えています。

  • 改行
  • カンマ
  • ダブルクォート
  • シングルクォート

MySQL からのエクスポートはタブ区切り

実際やってみるとわかりますし、こちらのページにもありますように、MySQL から標準出力した場合には「タブ区切り」でしか出力されません。

タブ区切りのレコードを sed でいい感じに置換する

となると生データはタブ区切りであることが前提の上で、そのデータを置換してデータの欠損がないように適切にセパレートしてやる必要があります。

これを踏まえた上で、以下、考えてみます。

まずは基本となるコマンド

シェルから以下のコマンドを実行すると、当該テーブルのデータが CSV ファイルとして書き出されます。

# mysql -uusername -ppassword --database=db_name --host=host_address -B -e "SELECT * FROM table_name" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > table_name_output.csv

短いコマンドなので説明の必要はないでしょうが、上記コマンドは以下の手順を踏んでいます。

  • SQL を実行して全てのカラムの全てのレコードを表示している

    • 当然ながらこの SQL は自由に書いて構いませんし、オプションでカラム名のレコードを省略することもできます
  • 取得できた全てのレコード(というかテキストの塊)に対し、「タブ」を「","」に置換している

  • 上記の置換だと、ファイルの一番先頭および一番最後の部分は「タブ」にマッチしないため、その2つの部分に個別に「"」を付与している

  • 改行の調整

  • table_name_output.csv というファイルへの出力

上記コマンドの問題点

上記のコマンドでは、セパレータが「,」、フィールドの区切りが「"」となっています。ここで問題となるのは、フィールド中に「,」や「"」が含まれていた場合です。また、フィールド中に改行が含まれていた場合も当然に問題となります。

ひとつひとつ対処法を見ていきます。

  • フィールド中に改行が含まれていた場合
    • この場合はフィールドの「引用記号」の中に改行を含んでやる必要があります。すなわち、以下のような CSV ではダメです(改行の時点で別レコードとみなされる)。
100,tanaka,こんにちは
お元気ですか

「引用記号」が「"」であるとして、以下のようにする必要があります。これならば正しくインポートされます。

"100","tanaka","こんにちは
お元気ですか"
  • フィールド中に「"」が含まれていた場合

    • この場合は「"」を「""」にエスケープした上で、「引用記号」で囲む必要があります*2
  • フィールド中に「'」が含まれていた場合

    • これは特に問題はありません(もちろん「引用記号」に「'」を指定すると問題ですが)。
  • フィールド中に「,」が含まれていた場合

    • この場合は改行の場合と同じく、しっかりと「引用記号」で囲ってやる必要があります。

結論

では上記の条件を満たすような CSV を作るためには、MySQL 標準で吐き出された TSV に対してどのような置換を施せばいいのでしょうか。結論は以下のとおりです。

  • まず「"」を「""」に置換する

    • 「タブ区切り」であるので「"」が存在する場合はそれはフィールド中に限られます。したがって「"」を「""」に置換すればエスケープする必要がある部分を過不足無く埋め尽くせます。
  • タブ区切りを「"」区切りに変更する

    • タブの区切りを「"」の区切りに変更しましょう。この際、「"」以外の区切りにしてはいけません。なぜならそうした場合、もしフィールド中にその文字が含まれていると誤認識されるからです。先ほど「"」をエスケープしましたので、「"」に区切り文字を変換すれば誤認識は起こり得ません。
  • 実行するコマンド

    • 最終的に実行するコマンドは以下のようになります。
# mysql -uusername -ppassword --database=db_name --host=host_address -B -e "SELECT * FROM table_name" | sed 's/"/""/g;s/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > table_name_output.csv

これで、フィールドに「改行」「ダブルクォート」「シングルクォート」「カンマ」が含まれていても、吐き出したファイルにより問題なくインポートができると思います。

*1:Excelは無理かも

*2:「引用記号」で囲まないとダブルクォートが消えてしまう(PostgreSQL で確認)

Powered by はてなブログ