【MySQL】JSON型を含むテーブルをCSVエクスポートして別MySQLにインポート（sedで加工）
目次
JSON 型を含むテーブルを CSV エクスポートする方法と MySQL インポートする方法を調べました。
RDBでJSON型なんてまず扱わないかもしれないけど、私はまれに使います。こういうちょっとした小技は忘れがちで
CSVエクスポートほうやり方忘れちゃったから、ネットでさっと調べてコピペしちゃおう。あ、 JSON型のせいで「”」が使えない、、、
となってしまったりして、どうでもいいところで時間食ったりとか。次からは脳死でもできるように自分なりの方法をまとめておこうと思います。
作業はMacでやってます。
MySQL5.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"}]',NULLMySQLに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;詳細は公式リファレンスを参照。
バージョン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値として処理してくれる。
データが重複した場合
データが重複したり、外部キー制約に違反したなど、何かしらの理由でデータがインポートできない場合、そのデータは無視される。以下のような感じ。
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=1load data local infile を実行
load data local infile "ファイルのパス" into table テーブル名 FIELDS TERMINATED BY ',' ENCLOSED BY "'" IGNORE 1 LINES;