MySQLで完全一致検索ができない

使用するSQLをPostgreSQLからMySQLに変更してみたところ、LOAD DATA INFILEでハマった。
LOAD DATA INFILEでcsvデータを読み込み、読み込んだデータに文字化けがないことも確認しているのに、なぜか完全一致検索はできないがLIKE検索ならヒットするという現象に陥った。

原因は、LOAD DATA INFILEでwindowsの改行コードを指定していなかったこと。
Windowsで作ったデータはWarningもでず、問題なく読込むことができるので気づきにくい。

[仕様]

• Mac OS X ver.10.6.8
• 5.1.62 MySQL Community Server

[プロセス]
Windows EXCELで入力したデータをcsvで保存。
macのMySQL Workbenchにてcsvデータを読み込み。

LOAD DATA LOCAL INFILE "/Users/***/test.csv"
INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES;

入力データの確認。

SELECT column_1 FROM table_name; #OK

しかし、完全一致検索ができない。

SELECT column_3 FROM table_name WHERE column_2 = 'YOU'; #何もヒットしない

LIKE検索だとヒットする。

SELECT column_3 FROM table_name WHERE column_2 LIKE '%YOU%'; #希望の結果

[原因]
文字コードや改行コードに原因があると思い、csvデータをエディタmiで開いて確認。

• テキストエンコーディング: UTF-8
• 改行コード: CR+LF (Windows)

改行コードがあやしい？
念のためターミナルでod -c [csv_file]で改行コードを確認。
やっぱり「\r  \n」になっている。

MySQLのリファレンスを確認。
http://dev.mysql.com/doc/refman/5.1/ja/load-data.html

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

デフォルトが「\n」だったため、各レコードの最後に目に見えない「\r」が付いており、完全一致検索ができてなかった。

[解決]
MySQLのLOAD DATA INFILEの改行コードはデフォルトで「\n」なので、windowsのエディタで作ったデータを読み込む際にはLINES TERMINATED BYを指定する。

LOAD DATA LOCAL INFILE "/Users/***/test.csv"
INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES ;

[追記]
Macで作成したcsvファイルの場合。

LOAD DATA LOCAL INFILE "/Users/***/test.csv"
INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r' IGNORE 1 LINES ;

Share on Facebook Share on Twitter Share on Google Plus

About Piyoko