今はもう機会が少ないかもしれませんが、手元の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 を指定する
- 1行目がヘッダーになっている→(@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のバージョンによっては、セキュリティの関係で必要になります。
参考
なぜ--local-infileオプションが必要になるの?
LOAD DATA LOCALコマンドは、公式ドキュメントにあるように、潜在的にセキュリティリスクを含んでいます。
(何も制限なく黙示的に)LOAD DATA LOCALでローカルファイルを読み込めるようになると、悪意のあるサーバーに対して自由にローカルファイルへのアクセスを許すことになってしまいます。
そこで、MySQLクライアントのインストール時には、デフォルトでLOAD DATA LOCALを無効化し、明示的に--local-infile=1オプションがあるときだけ有効化するようにしています。
* 正規化すると"party"カラムは別テーブルに分けるべきですが、そこは気にしないことにします。