はじめに
MySQLにcsvをインポートする際に、MySQL WorkBenchのインポートウィザードやInsertやBulk InsertではなくLOAD DATA INFILE構文でcsvをインポートする方法について。おまけ程度にエクスポートする方法についてまとめる。
サンプルデータ
サンプルデータは注文履歴3000万件×10列をordersテーブルに、会員マスタ100万件×5列をusersテーブルにインポートすることを想定している。3000万件なので、そこまで大きくもないが...。テーブル定義は下記の通り。
mysql> create database ec;
mysql> use ec;
mysql> CREATE TABLE orders (
order_id INT NOT NULL,
order_datetime DATETIME NOT NULL,
user_id INT NOT NULL,
branch_num INT NOT NULL,
price INT NOT NULL,
product_id INT NOT NULL,
INDEX ind_id(user_id),
INDEX ind_ord(order_id)
);
mysql> CREATE TABLE users (
user_id INT,
gender VARCHAR(255),
area VARCHAR(255),
age INT,
INDEX id_index (user_id)
);
LOAD DATA INFILE
LOAD DATA INFILEは、高速にテキストファイルからテーブルに行を読み取ってくれるコマンド。詳細は13.2.6 LOAD DATA INFILE 構文を参照。csvであれば、オプションの区切りFIELDS TERMINATEDを,にして、文字列の囲み文字があればOPTIONALLY ENCLOSEDは"(ダブルクオート)、カラム名があればIGNOREでスキップする行数を入れる。
LOAD DATA INFILE '<path to csv>' INTO TABLE <db.table> FIELDS TERMINATED BY '<hoge>' OPTIONALLY ENCLOSED BY '<hoge>' IGNORE <hoge> LINES;
100万件くらいであれば数秒でインポートが完了する。
mysql> LOAD DATA INFILE '~/Desktop/cust_mst_1m.csv' INTO TABLE ec.users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES; Query OK, 959676 rows affected (15.80 sec) Records: 959676 Deleted: 0 Skipped: 0 Warnings: 0
3000万件くらいであれば30分くらいでインポートが完了する。
mysql> LOAD DATA INFILE '~/Desktop/orders_30m.csv' INTO TABLE ec.orders FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES; Query OK, 32434489 rows affected (35 min 54.56 sec) Records: 32434489 Deleted: 0 Skipped: 0 Warnings: 0
こんな感じ。
mysql> SELECT count(1) FROM ec.orders; SELECT count(1) FROM ec.users; +----------+ | count(1) | +----------+ | 32434489 | +----------+ 1 row in set (2.47 sec) mysql> SELECT count(1) FROM ec.users; +----------+ | count(1) | +----------+ | 959676 | +----------+ 1 row in set (0.15 sec)
--secure-file-priv option
--secure-file-priv optionでこける。これはセキュリティ系の話だそう。こちら
MySQLでCSV出力しようとしたら --secure-file-priv option のエラーになったを参照し、修正した。下記、修正するための実行内容。まずmy.cnfが読み込まれる場所を確認する。
$ mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
/usr/local/etc/my.cnfの内容をテキストエディタでもvimでもなんでもいいので、最終行にsecure-file-priv = ""を追記して保存。本来は空白なんかよりもパスワード先とかを指定するほうが良いらしい。
[mysqld] ***** なんかの設定。 secure-file-priv = ""
Mysqlサーバーを再起動して、下記のSQLを実行すると、下記の結果が空になる。通常はNULLがはいっている。
mysql> SELECT @@global.secure_file_priv; +---------------------------+ | @@global.secure_file_priv | +---------------------------+ | | +---------------------------+ 1 row in set (0.00 sec)
あとはインサートするだけ。
空白などの値がある場合のインサート
id,val,name 1,100,"1回目のインサート" 2,200,"2回目のインサート" 3,300,"3回目のインサート" 4,,"4回目のインサート" 5,500, mysql> truncate tbl01; Query OK, 0 rows affected (0.02 sec) mysql> LOAD DATA INFILE '~/Desktop/sample.csv' INTO TABLE hatena.tbl01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES; ERROR 1366 (HY000): Incorrect integer value: '' for column 'val' at row 4
その場合は、このパターンだったらNULLに変換、というコマンドを追加する必要がある。IGNORE 1 LINESの下の部分がそれに当たる。
mysql> LOAD DATA INFILE '~/Desktop/sample.csv' INTO TABLE hatena.tbl01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES ( @var_id, @var_val, @var_name) set `id` = nullif(@var_id, ""), `val` = nullif(@var_val, ""), `name` = nullif(@var_name, ""); Query OK, 5 rows affected (0.01 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from tbl01; +------+------+---------------------------+ | id | val | name | +------+------+---------------------------+ | 1 | 100 | 1回目のインサート | | 2 | 200 | 2回目のインサート | | 3 | 300 | 3回目のインサート | | 4 | NULL | 4回目のインサート | | 5 | 500 | NULL | +------+------+---------------------------+ 5 rows in set (0.00 sec)
エクスポート
次は、MySQLのエクスポートについてまとめる。エクスポートするテーブルはMySQLのサンプルデータのworld。
Other MySQL Documentationにあるworld databaseをダウンロードし、Zipファイルを解凍した後、SQLファイルをMySQLにインポートする。
mysql -u root -p < ~/Desktop/world.sql Enter Password:*****
ここでは、このデータベースを使用する。
use world; Database changed
select ... into outfile
select ... into outfileはテキストファイルとして、テーブルの情報をエクスポートするコマンド。カラム名をそのままエクスポートできないので、ユニオンする必要がある。select ... into outfileのオプションは下記の通り。
| オプション名 | 内容 |
|---|---|
| FIELDS TERMINATED BY | 各フィールドを何で区切るか。 |
| FIELDS ENCLOSED BY | フィールドを何で囲むか。 |
| OPTIONALLY ENCLOSED BY | charやvarcharなどの文字列を何で囲むか。文字型以外は無視される。 |
| FIELDS ESCAPED BY | エスケープ文字の指定。 |
カラム名をコピペしてselect句とunion allでくっつけてエクスポートする。
mysql> show columns from city; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | MUL | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
実際はこのように記述する
mysql> SELECT 'ID', 'Name', 'CountryCode', 'District', 'Population' UNION ALL SELECT * FROM world.city INTO OUTFILE '~/Desktop/city.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ;
