MySQLテーブルにファイルインポートがうまく出来ないときに確認したいこと

今はもう機会が少ないかもしれませんが、手元のCSVファイルを一括で読み込んで、MySQLにインポート（INSERT）したいときがあります。

ファイルインポート自体は、MySQLに付属しているコマンドでちょちょっとするだけでできるのでとても便利です。
ただ、ちょっと癖がある部分と注意が必要なところがあるので、メモしておきます。

CSVファイルでレコードを登録する基本

前提条件

バージョン

この記事の内容はバージョンによって挙動が大きく変わるので、環境によっては違う感じになるかもしれません。

クライアント

$ mysql --version
mysql Ver 15.1 Distrib 10.1.45-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

サーバー

mysql> select version();
+------------+
| version() |
+------------+
| 5.7.29-log |
+------------+

CREATE文

例として、こんな感じのシンプルなテーブルを作りました。

CREATE TABLE `presidents` (
  `id` bigint(20) NOT NULL,
  `name` varchar(45) NOT NULL,
  `party` varchar(45) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

読み込みたいCSV

上のテーブルにインポートしたいデータです。

id,name,party
1,George Washington,Unaffiliated
2,John Adams,Federalist
3,Thomas Jefferson,Democratic-Republican
4,James Madison,Democratic-Republican
5,James Monroe,Democratic-Republican

CSVの内容を登録する方法（基本）

使い方とオプションは、公式マニュアルを読むと大体分かります。

LOAD DATAコマンドを使います。

上記のようなテーブルに
ローカルのCSVファイル "~/presidents.csv" をインポートしたいときは、下記のように実行します。

mysql> load data local infile "~/presidents.csv"
into table crawling.presidents
fields terminated by ','
lines terminated by '\n'
(@id,@name,@party)
SET
id = @id,
name = @name,
party = @party,
created_at = NOW(),
updated_at = NOW();

こちらは

• リモートのサーバーにインポートしたい→local を指定する
• １行目がヘッダーになっている→(@id,@name,@party)のように指定
• カンマ区切り→fields terminated byで指定
• Linuxスタイルの改行文字→lines terminated byで指定

の場合のインポートコマンドになります。

エラーで登録できないとき

ERROR 13 (HY000): Can't get stat of /path/file.csv (Errcode: 2)

もしローカルにあるMySQLサーバーではなく、
リモートのサーバーにローカルのファイルをインポートしたい場合に起こりえます。

LOAD DATA LOCAL INFILE

の LOCAL を忘れていませんか？

LOCAL修飾子を付けると、
「ローカルで読み込んで、リモートのMySQLに送って登録してもらうよ」という意味になります。
サーバーが直接（サーバーホスト上の）ファイルを読み込むときには不要です。
ですが、クライアント側がリモートにあるときは、LOCAL修飾子を付けないとERRORになるかもしれません。

LOCAL修飾子をつけないと、そこにファイルがあるはずなのに読めないという事態に陥るので覚えておきたいですね。

公式ドキュメントが参考になります。

ERROR 1148 (42000): The used command is not allowed with this MySQL version

ログイン時に --local-infile オプションをつけましょう。

$ mysql -u USERNAME -p --local-infile

MySQLのバージョンによっては、セキュリティの関係で必要になります。

* 正規化すると"party"カラムは別テーブルに分けるべきですが、そこは気にしないことにします。