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