load dataするときに、最後の列(文字列)がちゃんと入らない

質問

load data infile～で一括登録する上で、
最後の列に、空白またはnull指定しても、
ちゃんと登録されてくれません。

create table ～(
id char(12) not null primary key,
1R varchar(12),
2R varchar(12),
3R varchar(12));

実際に一括登録するデータは以下です(スペースはタブ区切り)
2R・3Rに対して、このように実験的に入れてみました。
1 aa1 \N \N\n　>Nullを指定
2 bb1 \n　　 >タブ区切りの空白指定
3 cc1\n　　　　>タブ自体をを書かない

登録結果は以下のようになりました。
1 aa1 NULL N
2 bb1
3 cc1 NULL NULL

id1の行については、2RにはNULL値が登録できたのですが、
3Rには「N」と表示されています。
しかし、ただのNではないみたいで、
(where 3R like 'N_')としないとヒットしません。
また、'_'は空白ではないらしく正体不明です。

id2の行については、2Rは空白になっていますが、
3Rは（where 3R = ''）としてもヒットせず、
なぜか(where 3R like '_')にヒットします。
これもまた、'_'は空白ではありません。

冒頭にも書きましたが、このように
最後の列にうまく空白やNullが登録されません。

id3の行のように、
値のある列までで閉じてあげると綺麗に入ります。

力ずくでid3のように成形してあげることもできますが、
一括登録という便利な機能を使う上で何か違う気がします。

環境　windowsXP+MySQL5.0
文字数制限のためあまり詳しく掛けませんでした。不足な点があれば補足致しますので、
よろしくおねがいします。

回答No.3

ma-chan45 · ベストアンサー

実際にやってみました。
同じテーブルを作成し、同じテストデータを作成して実験してみました。

結果、私のPCでは
+----+------+------+------+
| id | 1R | 2R | 3R |
+----+------+------+------+
| 1 | aa1 | NULL | N
|
| 2 | bb1 |
| NULL |
| 3 | cc1
| NULL | NULL |
+----+------+------+------+
3 rows in set

という風に3行表示され、変なところで改行(？)されてました。(なりませんでした？)
で、いろいろやってみたんですけど、
テストデータで改行文字に「\n」を使っていたり
改行文字の後に改行することで自動的に「\r\n」が付加されたために
このような現象が起こったのかなと思いました。

たとえば、テストデータを
「1 aa1 \N \Nxyz2 bb1 xyz3 cc1xyz」
と、1行で書いたり(このとき「xyz」は適当な改行文字とします。)、
「1 aa1 \N \N
2 bb1
3 cc1」
と「\n」を書かないで作成し、
> load data local infile "testdata" into table TABLE
> fields terminated by "\t"
> lines terminated by "xyz";
または、
> load data local infile "testdata" into table TABLE
> fields terminated by "\t"
> lines terminated by "\r\n";
としてみたらどうでしょうか？

それともこれだともともとの質問の趣旨からはずれてしまうでしょうか？
これだと
+----+------+------+------+
| id | 1R | 2R | 3R |
+----+------+------+------+
| 1 | aa1 | NULL | NULL |
+----+------+------+------+
| 2 | bb1 | | NULL |
+----+------+------+------+
| 3 | cc1 | NULL | NULL |
+----+------+------+------+
と、きれいに表示されましたので、まだましになったかなと思ったんですけど・・・・

2行目3列目のタブについては・・・・
「bb1」のあとにタブを入力したために、mysqlのほうで3列目のフィールドがある、というふうに勘違いして
スペースを作成したために、NULLではないものがはいったのかな～～？って考えてます。

どうでしょう？？

お礼

後者の例で試してみたところ、綺麗にインポートできました!!

＞という風に3行表示され、変なところで改行(？)され＞てました。(なりませんでした？)

変な改行についても頭を悩ませていましたが、別問題だと思っていたのでこちらのほうには書いてませんでしたが、一緒に解決することができてうれしいです。

まだまだ初心者なもので、問題が発生するたびに混乱して、問題点の切り分けがうまくできてませんでした。
また質問させていただくことがあるかもしれませんがよろしくお願いします。_(._.)_

回答No.2

ma-chan45

ごめんなさい。
また確認させてください。

一括登録するデータの「\n」「\N」って
ファイル内に目に見えるように記述されているんですよね？
つまり、1行目なら
1 aa1 \N \N\n
っていう文字列がテキストファイル内にそのまま記述されているんですよね？

改行の「\n」は、質問でわかりやすくするために
あえて記述した、ということはありませんよね？

補足

ファイル内に、\nと書いております。

その理由として・・・
列が(id,1R,2R,3R)と4列あるのですが、
id3の行のように2列分しか値を持たせていない行のために、明示的に\nと書きました。

再度、よろしくおねがいします。

回答No.1

ma-chan45

こんにちは。

実行したコマンド(load data infileの後の部分も含めて)
教えてもらえますか？

補足

load data local infile "c:/ファイル名.txt" into table TABLE名
fields terminated by '\t'
lines terminated by '\n';

です。よろしくおねがいします。