スポンサーリンク

8-1.MySQLにCSVデータを一括読み込みする方法[LOAD DATA]

スポンサーリンク

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

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

スポンサーリンク

1.「LOAD DATA」構文

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

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

1)構文(簡易版)

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

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

2)構文説明

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

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

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

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

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

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

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

スポンサードサーチ

2.読み込み実行例

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

1)実行サンプルの説明

①テーブル

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

②読み込むCSVデータ

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

2)読み込みを行うLOAD文

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

3)実行

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

スポンサードサーチ

3.MySQL8.0で実行したときにエラーが出た場合

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

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

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

2)設定方法

①サーバー

 

②クライアント

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

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

 

 

ブログランキング・にほんブログ村へにほんブログ村