こんにちは。
データベースの学習に入り、はや2週間が経とうとしています。
最近のハイライトはサンプルデータを使ってあれやこれやMySQLの練習をしようと思ったら、テーブル設計にミスがあるのが発覚し、一から作り直す羽目になったことです。どうもjunpei314です。
はじめに
データベース設計の勉強をしている際に、テーブルを作成できたはいいものの、大量のサンプルデータを格納するにはどうすればいいのだろうと思い色々と調べてみました。
今回は、大量のデータをテーブルに格納する方法をまとめてみます。
実行環境
MacBook Air (M1)
MySQL8.0
サンプルデータ
以下のサンプルデータを使用する
prefectures
| id | name |
|---|---|
| 1 | 北海道 |
| 2 | 青森県 |
| 3 | 岩手県 |
| -- | ----- |
| 46 | 鹿児島県 |
| 47 | 沖縄県 |
1.一括で格納する(Bulk Insert)
下記の様に記述することで省略してデータを格納できる
INSERT INTO table_name (id, name)
VALUES
(1, '北海道'),
(2, '青森県'),
(3, '岩手県'),
-------------
(46, '鹿児島県'),
(47, '沖縄県');
2.テキストファイルを直接テーブルにロードする
2-1. LOAD DATAを使う
LOAD DATA LOCAL INFILE 'ファイルパス'
INTO TABLE prefectures
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name);
COLUMNS TERMINATED BY ','
入力ファイル内の各カラム（列）がどの文字で区切られているかを指定する
LINES TERMINATED BY '\n'
入力ファイル内の各行がどの文字で区切られているかを指定する
下記のエラーが出ることがある
mysqlimport: Error: 3948,
Loading local data is disabled;
this must be enabled on both the client and server sides,
when using table: programs
MySQLサーバがローカルデータのロードを許可していないために発生している模様
以下のコードで設定ファイルのステータスを確認する
SELECT @@local_infile;
データロードの設定がオフの場合は以下の様に出力される
+----------------+
| @@local_infile |
+----------------+
| 0 |
+----------------+
1 row in set (0.00 sec)
以下のコードを実行しデータをロードできるようにする
SET GLOBAL local_infile=on;
+----------------+
| @@local_infile |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
再度、LOAD DATA LOCAL INFILE を実行する
2-2. mysqlimportを使う
mysqlimport -u ユーザー名 -p --local データベース名 ファイル名
注意点
- mysqlimportは指定したファイル名からテーブル名を推測するため、ファイル名はテーブル名と同じものにする必要がある
- prefecturesテーブルにテキストファイル内のデータを格納する場合は、あらかじめprefectures.tsvなどの様にしておく
- デフォルトではタブで列を、改行で行を区切るため、特にオプションで指定しない場合はtsvファイルにする必要がある
- カンマで列を区切る場合は
--fields-terminated-by=','を追記する
- カンマで列を区切る場合は
- ファイル名を複数設定することで一度に複数のファイルをロードすることができる
まとめ
今回は効率的にデータをテーブルに格納する方法をまとめました。
SQLスクリプト内でデータのロードを行いたい場合や、他のSQLクエリと組み合わせてデータのロードを行いたい場合は、LOAD DATA LOCAL INFILEを使用してみてください。
また、シェルスクリプトからデータのロードを行いたい場合、または複数のファイルを一度にインポートしたい場合は、mysqlimportを使用してみてください。
参考URL
[MySQL] テーブルにファイルをインポートする – mysqlimport編
MySQL 8.0 リファレンスマニュアル LOAD DATA ステートメント