揮発性のメモ２

知識をメモ書きしておく

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

MySQL

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全機能バイブル