Hatena::ブログ(Diary)

揮発性のメモ このページをアンテナに追加 Twitter

2016/02/06 (土)

MySQLでcsvファイルをインポートする

mysqlimportコマンドインポートする

$ mysqlimport -uhogehoge -pPASSWORD hogeDB 
  --delete
  --fields-terminated-by=','
  --fields-optionally-enclosed-by='"'
  ~/T_PIYOPIYO.csv

ファイル名から拡張子を捨てた分をテーブル名とみなして突っ込んでくれる。 オプション次第で並列処理もしてくれる。

--ignore-lines=n 無視する行数。カラム名無視したいときとか使う
--use-threads=n 最大スレッド数。 複数ファイルを同時処理したいとき使う
--deleteインポート前にテーブルを削除するとき指定
--fields-terminated-by=s区切り文字デフォルトは "\t"
--fields-enclosed-by=s
--fields-optionally-enclosed-by=s
くくり文字デフォルトは なし
必ずくくられてるわけじゃあないときはoptionallyの方使う
--lines-terminated-by=s改行コードデフォルトは "\n"
--ignore
--replace
キーがダブった時の処理。無視するか、上書きするか。
注意事項1
ファイルの読み書き権限必要
注意事項2
ファイル名は相対パスでなく絶対パス指定する(mysqlユーザで動作するため)
注意事項3
insert契機のトリガー普通に発動する

CSVファイルプロンプトからエクスポート

mysql> select * from T_PIYOPIYO 
       into outfile '/tmp/T_PIYOPIYO.csv' 
       fields terminated by ',' 
       enclosed by '"';

区切り文字はタブ(\t)、改行は\n、項目はくくらない

というデフォルト設定なので、最低限 カン指定とくくり文字指定する

あと、ファイルの読み書き権限必要


CSVファイルをmysqldumpコマンドエクスポート

$ mysqldump -uhogehoge -pPASSWORD hogeDB T_PIYOPIYO 
  --tab=/tmp 
  --fields-optionally-enclosed-by='"' 
  --fields-terminated-by=','
  -t --skip-triggers

--tab指定したディレクトリに、テーブル名.txt という名前データ作成される。

1行目からデータがはじまる(カラム名の行は無い)

テーブル名.sqlスキーマとかトリガとかが出力される。(--skip-triggersと-tを両方指定すれば、.sqlファイルは作られない)

--tab データを別ファイルで出力する
フォーマット指定オプションはmysqlimportのオプション参照
--no-create-info (-t) CREATE TABLE を書かない
--skip-triggers トリガを書かない

ファイルの読み書き権限必要


TSVファイルmysqlコマンドエクスポート

$ mysql -uhogehoge -pPASSWORD hogeDB -e 'select * from T_PIYOPIYO' > /tmp/T_PIYOPIYO.tsv

出力先が標準出力だと表に整形されるけど、それ以外ならTSVで出る。

先頭行がカラム名のtsvが出力される。くくり文字は無し。

この方法だけは ファイルの読み書き権限不要


CSVプロンプトからインポート

mysql> load data infile "/home/hogehoge/T_PIYOPIYO.csv" 
       into table T_PIYOPIYO
       fields terminated by ','
       optionally enclosed by '"';

ファイルの読み書き権限必要

エクスポートの時と同じく、区切り文字やくくり文字に気を付ける。


ファイルの読み書き権限を与える

mysql> GRANT file on *.* to hogehoge@localhost;

ファイルの読み書き権限を奪う

mysql> REVOKE file on *.* from hogehoge@localhost;

ファイルの読み書き権限テーブルごととかは出来なくて一括なので、特定DB指定したりするとむしろエラーになる

mysql> GRANT file on hogeDB.* to hogehoge@localhost;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'hogeDB.* to hogehoge@localhost' at line 1

MariaDB&MySQL全機能バイブル

はてなユーザーのみコメントできます。はてなへログインもしくは新規登録をおこなってください。

トラックバック - http://d.hatena.ne.jp/iww/20160206/csv
2006 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2007 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2008 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2009 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2010 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2011 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2012 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2013 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2014 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2015 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2016 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2017 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |
2018 | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 |