はじめに
大量データを登録したいときに何か便利で効果的な方法はないか探っていたときに知ったもの
- 登録しようとしているレコード情報をファイルに書き出す
- MySQLに接続してファイルを読み込み→レコード登録
こういったやり方でもレコード登録ができる
INSERT INTO ...を使わないこういったやり方もある
登録データをサーバ上にファイルとして残せるので管理しやすそう
データの確認もしやすい
サンプル
/* ファイルシステム上のCSVファイルのパスを指定 */
LOAD DATA INFILE '/path/path2/path3/data/file.csv'
/* データを挿入する対象のテーブル名を指定 */
INTO TABLE table
/* 各設定値の区切り文字と、囲む文字列を指定 */
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
/* 行（=レコード）の区切り文字を指定 */
LINES TERMINATED BY '\n'
/* CSVファイルの最初の行（ヘッダー行など）を無視 */
IGNORE 1 LINES;
"ID","Name","Age"
"1","AAA","25"
"2","BBB","30"
"3","CCC","22"
事前準備
いずれも必須
- ユーザにFILE権限があるか確認（なければ付与）
- 読み込むファイルを格納する場所の指定
- ファイルを作成
FILE権限の確認と付与
権限がない状態でLOAD DATA ...を実行するとこのようなエラーがでる
ERROR 1045 (28000): Access denied for user 'user_test'@'localhost' (using password: YES)
権限の確認
対処法として
まずは付与されている権限を確認する
mysql> SHOW GRANTS FOR 'user_test'@'localhost';
+------------------------------------------------------------------------------+
| Grants for user_test@localhost |
+------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON `test_database`.* TO `user_test`@`localhost` |
+------------------------------------------------------------------------------+
ちなみに、ALL PRIVILEGES には FILE権限は含まれていなかった
なのでこの状態ではLOAD DATA ...は使えない
FILE権限の付与
GRANT ... ONでFILEを指定する
-- 実行例
mysql> GRANT FILE ON *.* TO 'user_test'@'localhost';
Query OK, 0 rows affected (0.01 sec)
-- 確認
mysql> SHOW GRANTS FOR 'user_test'@'localhost';
+------------------------------------------------------------------------------+
| Grants for user_test@localhost |
+------------------------------------------------------------------------------+
| GRANT FILE ON *.* TO `user_test`@`localhost` |
| GRANT ALL PRIVILEGES ON `test_database`.* TO `user_test`@`localhost` |
+------------------------------------------------------------------------------+
GRANT FILE ON *.*という権限が付与される
これで先ほどのエラーは出なくなる
補足
GRANT FILE ON test_database.* TO 'user_test'@'localhost';
このような記述はできなかった
MySQLではFILE権限をデータベースに対して直接付与することはできない
FILE権限はグローバルな権限で、データベース単位での制御はできないそう
パスの指定
FILE権限を付けるだけでは設定が足りていない場合がある
今度はこのようなエラーが出ることがある
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
対処法は２つあった
- 現在の設定で指定されているパスにファイルを確認→そこに配置する
- MySQLの設定ファイルを編集してパス指定を消す
指定パスを確認
-- SQLで調べる 1
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
-- SQLで調べる 2
mysql> select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/ |
+---------------------------+
1 row in set (0.00 sec)
この場合では
/var/lib/mysql-files/ ディレクトリ内でのファイルの読み書きのみ許可している状態
設定ファイル編集→パス指定を消す
# 実行例
# 設定ファイルを編集
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# "[mysqld]"の下に追加
# 追加: secure-file-priv = ""
# MySQL再起動
$ sudo systemctl stop mysql
$ sudo systemctl start mysql
$ sudo systemctl status mysql
設定ファイル編集→MySQL再起動
これで設定が反映される
-- SQLで調べる 1
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | |
+------------------+-------+
1 row in set (0.01 sec)
-- SQLで調べる 2
mysql> select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| |
+---------------------------+
1 row in set (0.00 sec)
パス指定がなくなった
ファイル作成
このようなファイルを用意した
"client_no","name","address","post_code","phone_number","email"
"1","name_1","address_1","100-0001","01000000001","mail_1"
"2","name_2","address_2","100-0001","01000000002","mail_2"
"3","name_3","address_3","100-0001","01000000003","mail_3"
対象テーブルの定義はこちら
mysql> DESC clients;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| client_no | int | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| address | varchar(100) | YES | | NULL | |
| post_code | char(8) | YES | MUL | NULL | |
| phone_number | char(12) | YES | | NULL | |
| email | varchar(30) | YES | UNI | NULL | |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
コマンド実行例
-- 実行例
mysql> LOAD DATA INFILE '/var/lib/mysql-files/insert_clients.csv'
-> INTO TABLE clients
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> IGNORE 1 LINES;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
-- 確認
mysql> select count(*) from clients;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
ちなみに筆者の環境では初期設定で
secure_file_priv=/var/lib/mysql-files/
が指定されていた
おわりに
これは非常に便利だと思った。
でも、業務で使える場所はそんなになさそうかな。セキュリティリスクや容量問題などが出てきそうだし。使える場所だったら手段の一つとして持っておくといいかもしれない。