８－１.MySQLにCSVデータを一括読み込みする方法［LOAD DATA］

2019年7月21日2020年1月5日

EXCELなどで作成されたデータをMySQLのテーブルに取り込みたいという事象はよくあることですね。
CSVファイルやTSVファイルをMySQLのテーブルに取り込むにはどうしたらいいでしょうか。

MySQLにはCSVやTSVを一括で取り込むことが出来るコマンドが用意されていますので使い方をご紹介します。

１．「LOAD DATA」構文

TSVデータやCSVデータを一括で取り込むには「LOAD DATA INFILE」構文を使用します。詳しくは本家のマニュアルを参照して頂くといいと思います。

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

１）構文（簡易版）

通常よく使う構文は以下となります。（分かり易くするために文毎に改行していますが続けて記述して大丈夫です）

LOAD DATA [LOCAL] INFILE　 '「ファイル名」’
INTO TABLE 「テーブル名」
FIELDS TERMINATED BY '引用符’
LINES TERMINATED BY '改行コード’;

2)構文説明

上記の構文を説明します。

１行目の「LOAD DATA LOCAL INFILE」句で指定している「ファイル名」はテーブルに読み込むファイル名を記述します。
ローカルにファイルがある場合は「LOCAL」句を指定します。

ロードするファイル名には日本語は控えた方が良いです。半角英数のみで記述しましょう。

また、ファイル名をディレクトリとあわせて指定する場合ですが、Windowsの場合はディレクトリ記号の「￥」は「/」で指定してください。
（「c:\data\test.csv」の場合は「\c:/data/test.csv」と記述します）

２行目の「INTO　TABLE」句では「テーブル名」で取り込み先のテーブルを指定します。

３行目の「TERMINATED　BY」句で指定するのは文字列の引用符記号の指定です。
例えばデータが「”ABC"」のように「”」（ダブルクォーテーション）で挟んでいる場合は「TERMINATED BY '"'」と指定します。

４行目の「LINES TERMINATED BY」句で指定しているのは一行の終わりの改行コードを指定します。改行コードは「CR」を「\r」「LF」を「\n」で表します。
改行コードはOSによって違ってきます。
Windowsは「CR」「LF」なので「\r\n」
LinuxなどUNIX系は「LF」なので「\n」
Macは「CR」なので「\r」
となります。
読み込むデータをバイナリエディタで確認すると間違いがないでしょう。

スポンサードサーチ

２．読み込み実行例

ではCSVデータをテーブルに一括で読み込みを行う方法を実例で説明していきましょう。

１）実行サンプルの説明

①テーブル

データを読み込むのは以下のテーブルです。

mysql> desc table01;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| keycode  | char(10)    | YES  |     | NULL    |       |
| dataname | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.10 sec)

②読み込むCSVデータ

読み込むファイルはフルパスで「C:\data\data01.csv」です。

"A01","cat"
"B01","dog"
"C01","bird"
"d01","cow"
"e01","fox"
"f01","bear"

２）読み込みを行うLOAD文

ディレクトリの頭につける記号は「￥」ではなく「/」のほうが相性がいいようです。

LOAD DATA LOCAL INFILE 'C:\data\data01.csv' INTO TABLE table01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

３）実行

以下は実行結果とロード後の検証のためテーブル内容を表示させた結果です。

mysql> LOAD DATA LOCAL INFILE 'C:/data/data01.csv' INTO TABLE table01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Query OK, 6 rows affected (0.18 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from table01;
+---------+----------+
| keycode | dataname |
+---------+----------+
| A01     | cat      |
| B01     | dog      |
| C01     | bird     |
| d01     | cow      |
| e01     | fox      |
| f01     | bear     |
+---------+----------+
6 rows in set (0.02 sec)

mysql>

スポンサードサーチ

３．MySQL８．０で実行したときにエラーが出た場合

１）エラーについて　　ERROR 1148 (42000)

MySQL５．６では問題なく正常の動作した処理だったのにMySQL８にバージョンを上げたらエラーが出てしまい困っているのではないでしょうか。

mysql> LOAD DATA LOCAL INFILE 'C:\data\data01.csv' INTO TABLE table01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
ERROR 1148 (42000): The used command is not allowed with this MySQL version

これは8.0からのセキュリティ強化による初期値の変更が原因です。MySQサーバーとクライアント双方で「local_infile」の値を「１」に設定することで処理が出来るようになります。

２）設定方法

①サーバー

mysql> SET PERSIST local_infile= 1;　　　　　←local_infileを「１」に設定
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@local_infile;　　　　　　　　←設定されたことを確認
+----------------+
| @@local_infile |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

 

②クライアント

mysql起動時にパラメータを指定します。

c:\data>mysql -uroot -p --local_infile=1　　　　　←mysql起動時にパラメータを指定します
Enter password: *************

これで正常に処理できます。

 

 

---

---