MySQLのLOAD DATA INFILEで大はまりした話

| 1件のフィードバック

MySQLにCSVファイルをインポートする際にはLOAD DATA INFILE構文を使います。
とあるシステムのテーブルにCSVファイルをインポートしたところ、なぜかシステムが正常に動作しなくなり大はまりしました。今回はそんなお話です。

例えば以下のDDLでテーブルを作成します。

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT 'ユーザー名',
  `email` varchar(1024) NOT NULL COMMENT 'Email',
  PRIMARY KEY (`id`)
)

このuserテーブルに対して、以下のようなデータをインポートするとします。

1,山田 太郎,yamada@example.com
2,田中 花子,tanaka@example.com
3,鈴木 浩志,suzuki@example.com

この場合、コマンドは以下のように実行します。

LOAD DATA LOCAL INFILE 'C:/Data/user.csv' INTO TABLE user FIELDS TERMINATED BY ',';

さて、コマンドを実行したらテーブルにデータが正常にインポートされているか確認してみます。

SELECT * FROM user;

blog_20240723_01

ちゃんとデータが入っていますね。

ここまでは何の問題もないように思われます。ところが・・・

見えない落とし穴

ではuserテーブルからメールアドレスによりユーザーの検索を行うとどうなるでしょうか。

SELECT * FROM user WHERE email='tanaka@example.com';

上記のようなクエリを実行してみると・・・

blog_20240723_02

あれ? 結果が帰ってこない・・・

再度userテーブルのデータを確認してみてもちゃんとデータは入っているように見えます。

???

何か検索の仕方が悪かったかなとLIKE句による前方一致検索をやってみる。

SELECT * FROM user WHERE email LIKE 'tanaka%';

blog_20240723_03

これは正しい結果が得られる・・・と。
続いて同じくLIKE句による後方一致検索ならどうだっ!

SELECT * FROM user WHERE email LIKE '%example.com';

blog_20240723_04

ありゃ???
これだと検索にヒットしません。

???????

なんでだろう・・・きっと何か見落としがあるのだろうと思い、再度MySQLのマニュアルを熟読してみました。
するとこんな記述がありました。

Note
If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY 'rn' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use r as a line terminator when writing files. To read such files, use LINES TERMINATED BY 'r'.

超意訳。

テキストファイルをWindowsシステム上で作成した場合、ファイルを正しく読み込むためにはLINES TERMINATED BY 'rn'を指定してください。Windowsのプログラムは通常2文字の改行コード(CR+LF)を使用します。いくつかのプログラム(例えばWordPadなど)についてはCR(r)が使用されますので、その場合はLINES TERMINATED BY 'r'を指定してください。

これだ・・・
今回インポートしたデータはExcelを利用して生成したものだったので改行コードはCR+LFです。
ところがLOAD DATA LNFILEのデフォルトでは改行コードをLFとして扱うため、インポートした各行の最後のカラムの値の末尾にCRコード(r)が付加された状態でインポートが行われていたのです。

というわけで検証のためにuserテーブルに対して以下のクエリを実行してみます。

SELECT * FROM user WHERE email='tanaka@example.com\r';

するとちゃんと該当のレコードがヒットしました。

blog_20240723_05

MySQLのクライアントでは改行コードは目視できず、見えない文字が検索対象値に含まれていたのです。
ということで正しくデータインポートを行うためには以下のようコマンドを実行します。

LOAD DATA LOCAL INFILE 'C:/Data/user.csv' INTO TABLE user FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

改めて、以下のクエリを実行。

SELECT * FROM user WHERE email='tanaka@example.com';

今度は正しい結果が得られました。

blog_20240723_06

結論

Windowsシステムで作成されたCSVファイルをインポートする際はLINES条項を正しく指定しましょう。
・・・当たり前か orz

MySQLのLOAD DATA INFILEで大はまりした話」への1件のフィードバック

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>