MySQLへCSVなどのテキスト形式のデータファイルを一括でインポートします。
今回はLinuxなどのコマンドとして提供されているmysqlimportを利用します。通常MySQLをインストールした際に自動的に付いてきますのですぐに実行することが可能です。
$ mysqlimport -u username -p --local dbname tablename.tsv
シェルスクリプト内で実行する場合はこちらが手軽ですね。また大抵の場合、INSERT文を大量に発行するよりも高速にデータを入れることができます。
環境準備
テスト用テーブル
ここではサンプルとして書籍の情報を扱うDBとテーブルを作成し、データをインポートします。
/* DB作成 */
CREATE DATABASE book;
USE book;
/* テーブル作成 */
CREATE TABLE bookshelf( -- 書棚テーブル
id int,
title varchar(128),
price int,
PRIMARY KEY(id)
);
サンプルデータ
インポートするためのテキストファイルをタブ区切り形式で用意します。文字コードはUTF-8、改行コードはLFにしてあります（Windows上で動作しているMySQLへインポートする場合はCRLF）。またmy.cnfなどで文字コードの設定を変更している場合はそれに合わせます。1行目に各カラムのタイトルは付けません。
今回は1月28日時点のAmazonのコミック売上げランキングから抜き出してきました。どうでも良いですが0円の書籍はKindleのお試し版みたいですねｗ typoではありませんｗ
$ cat bookshelf.tsv
1 鬼滅の刃 19 484
2 戦争は女の顔をしていない1 990
3 若林くんが寝かせてくれない1 0
4 ゆるキャン△１ 0
5 とんでもスキルで異世界放浪メシ5 614
6 異世界迷宮でハーレムを5 614
7 ダーウィンズゲーム１ 0
8 幼女戦記17 614
9 若林くんが寝かせてくれない2 0
10 ＮＥＷ ＧＡＭＥ！10 792
注意点として、mysqlimportコマンドを利用する場合はテーブル名とインポートするファイル名を同じ名前にする必要があります。拡張子は自由に設定することができます。
インポートする
実行
mysqlimportコマンドでインポートする場合は以下のように入力します。
$ mysqlimport -u foo -p --local book bookshelf.tsv
Enter password:
book.bookshelf: Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
各オプションは以下の通り。
-uの後にユーザー名を指定-pを指定するとEnterを押した直後にパスワードを聞かれます。- パスワードをオプションに続けて直接書くと
.bash_historyなどのログに記録されてしまうので止めた方が良いです。
- パスワードをオプションに続けて直接書くと
--localは所定のディレクトリ以外の場所のファイルをインポートする場合に必要です。詳細は後述します。- データベース名(book)を指定
- 最後にインポートするファイルのパス
インポートの実行にはFILE権限が必要です。
確認
MySQLへログインして正常にデータが入っているか確認しておきます。
$ mysql -u foo -p
Enter password:
mysql> use book;
mysql> select * from bookshelf;
+----+------------------------------------------------+-------+
| id | title | price |
+----+------------------------------------------------+-------+
| 1 | 鬼滅の刃 19 | 484 |
| 2 | 戦争は女の顔をしていない1 | 990 |
| 3 | 若林くんが寝かせてくれない1 | 0 |
| 4 | ゆるキャン△１ | 0 |
| 5 | とんでもスキルで異世界放浪メシ5 | 614 |
| 6 | 異世界迷宮でハーレムを5 | 614 |
| 7 | ダーウィンズゲーム１ | 0 |
| 8 | 幼女戦記17 | 614 |
| 9 | 若林くんが寝かせてくれない2 | 0 |
| 10 | ＮＥＷ ＧＡＭＥ！10 | 792 |
+----+------------------------------------------------+-------+
10 rows in set (0.00 sec)
大丈夫そうですね。
CSVをインポート
カラムをカンマ(,)で区切ったファイル形式の方のCSVをインポートします。
サンプルデータ
先ほどのファイルをカンマ区切りに変換しました。また文字列のカラムはダブルコーテーションで囲うことにします。
$ cat bookshelf.csv
1,"鬼滅の刃 19",484
2,"戦争は女の顔をしていない1",990
3,"若林くんが寝かせてくれない1",0
4,"ゆるキャン△１",0
5,"とんでもスキルで異世界放浪メシ5",614
6,"異世界迷宮でハーレムを5",614
7,"ダーウィンズゲーム１",0
8,"幼女戦記17",614
9,"若林くんが寝かせてくれない2",0
10,"ＮＥＷ ＧＡＭＥ！10",792
実行
このCSVファイルをインポートするにはmysqlimportにオプションを2つ追加するだけです。
$ mysqlimport -u foo -p --local --fields-terminated-by="," --fields-enclosed-by='"' book bookshelf.csv
追加されたオプションは以下の通り。
--fields-terminated-byで区切り文字を指定（デフォルトはタブ）--fields-enclosed-byは文字列などを囲う記号を指定（デフォルトは未指定）
タブ区切りもCSV？
余談ですが最近のCSVはタブや半角スペースで区切った物もCSV(Character-Separated Values)と呼ぶので、最初に取り上げたタブ区切りテキストもCSVではあるのですが、それはまた別の話ｗ
類似したフォーマットとして、タブで区切られた tab-separated values (TSV)や、欧文間隔 (いわゆる半角スペース) で区切られた space-separated values (SSV) などがあり、これらをまとめて character-separated values (CSV)、delimiter-separated values (DSV) とも呼ばれることも多い。
Comma-Separated Values – Wikipedia
オプション
ヘッダ行をスキップ –ignore-lines
1行目にカラム名がある場合、このままだとカラム名までもデータとして取り込まれるか、テーブルのデータ型と合わない場合は弾かれてしまいます。
$ cat bookshelf.tsv
ID タイトル 価格
1 鬼滅の刃 19 484
2 戦争は女の顔をしていない1 990
3 若林くんが寝かせてくれない1 0
--ignore-linesオプションでカラム名が書かれたヘッダ行をスキップすることができます。
$ mysqlimport -u foo -p --local --ignore-lines=1 book bookshelf.tsv
正確には--ignore-lines=はファイルの先頭から指定した行数をスキップするオプションですので、ファイルの途中からインポートするといったことにも応用できます。
エスケープ
カラムの中に区切り文字など処理場まずい文字が登場する場合にはバックスラッシュ(円記号)でエスケープしてやります。
$ cat bookshelf.csv
1,鬼滅の刃\,19,484
$ mysqlimport -u foo -p --local --fields-terminated-by="," book bookshelf.csv
この場合はダブルコーテーションなどで囲い--fields-enclosed-byオプションを指定するやり方でも解決できます。
$ cat bookshelf.csv
1,"鬼滅の刃,19",484
$ mysqlimport -u foo -p --local --fields-terminated-by="," --fields-enclosed-by='"' book bookshelf.csv
ホスト/ポートを指定 -h, –port
デフォルトだとlocalhostの3306番のポートへ接続しますが、-hまたは--hostオプションでホストを、--portオプションでポートを指定することができます。
$ mysqlimport -u foo -p --local -h db1.example.com --port 13306 book bookshelf.csv
サービスの規模が大きくなってくるとDBサーバは別に建てることが多いですし、手元にあるデータを遠隔地にあるサーバへ直接インポートしたい時にも重宝します。
注意点としては、事前にMySQLへログイン元のホストでユーザーを新規作成しておく必要があります。MySQLはログイン元のホストが異なると、ユーザー名が同じでも全く別のユーザーとして扱われます。ユーザー作成後にGRANTもお忘れなく。
AUTO INCREMENTの扱い –columns
特定のカラムを自動採番したい場合、データファイル上はその列を空にしておきます。
CREATE TABLE bookshelf(
id int AUTO INCREMENT, -- NEW!
title varchar(128),
price int,
PRIMARY KEY(id)
);
idがあった箇所を消しておくだけです。
$ cat bookshelf.tsv
鬼滅の刃 19 484
戦争は女の顔をしていない1 990
若林くんが寝かせてくれない1 0
(中略)
もしカラムを自体が無かったことにしてしまうと、以下のように何だかよくわからないことになってしまいます。
$ cat bookshelf.tsv
鬼滅の刃 19 484
戦争は女の顔をしていない1 990
(中略)
$ mysqlimport -u foo -p --local book bookshelf.tsv
Enter password:
book.bookshelf: Records: 10 Deleted: 0 Skipped: 0 Warnings: 20
$ mysql -u foo -p
mysql> use book;
mysql> select * from bookshelf;
+----+-------+-------+
| id | title | price |
+----+-------+-------+
| 1 | 484 | NULL |
| 2 | 990 | NULL |
(中略)
| 9 | 0 | NULL |
| 10 | 792 | NULL |
+----+-------+-------+
もしどうしてもデータファイル上からカラムを取り払ってしまいたい場合は、--columnsで指定することで回避できます。
$ cat bookshelf.tsv
鬼滅の刃 19 484
戦争は女の顔をしていない1 990
(中略)
$ mysqlimport -u foo -p --local --columns=title,price book bookshelf.tsv
Enter password:
book.bookshelf: Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
テーブルを空にしてからインポート –delete
--deleteオプションを指定すると、テーブルのデータを削除してからインポートしてくれます。
$ mysqlimport -u foo -p --local --delete book bookshelf.csv
これはTRUNCATE bookshelfではなく、DELETE FROM bookshelfのようでAUTO INCREMENTの値はリセットされません。
全テーブルをロックしてからインポート
インポートする際に
--lock-tables
トラブルシューティング
–localオプションを付けていない
MySQL5.7.16以降から特定のディレクトリ以外にあるファイルをインポートしようとすると、以下のようなエラーとなります。
mysqlimport: Error: 1290, The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, when using table: bookshelf
MySQLのシステム変数secure_file_privで指定されているディレクトリ以外にあるファイルを気軽にインポート出来なくする制約のようです。Linux環境の多くは以下の通り/var/lib/mysql-files/がデフォルトで割り当てられています。
mysql> SHOW GLOBAL VARIABLES LIKE 'secure_file_priv';
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)
解決策としては--localオプションを付けて実行するか、secure_file_privで指定されているディレクトリにデータファイルを置く必要があります。
Warningsの具体的な内容を確認したい
どうもmysqlimportを利用した際のWarningsの内容はどこにも出力されないようです。もしWarningsの値も確認したい場合はLOAD DATA INFILE構文を利用する必要があります。
設定も確認したんですけどね。どなたかご存知でしたら教えてくださいませ。
mysql> SHOW GLOBAL VARIABLES LIKE 'log_warnings';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings | 2 |
+---------------+-------+
mysql> SHOW GLOBAL VARIABLES LIKE 'log_error';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| log_error | /var/log/mysqld.log |
+---------------+---------------------+
参考ページ
- 4.5.5 mysqlimport — データインポートプログラム
- 5.1.5 システム変数の使用
- 5.1.6 Server Command Options
- 13.2.6 LOAD DATA INFILE 構文
このブログを応援する
お寄せいただいたお気持ちは全額サーバ代や次の記事を執筆するための原資として活用させていただいております。この記事が参考になった場合などぜひご検討ください。
