目的
csvファイルからデータをインポートしようとしたらエラーが出たので解決方法をを残す。
（調べたときによくわからなかった用語もメモする。）
経緯
- テスト用データベース（test）を作成。
- テスト用テーブルを作成。
- csvファイルにデータを記載。
- csvファイルからDBにインポートしようとしたら、エラーが出た。
手順
- csvファイルにデータを作る。
- sqlでデータベースを作る。
- sqlでテーブルを作る。
- local_infileが有効かどうかを確かめる。
- csvをインポートする。
01:テスト用データベースを作成
テスト用のデータベース（test）を作成。
mysql> create database test;
02:テスト用テーブルを作成
id、name、statusをもっているテスト用のテーブル(test_table)を作成。
mysql> create table test_table
-> (
-> id int not null auto_increment,
-> name varchar(16) not null,
-> status int not null,
-> primary key (id)
-> );
< auto_increment >
カラム値が指定されなかったとき、mysqlが自動で番号を振ってくれる。データ型は整数で。値は1ずつ増えて連番になる。
< primary key >
テーブルに登録されたレコード全体から、ひとつのデータに特定できる列のこと。例えば、同じ名前の人がいたとしてもidが違えば別人だと判断できる。レコードが一意のものだと表すことができる。
03:csvファイルにデータを記載
csvファイルにインポートしたいデータを書いて保存する。
ファイル名はtest_data.csvにしました！
1,"satoshi",2
2,"kasumi",2
3,"takeshi",1
04:いよいよcsvファイルからDBにインポート！
先ほど作成したcsvファイルをインポートしてDBにデータを入れる。
${FILE_PATH}には、csvファイルを保管した場所までの絶対パスを入れる。
mysql> load data local
-> infile '${FILE_PATH}/test_data.csv'
-> into table
-> test.test_table
-> fields
-> terminated by ','
-> enclosed by '"'
-> ;
< local >
FILE権限なしで、ローカルファイルの読み込みが可能になる。 行の重複エラーがあっても挿入処理が続行される。（データがなければインサート、あればスキップしてくれる。）
< terminated by >
区切り文字を指定する。今回はカンマで区切る。
< enclosed by >
囲い文字を指定する。今回はダブルクオート。
エラー発生！
これでうまくいくぞ！と思ったらエラー発生…。
ERROR 1148 (42000): The used command is not allowed with this MySQL version
え、バージョン対応してないの？と思って確認してみると、バージョンは8.0.18。別に格段古い感じでもないよな〜と思って他の原因を探すことに。
原因
公式ドキュメントにこんな記載が…！
--local-infile[=1] オプションを指定することによって LOAD DATA LOCAL を有効にする
↓ これか？！と思って調べてみると、、、
mysql> select @@local_infile;
+----------------+
| @@local_infile |
+----------------+
| 0 |
+----------------+
1 row in set (0.00 sec)
どうやらこいつのせいっぽいぞ…？！
< @@ local_infile >
変数であることを明示的に指示するために、変数名の前に@@をつける。
そもそも local_file ってなんぞ？
local_fileは、ローカルオプションをつけてload dataするときに、サーバー側の機能を制御してくれるMySQLのシステム変数。local_infileの設定に応じて、サーバはクライアント側で有効にしたクライアントがローカルデータのロードを行うことを拒否または許可する。
つまり、アクセス制限みたいなことをやってくれてる変数みたい。デフォルト状態では無効になっているから、明示的に無効か有効かを書かないといけない。
無効：local_infile = 0
有効：local_infile = 1
どうやら、自分で自分を無効にしていたからできなかったっぽい…。
local_infile 設定する
さっそく設定！！
mysql> set persist local_infile=1;
mysql> select @@local_infile;
+----------------+
| @@local_infile |
+----------------+
| 1 |
+----------------+
1 row in set (0.01 sec)
無事、有効にできました。
< set persist >
サーバのパラメータを設定するときにset persistを使うと再起動後も保持しておいてくれる。
やっとcsvインポートできる…！！
まじで長かった。インポートすっぞ！！！！！！
mysql> load data local
-> infile '/Users/saori/quiita/mysql/csv-import/test_data.csv'
-> into table
-> test.test_table
-> fields
-> terminated by ','
-> enclosed by '"';
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from test_table;
+----+---------+--------+
| id | name | status |
+----+---------+--------+
| 1 | satoshi | 2 |
| 2 | kasumi | 2 |
| 3 | takeshi | 1 |
+----+---------+--------+
3 rows in set (0.01 sec)
うをおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおおお！！！！！（歓喜）
引っかかったこと
csvを読み込ませるとき、囲み文字の指定が必要。
csvファイルを作るときに、nameがvarcharだからダブルクオートでくくったら、ダブルクオートも入った奇妙なデータを作り出してしまった。loadを実行するときにenclosedを設定しなかったから、うまく読み取ってくれなかったみたい。
↓ loadをこうして実行したら、、、
mysql> load data local infile
-> '${FILE_PATH}/test_data.csv'
-> into table
-> test.test_table
-> fields terminated by ',';
↓ こうなった。（悲しい）
mysql> select * from test_table;
+----+-----------+--------+
| id | name | status |
+----+-----------+--------+
| 1 | "satoshi" | 2 |
| 2 | "kasumi" | 2 |
| 3 | "takeshi" | 1 |
+----+-----------+--------+
3 rows in set (0.00 sec)
書いてあったのに見過ごしました。ちゃんと囲み文字の指定もしましょう。
まとめ
大量のデータをインポートするときは、csvにデータをまとめて入れるのが楽だしはやそうだなと思った。csvファイルからデータをインポートするには、以下の手順でやる。
1. csvファイルにデータを作る。
2. sqlでデータベースを作る。
3. sqlでテーブルを作る。
4. local_infileが有効かどうかを確かめる。
5. csvをインポートする。
参考資料
- MySQLのAUTO_INCREMENTについて色々と調べてみた。
- 「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典「主キー」
- LOAD DATA INFILE構文：データのインポート
- MySQL 8.0でLOAD DATA LOCAL INFILEが "ERROR 1148 (42000): The used command is not allowed with this MySQL version" で失敗する時
- MySQL 8.0リファレンスマニュアル LOAD DATA LOCALのセキュリティ問題
- MySQL 8.0 の SET PERSIST について
- MySQL 5.6 リファレンスマニュアル システム変数の使用
修正
- lical_infileになっていたため、local_infileに修正しました。
ご指摘いただきありがとうございました！