somemo's diary

プログラマ、雑記、プログラミング関係はLinkから、数式はこっちでまとめていることが多い

【MySQL】データのインポート・エクスポート【CSV】

csvファイルからのインポートと、csvファイルへのエクスポートについてのメモです。

テスト用テーブル

以下のようなテーブルを作成します。テーブル名はEclipseからエクスポートしたEmacsキーバインドをテストしているためです。

create table csv_test
(
  id   int,
  name varchar(4000)
)

インポート

ファイルを用意して、以下のSQLを実行します。

LOAD DATA LOCAL INFILE "c:\\test.csv"
INTO table csv_test
FIELDS TERMINATED BY ','
       ENCLOSED BY '"'
       ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

上から順に以下の意味です。

  • 入力ファイルの指定(LOCAL指定:接続元のファイルを参照する)
  • データを入れるテーブル名(スキーマ.テーブルも可能)
  • フィールドの区切り
  • フィールドの囲み文字
  • エスケープ文字
  • 行の終了文字
  • 先頭から無視する行数

FIELDSは省略可能です。省略したときの値は以下のようになります。

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

つまり、CSVに限らずTSVでも使えるようです。また、LINESの省略時は「\n」になります。

重複データの扱い

作成したテーブルにはPKがないので、再度実行すると、同じデータが作成されます。まずPKを追加します。

alter table csv_test add constraint primary key (id);

PKが存在する状態で再度実行してみます。0件実行となり、無視されました。既存のレコードを上書きしたい場合、以下のようにして実行します。

LOAD DATA LOCAL INFILE "c:\\test.csv"
REPLACE INTO table csv_test
FIELDS TERMINATED BY ','
       ENCLOSED BY '"'
       ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

更新した件数が一番最初に実行したときと同じになります。REPLACEをつけない場合は、IGNOREをつけたことになります。ただし、LOCALを指定したときのみです。LOCALを指定せず、サーバのファイルを使用したときはエラーとなるようです。

文字セット

CSVファイルの文字コードsjisに設定されていることが多いです。ただし、データベースの文字セットはUTF8に設定されていることが多いです。この場合、正しくデータが作成されません。実際にUTF8のデータベースに日本語+ASCIIデータが格納されているsjisCSVファイルをインポートすると空で登録されました。ASCII+日本語の場合、ASCIIまでのデータが作成されます。

これを解決するには、インポートする前に文字セットを指定しあげます。

set character_set_database=sjis

エクスポート

以下のSQLを実行します。

SELECT * FROM csv_test
INTO OUTFILE "c:\\test_out.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'

再度実行すると、以下のエラーが発生します。インポートと同様にREPLACEをつけてみましたが、構文エラーになりました。

ERROR 1086 (HY000): File 'c:\test_out.csv' already exists

めんどうですが、ファイルを削除して実行しなおしましょう。

FILE権限

エクスポートの実行は、rootユーザで行っていました。テーブルに対するCRUDのみを行える一般ユーザでは行えず、FILE権限が必要です。

FILE は、LOAD DATA INFILE および SELECT ... INTO OUTFILE ステートメントを使用してサーバ上でファイルの読み書きを行う権限です。FILE 権限を持つユーザは、サーバ ホストのすべてのファイル、つまり MySQL サーバの読み込み可能ファイルを読み込めます。(これは、この権限を持つユーザであれば、サーバがファイルへのアクセスを許可するために、データベース ディレクトリのファイルを読むことができるということです。) さらに、FILE 権限があるユーザは、MySQL サーバが書き込みアクセスのあるディレクトリに、新しいファイルを作成できます。セキュリティ対策として、サーバは既存ファイルの上書きは行ないません。

LOCALを指定していたのでLOAD DATA INFILEを実行できましたが、サーバのファイルを上書きする危険性があるためエクスポートできないわけです。FILE権限を与えるには以下を実行します。FILEはデータベースに関係ないため、グローバルに設定します。

GRANT FILE ON *.* TO user@localhost

権限を付与されてからは実行することができました。

また文字セット

エクスポート時にも文字コードによる問題が発生していました。CSVに出力されますが、文字化けしています。事前の文字セット

これを解決するには、同じ構造の異なる文字セットに設定されたテーブルを作成する必要があります。


create table csv_test
(
  id   int,
  name varchar(4000)
) CHARACTER SET sjis COLLATE sjis_japanese_ci

ここまですると面倒なので、dumpのほうがよさそうです。