質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.90%

mysqlのテーブルをcsvで移行時に、ほとんどのレコードがインポートされない

受付中

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 3,056

score 53

mysqldumpして取得したテーブルのcsvを別のmysqlにLOAD DATA INFILEでインポートしたのですが、90万行ぐらいあるレコードのうち、122行しか読まれませんでした。(csvにはきちんと読み込まれているのを確認してあります。)

叩いたコマンドは以下のとおりです。

LOAD DATA LOCAL INFILE '/vagrant/data1.csv' into table SampleTable FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\';

この原因はどのようなことが考えられるでしょうか。なお、このcsvは別のLinux機からとったもので、windowsマシン上で作られたものではないので、

LINES
TERMINATED BY '\r\n'

の話とは関係ないと思われます。(実際このキーワードを付けると、インポートは1件のみしかできませんでした)

あと、selectを叩くと、日本語の部分が文字化けしていたため、

show variables like "chara%";

を実行すると、案の定latin1の項目があったため、my.cnfを書き換えて、現在はlatin1の項目がないことを確認してあります。日本語が???となってしまうのですが、これはどのように対処すべきでしょうか。

追記1:なお、mysqld.logが吐かれるようにmy.cnfを書き換えたのですが、特にLOAD FILEに関するエラー等は出ておりませんでした。

追記2:用いたcsvの文字コードはutf8でした。

[vagrant@localhost vagrant]$ nkf -g data1.csv
UTF-8 (LF)

追記3:mysqlのバージョンは5.6.20です。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • km668

    2016/08/22 06:34

    文字コードも含めてエクスポートするSQLとインポートするSQLは以下のように
    整合していないとトラブルにみまわれます。
    整合させるには明示的に指定させるのが好ましいと思います。
    Msqlの移行でmysqldumpを使って私もさんざん痛い目にあったことがあります。(テーブル定義やプロシジャーはmysqldumpでしか取り出せませんが、テーブルデータはSQLで行った方が確実です。)
    SELECT * FROM スキーマ名.テーブル名
    INTO OUTFILE '/フォルダ名/ファイル名'
    CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
    TRUNCATE TABLE スキーマ名.テーブル名;
    LOAD DATA INFILE '/フォルダ名/ファイル名'
    INTO TABLE スキーマ名.テーブル名 CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';


    キャンセル

  • Panzer_vor

    2016/08/22 08:28

    CSVエクスポート時の文字コードと出力形式とインポート時の文字コード取込形式は一致してますか?

    また文字化けというのは、
    取込結果が化けているという認識で問題ないでしょうか?

    キャンセル

  • A.Ichi

    2016/08/22 11:14

    csvから取り込まれた122行が先頭からの連続であれば123行の文字コードやエスケープ、NULL等を疑ってみては?

    キャンセル

回答 2

+1

A.Ichiさんも仰っていますが、

  • 122行だけなら正常にインポートできるのか
  • 123行にするとエラーが出るのか

と現象発生の前後で分けて検証されるとよろしいかと思います。
122行目か123行目に処理できない記号やNULL、改行などが入っている可能性が考えられます。

文字化けについてはMySQLの設定以外にもテーブルやフィールドの文字コードも疑ってみて下さい。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+1

CSVもUTF-8。
LOAD時もUTF-8として登録。
DBもUTF-8に変えた(再起動済?)。

とすれば、次は、TABLEがUTF8かどうかの確認ですね。
TABLEをDROP CREATEしていないければ、latin1のままになっているはず。
show table status from [table名];
もしくは
show create table [table名];
で確認できます。

あとは、他の方が言うように、
登録されている122件のデータの状態を確認が必要です。
122件目も正常に登録されているか。(途中で切れていないか)
123件目に変なデータ(改行や型違い・桁オーバー)があるか。

そもそも、登録するテーブルに元々データが入っていて、主キーが被っていないか。
etc...

上記でも解決できなかった場合、
テーブル定義と
切れている部分のCSVの中身と
登録されている中身を記載して頂ければ、
より明確な答えを得られるかもしれません。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.90%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る