CodlessCode
MySQL

【 MySQL 】 JSON 型を含むテーブルを CSV エクスポートして別 MySQL にインポート(sedで加工)

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
  1. 先頭に「’」をつける
    $ 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
        
  2. 末尾に「’」をつける
    $ 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'
            
  3. タブ「\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'
            
  4. 「’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を検索して1に変更する。

set local infile in mysql rds

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 → 1行目を無視(列名なので)

詳細は公式リファレンスを参照。
バージョン5.6のものだが、2021/10/27時点では5.7.128.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は5行あったが、
そのうち5行に警告が出て、5行スキップされたということ。つまり全て無視された。

警告を見る場合はshow warnings;を実行する。

まとめ

  1. 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
            
  2. オプション付きでMySQLにログイン
    mysql -h ホスト名 -u ユーザー名 -p DB名 --local_infile=1
            
  3. load data local infile を実行
    load data local infile "ファイルのパス" into table テーブル名 FIELDS TERMINATED BY ',' ENCLOSED BY "'" IGNORE 1 LINES;
            

お読みいただきありがとうございました。