JSON 型を含むテーブルを CSV エクスポートする方法と MySQL インポートする方法を調べました。
RDBで JSON 型なんてまず扱わないかもしれないけど、私はまれに使います。
こういうちょっとした小技は忘れがちで
CSVエクスポートほうやり方忘れちゃったから、ネットでさっと調べてコピペしちゃおう。
あ、 JSON 型のせいで「”」が使えない、、、
となってしまったりして、どうでもいいところで時間食ったりとか。
次からは脳死でもできるように自分なりの方法をまとめておこうと思います。
参考になれば幸いです。
作業はMacでやってます。
MySQL 5.7.12 と 8.0.24 で動作を確認しました。
MySQL の JSON 型を含むテーブルを CSV エクスポート
実際のコマンド
mysql -h ホスト名 -u ユーザー名 -p DB名 -e "select * from テーブル;” | sed -e "s/^/'/g" | sed -e "s/\$/'/g" | sed -e "s/\t/','/g" | sed "s/'NULL'/NULL/g" > ./export.csv
例）samplesというJSON型を含むテーブルがあったとして
$ mysql -h H -u U -p DBNAME -e "select * from samples;" > export.csv
$ cat export.csv
id temp_max temp_min coord weather icon
1 14.51 12.96 {"lat": 34.8537, "lon": 135.638} [{"id": 803, "main": "Clouds"}] NULL
上記のデータがsedによって加工されて以下のようになる。
$ mysql -h H -u U -p DBNAME -e "select * from samples;" | sed -e "s/^/'/g" | sed -e "s/\$/'/g" | sed -e "s/\t/','/g" | sed "s/'NULL'/NULL/g" > export.csv
$ cat export.csv
'id','temp_max','temp_min','coord','weather','icon'
'1','14.51','12.96','{"lat": 34.8537, "lon": 135.638}','[{"id": 803, "main": "Clouds"}]',NULL
補足：処理の流れ
処理前の状態
id temp_max temp_min coord weather icon
1 14.51 12.96 {"lat": 34.8537, "lon": 135.638} [{"id": 803, "main": "Clouds"}] NULL
- 先頭に「’」をつける
$ mysql -h H -u U -p DBNAME -e "select * from samples;" | sed -e "s/^/'/g" > export.csv 'id temp_max temp_min coord weather icon '1 14.51 12.96 {"lat": 34.8537, "lon": 135.638} [{"id": 803, "main": "Clouds"}] NULL - 末尾に「’」をつける
$ mysql -h H -u U -p DBNAME -e "select * from samples;" | sed -e "s/^/'/g" | sed -e "s/\$/'/g" > export.csv 'id temp_max temp_min coord weather icon' '1 14.51 12.96 {"lat": 34.8537, "lon": 135.638} [{"id": 803, "main": "Clouds"}] NULL' - タブ「\t」を「’,’」に置き換える
$ mysql -h H -u U -p DBNAME -e "select * from samples;" | sed -e "s/^/'/g" | sed -e "s/\$/'/g" | sed -e "s/\t/','/g" > export.csv 'id','temp_max','temp_min','coord','weather','icon' '1','14.51','12.96','{"lat": 34.8537, "lon": 135.638}','[{"id": 803, "main": "Clouds"}]','NULL' - 「’NULL’」を「NULL」に置き換える
$ mysql -h H -u U -p DBNAME -e "select * from samples;" | sed -e "s/^/'/g" | sed -e "s/\$/'/g" | sed -e "s/\t/','/g" | sed "s/'NULL'/NULL/g" > export.csv 'id','temp_max','temp_min','coord','weather','icon' '1','14.51','12.96','{"lat": 34.8537, "lon": 135.638}','[{"id": 803, "main": "Clouds"}]',NULL
MySQLにCSVインポート
local_infile=1の確認
MySQLにログインし
show variables Variable_name = 'local_infile';
ONかどうか確認する。
必要に応じて値を変更する。
set global local_infile = 1;
AWS RDSの場合は、
パラメータグループからlocal_infileを検索して１に変更する。
load data local infile でインポート
オプション付きでMySQLにログインし、ローカルファイルをインポートするSQLを実行する。
ファイルパスは相対でも絶対でも大丈夫。
mysql -h ホスト名 -u ユーザー名 -p DB名 --local_infile=1
mysql> load data local infile "ファイルのパス" into table テーブル名 FIELDS TERMINATED BY ',' ENCLOSED BY "'" IGNORE 1 LINES;
- FIELDS TERMINATED BY ‘,’ → カンマ区切り
- ENCLOSED BY “‘” → 各列のデータを囲う文字
- IGNORE 1 LINES → １行目を無視（列名なので）
詳細は公式リファレンスを参照。
バージョン5.6のものだが、2021/10/27時点では5.7.12と8.0.23で動作確認できた。
NULL値の扱い
公式から、NULL値について書かれた箇所を抜粋すると
FIELDS ENCLOSED BY が空でない場合、リテラルワード NULL をその値として含むフィールドは NULL 値として読み取られます。これは、文字列 ‘NULL’ として読み取られる、FIELDS ENCLOSED BY 文字で囲まれたワード NULL とは異なります。
なので、上記の文法だったらNULLとすることでNULL値として処理してくれる。
データが重複した場合
データが重複したり、外部キー制約に違反したなど、何かしらの理由でデータがインポートできない場合、
Skippedカウントが増えて、そのデータは無視される。
以下のような感じ。
mysql> load data local infile ……
Query OK, 0 rows affected, 5 warnings (0.02 sec)
Records: 5 Deleted: 0 Skipped: 5 Warnings: 5
この場合、インポートしようとしたRecordsは５行あったが、
そのうち５行に警告が出て、５行スキップされたということ。つまり全て無視された。
警告を見る場合はshow warnings;を実行する。
まとめ
- CSVにエクスポート
mysql -h H -u U -p DBNAME -e "select * from samples;" | sed -e "s/^/'/g" | sed -e "s/\$/'/g" | sed -e "s/\t/','/g" | sed "s/'NULL'/NULL/g" > export.csv - オプション付きでMySQLにログイン
mysql -h ホスト名 -u ユーザー名 -p DB名 --local_infile=1 - load data local infile を実行
load data local infile "ファイルのパス" into table テーブル名 FIELDS TERMINATED BY ',' ENCLOSED BY "'" IGNORE 1 LINES;
お読みいただきありがとうございました。
