MySQL5.5でのエクスポートとインポート(LOAD DATA INFILE)について

コマンドラインからデータをエクスポートしてインポートする、練習をしています。
*phpMyAdminを使ってやりたいのですが、結局文字化けして基本のコマンドラインから
シンプルなデータを作って練習--勉強--しています。 )
http://q.hatena.ne.jp/1353659192
で1度できたと思ったのですが、できてませんでした。

データとエラーメッセージの記述で文字数の制限を超えてしまいますので、
http://test01.1811way.com/archives/45
に、アドバイスいただきたい内容を記述してあります。

よろしくお願いします。

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2012/12/03 11:26:47
  • 終了:2012/12/03 21:07:45

ベストアンサー

id:rouge_2008 No.3

rouge_2008回答回数594ベストアンサー獲得回数3512012/12/03 20:29:35

ポイント100pt

既出の回答にあるように、パスの区切り文字には半角スラッシュ「/」を使ってみてください。
あるいは円記号を使う場合、「\\」と2個続けても大丈夫です。
mysqldumpユーティリティでしたら「\」でも認識したのですが、mysqlは上手く処理できないようです。(※Linuxのパスの区切り文字はスラッシュ「/」ですので、おそらくWindows版だけの問題だと思いますが・・・)

・エクスポート

SELECT * FROM test01 INTO OUTFILE 'c:\\work\\backup001.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';


・インポート

LOAD DATA INFILE "c:\\work\\backup001.csv" INTO TABLE test01 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';


※改行コードも明示的に指定した方がいいです。(Windows環境であっても何も指定しない場合、デフォルトでLFになります。)

「LOAD DATA INFILE 構文」
http://dev.mysql.com/doc/refman/5.1/ja/load-data.html

LOAD DATA INFILE と SELECT ... INTO OUTFILE ステートメントの両方に対して、FIELDS と LINES 条項の構文は同じです。条項は両方とも任意ですが、もし両方が指定された場合 FIELDS は LINES に先行しなければいけません。

もし FIELDS 条項を指定すると、少なくてもどれか1つを指定する必要はありますが、その各サブ条項 (TERMINATED BY、[OPTIONALLY] ENCLOSED BY、そして ESCAPED BY) もまた任意になります。

もし FIELDS 条項を指定しなければ、デフォルトは、このように書き込んだ場合と同じようになります。

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'


もし FIELDS 条項を指定しなければ、デフォルトは、このように書き込んだ場合と同じようになります。

LINES TERMINATED BY '\n' STARTING BY ''


言い換えると、インプットを読み込む時、デフォルトは LOAD DATA INFILE が次のように機能するよう働きかけるという事です。

  • 改行部分のラインの境界を探してください。
  • ライン プリフィックスを飛び越えないでください。
  • タブのところでラインをフィールドに分割してください。
  • フィールドが引用文字によって囲まれていると思わないでください。
  • ‘\’ が先に来るタブ、改行、または ‘\’ を、フィールド値の一部である直定文字として解釈してください。

反対に、デフォルトはアウトプットを書き込む時に SELECT ... INTO OUTFILE が次のように機能するよう働きかけます。

  • フィールドの間にタブを書いてください。
  • 引用文字でフィールドを囲まないでください。
  • フィールド値内で起こるタブのインスタンス、改行、または ‘\’ を避ける為に ‘\’ を利用してください。
  • 行の最後に改行を書き込んでください。


バックスラッシュは、MySQL の中では文字列内の拡張文字ですので、FIELDS ESCAPED BY '\\' を書き込むには、単一バックスラッシュだと認識させる為に2つのバックスラッシュを指定しなければいけません。

注意:もしウィンドウズ システム上でテキスト ファイルを生成したら、ウィンドウズのプログラムは通常ラインのターミネータとして2つの文字を利用するので、ファイルを正確に読み込む為には LINES TERMINATED BY '\r\n' を利用しなければいけないでしょう。WordPad のようないくつかのプログラムは、ファイルを書き込む時 \r をライン ターミネータとして利用するでしょう。そのようなファイルを読み込む時は、LINES TERMINATED BY '\r' を利用してください。


「ELECT 構文」
http://dev.mysql.com/doc/refman/5.1/ja/select.html


・mysqldumpでも「--tab=出力するディレクトリのパス」を指定した場合、CSVでの出力が可能です。(SQLファイルも作成されます。)

mysqldump -u ユーザー名 -p パスワード --tab="c:\work" test --fields-terminated-by="," --fields-enclosed-by="\"" --lines-terminated-by="\r\n"


※上記の場合、「c:\work」にSQLとCSVファイルが出力されます。

「mysqldump — データベースバックアッププログラム」
http://dev.mysql.com/doc/refman/5.1/ja/mysqldump.html

  • --tab=path, -T path

タブによって分けられたデータファイルを生成します。各ダンプされたテーブルごとに、mysqldumpはテーブルを作成するCREATE TABLEステートメントを含むtbl_name.sqlファイルと、そのデータを含むtbl_name.txtファイルを作成します。オプション値はファイルを書き込むディレクトリです。

デフォルトで、.txtデータファイルはカラム値と、各行の最後で新しいラインの間にタブキャラクタを使用してフォーマットされます。このフォーマットは明示的に--fields-xxxと--lines-terminated-byオプションを使用することで特定することができます。

注:このオプションはmysqldumpがmysqldサーバと同一のマシンで作動している場合のみ使用されるべきです。FILE権限を保持しており、サーバはユーザの指定してアファイルをディレクトリ内に書き込む権限を与えられていなければいけません。



カラムの区切り記号、囲み記号、行の終端記号をデフォルトのものと違う文字にしたい場合、明示的に全部指定してしまう方が簡単だと思います。
とりあえずコマンドラインツールによる方法のみですが、必要でしたらphpMyAdminによる方法も追記します。

他1件のコメントを見る
id:kohhi

いつもありがとうございます。
ご指摘の通り”\”のまま入力したら通ったように思います。

phpMyAdminでインポート・エクスポートがうまくいかず、
簡単なデータでコマンドラインから、勉強のつもりでやってます。
また、よろしくお願いします。

2012/12/03 21:07:11
id:rouge_2008

ポイントとベストアンサーありがとうございます。
コマンドラインでは無事にインポート・エクスポートできたようで良かったです。

データベース、テーブル、各カラムの照合順序とCSVファイルの文字コードは一致しているでしょうか?
一致している場合は、UTF-8でもSJISでも文字化けはしないようです。


・インポート時の設定

  • 「ファイルの文字セット」にCSVファイルの文字コードを選択
  • 「インポートするファイルの形式」で「CSV」または「LOAD DATAするCSV」のどちらかにチェック
  • 「フィールド区切り記号」に半角カンマ「,」入力
  • 「フィールド囲み記号」に半角ダブルクォーテンション「"」(※変更なし)
  • 「フィールドのエスケープ記号」に半角バックスラッシュ\(※変更なし)
  • 「行の終端記号」に「auto」(※「インポートするファイルの形式」で「CSV」を選択した場合は変更なし。「LOAD DATAするCSV」を選択した場合は、ファイルの改行コードに合わせてCRLFの場合は「\r\n」、LFの場合は「\n」を入力)
  • 「LOCALキーワードを使用する」にチェックを入れたまま(※「インポートするファイルの形式」で「LOAD DATAするCSV」を選択した場合に表示されるオプション)


「non」にチェックを入れたまま「実行する」ボタンを押します。

1行しか挿入されなかった理由ですが、最初は「フィールド囲み記号」を正しく指定していなかった(※「0」を指定した)のが原因で、その次は「LOAD DATAするCSV」を選択した際にも「行の終端記号」に「auto」を指定していたのが原因ではないかと思います。
「LOAD DATAするCSV」を選択して実行した後に発行されたSQLを確認すると分かりますが、ファイルの改行コードがLFであっても、「LINES TERMINATED BY '\r\n'」となっていて、改行コードを正しく自動認識していないのが分かります。(※この為、明示的に指定します。)

※エクスポートも同じように指定すればおそらく大丈夫です。

2012/12/03 23:50:23

その他の回答(2件)

id:oil999 No.1

oil999回答回数1728ベストアンサー獲得回数3202012/12/03 11:43:26

ポイント100pt

エクスポート

シングルクォーテーションとダブルクォーテーションは全角ではなく半角で指定して下さい。また、フォルダ区切り文字は半角スラッシュにしてみてください。

SELECT * FROM test01 INTO OUTFILE "c:/work/backup.csv" FIELDS TERMINATED BY ',';

インポート

エクスポートと同様です。

LOAD DATA INFILE "c:/work/test01.csv" INTO TABLE test01 FIELDS TERMINATED BY ',';
id:oil999

C:\のルートフォルダに書き込み権限を与えていますか?
ご確認ください。

2012/12/03 18:39:48
id:kohhi

お手数おかけしてすいません。
デフォルトのrootユーザで入ってます。

ありがとうございます。
できました。

Windowsなので、"/"を"\"に手作業で変換してコマンド叩いてました。
"/"のまま入力したら通りました。
*こちらの原因かもしれません。
(権限は、デフォルトのrootユーザで入ってます。)

僕の勉強には次のステップがありますので、
また、よろしくお願いします。

2012/12/03 21:00:38
id:zzman No.2

燕雀安知鴻鵠之志回答回数284ベストアンサー獲得回数362012/12/03 11:47:58

ポイント50pt

クォーテーションが全角になっているのがエラーになっている原因だと思われます。

SELECT * FROM test01 INTO OUTFILE “c:\work\backup.csv” FIELDS TERMINATED BY ‘,’;

以下のように半角で行ってください。

SELECT * FROM test01 INTO OUTFILE "c:\work\backup.csv" FIELDS TERMINATED BY ',';
id:kohhi

質問者から

kohhi2012/12/03 16:27:49

早速お返事をいただき、ありがとうございます。
はじめにエクスポートをやってみました。
エラーメッセージ:
ERROR 1(HY000): Can't create/write to file 'c:worackup.csv'(Errcode 22)

はてな、さんのサイトで表示すると、エンマークがバックスラッシュに変わりますが、その関係ですかね。
'c:\worackup.csv'が崩れてます。

id:rouge_2008 No.3

rouge_2008回答回数594ベストアンサー獲得回数3512012/12/03 20:29:35ここでベストアンサー

ポイント100pt

既出の回答にあるように、パスの区切り文字には半角スラッシュ「/」を使ってみてください。
あるいは円記号を使う場合、「\\」と2個続けても大丈夫です。
mysqldumpユーティリティでしたら「\」でも認識したのですが、mysqlは上手く処理できないようです。(※Linuxのパスの区切り文字はスラッシュ「/」ですので、おそらくWindows版だけの問題だと思いますが・・・)

・エクスポート

SELECT * FROM test01 INTO OUTFILE 'c:\\work\\backup001.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';


・インポート

LOAD DATA INFILE "c:\\work\\backup001.csv" INTO TABLE test01 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';


※改行コードも明示的に指定した方がいいです。(Windows環境であっても何も指定しない場合、デフォルトでLFになります。)

「LOAD DATA INFILE 構文」
http://dev.mysql.com/doc/refman/5.1/ja/load-data.html

LOAD DATA INFILE と SELECT ... INTO OUTFILE ステートメントの両方に対して、FIELDS と LINES 条項の構文は同じです。条項は両方とも任意ですが、もし両方が指定された場合 FIELDS は LINES に先行しなければいけません。

もし FIELDS 条項を指定すると、少なくてもどれか1つを指定する必要はありますが、その各サブ条項 (TERMINATED BY、[OPTIONALLY] ENCLOSED BY、そして ESCAPED BY) もまた任意になります。

もし FIELDS 条項を指定しなければ、デフォルトは、このように書き込んだ場合と同じようになります。

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'


もし FIELDS 条項を指定しなければ、デフォルトは、このように書き込んだ場合と同じようになります。

LINES TERMINATED BY '\n' STARTING BY ''


言い換えると、インプットを読み込む時、デフォルトは LOAD DATA INFILE が次のように機能するよう働きかけるという事です。

  • 改行部分のラインの境界を探してください。
  • ライン プリフィックスを飛び越えないでください。
  • タブのところでラインをフィールドに分割してください。
  • フィールドが引用文字によって囲まれていると思わないでください。
  • ‘\’ が先に来るタブ、改行、または ‘\’ を、フィールド値の一部である直定文字として解釈してください。

反対に、デフォルトはアウトプットを書き込む時に SELECT ... INTO OUTFILE が次のように機能するよう働きかけます。

  • フィールドの間にタブを書いてください。
  • 引用文字でフィールドを囲まないでください。
  • フィールド値内で起こるタブのインスタンス、改行、または ‘\’ を避ける為に ‘\’ を利用してください。
  • 行の最後に改行を書き込んでください。


バックスラッシュは、MySQL の中では文字列内の拡張文字ですので、FIELDS ESCAPED BY '\\' を書き込むには、単一バックスラッシュだと認識させる為に2つのバックスラッシュを指定しなければいけません。

注意:もしウィンドウズ システム上でテキスト ファイルを生成したら、ウィンドウズのプログラムは通常ラインのターミネータとして2つの文字を利用するので、ファイルを正確に読み込む為には LINES TERMINATED BY '\r\n' を利用しなければいけないでしょう。WordPad のようないくつかのプログラムは、ファイルを書き込む時 \r をライン ターミネータとして利用するでしょう。そのようなファイルを読み込む時は、LINES TERMINATED BY '\r' を利用してください。


「ELECT 構文」
http://dev.mysql.com/doc/refman/5.1/ja/select.html


・mysqldumpでも「--tab=出力するディレクトリのパス」を指定した場合、CSVでの出力が可能です。(SQLファイルも作成されます。)

mysqldump -u ユーザー名 -p パスワード --tab="c:\work" test --fields-terminated-by="," --fields-enclosed-by="\"" --lines-terminated-by="\r\n"


※上記の場合、「c:\work」にSQLとCSVファイルが出力されます。

「mysqldump — データベースバックアッププログラム」
http://dev.mysql.com/doc/refman/5.1/ja/mysqldump.html

  • --tab=path, -T path

タブによって分けられたデータファイルを生成します。各ダンプされたテーブルごとに、mysqldumpはテーブルを作成するCREATE TABLEステートメントを含むtbl_name.sqlファイルと、そのデータを含むtbl_name.txtファイルを作成します。オプション値はファイルを書き込むディレクトリです。

デフォルトで、.txtデータファイルはカラム値と、各行の最後で新しいラインの間にタブキャラクタを使用してフォーマットされます。このフォーマットは明示的に--fields-xxxと--lines-terminated-byオプションを使用することで特定することができます。

注:このオプションはmysqldumpがmysqldサーバと同一のマシンで作動している場合のみ使用されるべきです。FILE権限を保持しており、サーバはユーザの指定してアファイルをディレクトリ内に書き込む権限を与えられていなければいけません。



カラムの区切り記号、囲み記号、行の終端記号をデフォルトのものと違う文字にしたい場合、明示的に全部指定してしまう方が簡単だと思います。
とりあえずコマンドラインツールによる方法のみですが、必要でしたらphpMyAdminによる方法も追記します。

他1件のコメントを見る
id:kohhi

いつもありがとうございます。
ご指摘の通り”\”のまま入力したら通ったように思います。

phpMyAdminでインポート・エクスポートがうまくいかず、
簡単なデータでコマンドラインから、勉強のつもりでやってます。
また、よろしくお願いします。

2012/12/03 21:07:11
id:rouge_2008

ポイントとベストアンサーありがとうございます。
コマンドラインでは無事にインポート・エクスポートできたようで良かったです。

データベース、テーブル、各カラムの照合順序とCSVファイルの文字コードは一致しているでしょうか?
一致している場合は、UTF-8でもSJISでも文字化けはしないようです。


・インポート時の設定

  • 「ファイルの文字セット」にCSVファイルの文字コードを選択
  • 「インポートするファイルの形式」で「CSV」または「LOAD DATAするCSV」のどちらかにチェック
  • 「フィールド区切り記号」に半角カンマ「,」入力
  • 「フィールド囲み記号」に半角ダブルクォーテンション「"」(※変更なし)
  • 「フィールドのエスケープ記号」に半角バックスラッシュ\(※変更なし)
  • 「行の終端記号」に「auto」(※「インポートするファイルの形式」で「CSV」を選択した場合は変更なし。「LOAD DATAするCSV」を選択した場合は、ファイルの改行コードに合わせてCRLFの場合は「\r\n」、LFの場合は「\n」を入力)
  • 「LOCALキーワードを使用する」にチェックを入れたまま(※「インポートするファイルの形式」で「LOAD DATAするCSV」を選択した場合に表示されるオプション)


「non」にチェックを入れたまま「実行する」ボタンを押します。

1行しか挿入されなかった理由ですが、最初は「フィールド囲み記号」を正しく指定していなかった(※「0」を指定した)のが原因で、その次は「LOAD DATAするCSV」を選択した際にも「行の終端記号」に「auto」を指定していたのが原因ではないかと思います。
「LOAD DATAするCSV」を選択して実行した後に発行されたSQLを確認すると分かりますが、ファイルの改行コードがLFであっても、「LINES TERMINATED BY '\r\n'」となっていて、改行コードを正しく自動認識していないのが分かります。(※この為、明示的に指定します。)

※エクスポートも同じように指定すればおそらく大丈夫です。

2012/12/03 23:50:23

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません