MySQLでEXPORT（INTO OUTFILE）したCSVファイルを他DB（IBM DB2）にインポートする方法
MySQLのデータをエクスポートして、他DBMSにデータをインポートする際にツールを使わない場合、たぶん・・なんですが、CSVをというフォーマットを介してデータ移行をするかと思っているのですが、そこで必ず引っかかってくるのが３つあり 1. データベースの文字コードの問題 2. "（ダブルクォーテーション）の問題 3. null値の問題 あたりを気にする必要があるかな？と思っております。 1.に関しては、MySQLの設定や、Linuxではあれば、nkf,iconv等を変換ツールを介してクリアできます 残りの、2. 3. ですが、MySQLのEXPORTのオプション設定では全ての要件をクリアするのが難しいかんじです・・。
今回は・・・・ MySQLのデータをEXPORT -> DB2のテーブルにIMPORTという作業を実現できるかに挑戦しております。
DB2では、NULLという文字をNULL値と認識することはできません、DB2のNULL認識では、以下のような形式でないと、IMPORTは正常に行えません。またダブルクォーテーションのエスケープは、\"ではなくて、""と二回続けて書きます
▼ IBM DB2でちゃんとIMPORTできるCSVファイル形式
\! cat /tmp/t2_1.text 1,"AAAA","2012-03-08 13:38:24" ,"AAAA","2012-03-08 13:38:24" 3,,"2012-03-08 13:38:24" 4,"AAAA", 9,"A""AAA","2012-03-08 14:05:36"
ポイントとしては、 ・NULLはNULLと入っておらず、単純な空の値であることです。"（ダブルクォーテーション）もはいってはいけません。DB2は空値とNULL値は別物と判断します。このあたりの説明は昔、こんなところで説明しました（http://www.s-quad.com/wordpress/?p=272）
▼ IBM DB2でのインポートコマンド例 もう一個余談ですが、DB2のIMPORTは以下のようなコマンドを発行します。
IMPORT FROM /tmp/t2_1.text OF DEL MODIFIED BY delprioritychar TIMESTAMPFORMAT="YYYY-MM-DD HH:MM:SS" CODEPAGE=954 COMMITCOUNT 1000 REPLACE INTO db2admin.t2 ;
今回の検証では、MySQLでエクスポートしたCSVファイルを、いかにDB2でIMPORTできる形式にMySQLのオプションを駆使してできるかを検証しております。
以下、だらだら書きます。
▼ 今回は、以下のテーブルを題材に進めます
-------------- CREATE TABLE `t2` ( `id` int(11) DEFAULT NULL, `detail` varchar(10) COLLATE utf8_bin DEFAULT NULL, `wdate` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin -------------- mysql> select * from t2; +------+--------+---------------------+ | id | detail | wdate | +------+--------+---------------------+ | 1 | AAAA | 2012-03-08 13:38:24 | | NULL | AAAA | 2012-03-08 13:38:24 | | 3 | NULL | 2012-03-08 13:38:24 | | 4 | AAAA | NULL | | 9 | A"AAA | 2012-03-08 14:05:36 | +------+--------+---------------------+ 5 rows in set (0.00 sec) --------------
▼ MySQLエクスポートの振る舞い
・まずは何も指定しないエクスポート
mysql> \! rm -rf /tmp/*.text SELECT * from t2 INTO OUTFILE "/tmp/t2_1.text"; \! cat /tmp/t2_1.text 1 AAAA 2012-03-08 13:38:24 NULL AAAA 2012-03-08 13:38:24 3 NULL 2012-03-08 13:38:24 4 AAAA NULL 9 A"AAA 2012-03-08 14:05:36 \! rm -rf /tmp/*.text
・TERMINATED、ENCLOSED BY が空白
SELECT * from t2 INTO OUTFILE "/tmp/t2_1.text" FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' ESCAPED BY ''; \! cat /tmp/t2_1.text 1 AAAA 2012-03-08 13:38:24 AAAA 2012-03-08 13:38:24 3 2012-03-08 13:38:24 4 AAAA 9 A"AAA 2012-03-08 14:05:36
※データは固定長で出力されます
・TERMINATEDに","を指定
\! rm -rf /tmp/*.text SELECT * from t2 INTO OUTFILE "/tmp/t2_1.text" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' ESCAPED BY ''; \! cat /tmp/t2_1.text 1,AAAA,2012-03-08 13:38:24 NULL,AAAA,2012-03-08 13:38:24 3,NULL,2012-03-08 13:38:24 4,AAAA,NULL 9,A"AAA,2012-03-08 14:05:36
・TERMINATEDに"," ENCLOSED BY '"'を指定
\! rm -rf /tmp/*.text SELECT * from t2 INTO OUTFILE "/tmp/t2_1.text" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''; \! cat /tmp/t2_1.text 1,"AAAA","2012-03-08 13:38:24" NULL,"AAAA","2012-03-08 13:38:24" 3,NULL,"2012-03-08 13:38:24" 4,"AAAA",NULL 9,"A"AAA","2012-03-08 14:05:36"
※カンマ区切りで且つ、文字型は、"でくくられている ・TERMINATED BY '$$sepa$$'
\! rm -rf /tmp/*.text SELECT * from t2 INTO OUTFILE "/tmp/t2_1.text" FIELDS TERMINATED BY '$$sepa$$' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''; \! cat /tmp/t2_1.text 1$$sepa$$"AAAA"$$sepa$$"2012-03-08 13:38:24" NULL$$sepa$$"AAAA"$$sepa$$"2012-03-08 13:38:24" 3$$sepa$$NULL$$sepa$$"2012-03-08 13:38:24" 4$$sepa$$"AAAA"$$sepa$$NULL 9$$sepa$$"A"AAA"$$sepa$$"2012-03-08 14:05:36"
※どーでも良い結果ですが、区切り文字に1桁以上の文字列が設定されることを確認
・ ESCAPED BY '"';
\! rm -rf /tmp/*.text SELECT * from t2 INTO OUTFILE "/tmp/t2_1.text" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'; \! cat /tmp/t2_1.text 1,"AAAA","2012-03-08 13:38:24" "N,"AAAA","2012-03-08 13:38:24" 3,"N,"2012-03-08 13:38:24" 4,"AAAA","N 9,"A""AAA","2012-03-08 14:05:36"
※ 文字列中の"は、""になっていることを確認しましたが、ついでにいうと、NULLまで"Nとなってしまった・・
・ESCAPED BY '\'
\! rm -rf /tmp/*.text SELECT * from t2 INTO OUTFILE "/tmp/t2_1.text" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\'; \! cat /tmp/t2_1.text 1,"AAAA","2012-03-08 13:38:24" \N,"AAAA","2012-03-08 13:38:24" 3,\N,"2012-03-08 13:38:24" 4,"AAAA",\N 9,"A\"AAA","2012-03-08 14:05:36"
※"がエスケープされ、NULLが\nとなった
\! rm -rf /tmp/*.text SELECT id, detail, wdate from t2 INTO OUTFILE "/tmp/t2_1.text" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''; \! cat /tmp/t2_1.text 1,"AAAA","2012-03-08 13:38:24" NULL,"AAAA","2012-03-08 13:38:24" 3,NULL,"2012-03-08 13:38:24" 4,"AAAA",NULL 9,"A"AAA","2012-03-08 14:05:36"
※ あんまし意味ない実験ですが、結果としては他と同じ
▼ 検討の結果 結局どんな形にせよ、上記で説明した。 2. "（ダブルクォーテーション）の問題 3. null値の問題 というものは改善できませんでした。残念無念 ▼じゃあどうやるの？ ここまでいくともう無理やりです。EXPORTコマンドで改善できないのであれば、SQLでこの問題を解決しようといった半ば強引な手法です。
というわけで、こんなコマンドを発行してみました。
\! rm -rf /tmp/*.text SELECT replace(ifnull(id,''),'"','""'), replace(ifnull(detail,''),'"','""'), replace(ifnull(wdate,''),'"','""') from t2 INTO OUTFILE "/tmp/t2_1.text" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''; \! cat /tmp/t2_1.text "1","AAAA","2012-03-08 13:38:24" "","AAAA","2012-03-08 13:38:24" "3","","2012-03-08 13:38:24" "4","AAAA","" "9","A""AAA","2012-03-08 14:05:36" "1","","2012-03-08 18:53:23" "9999","","2012-03-08 18:53:39"
いままでの中では一番、まとも！！！！ これでいけるだろ！
ポイントとしては、MySQLの列関数を駆使して ・replaceで、"を""に変換 ・ifnullでNULL値を、""（空値）に変換 というものを経由して、結果的に、「NULLという文字は含まれない」、「"を""に変換している」ということが実現できているわけです。でも実は、これは改善できたわけではありません。冒頭で説明した、NULLは"が括られないということが実現できていないのです・・・。やっぱ、sedとか使って変換しないとだめかなあ・・・・と思いつつもまだあきらめずに頑張ります。
あと、もっと究極な手段としては、そもそも、MySQLのEXPORT(INTO OUTFILE）なんか使わねーで、SQL（もしくは、ストアドプロシージャ）でこれを強引に突破するといった手法ですかね。まだまだあきらめずに頑張っていきます。
