バシャログ。

どんな時に便利なの？

例えばデータの整形をした上で違うデータベースにリストアしたり、WHERE 句で絞り込んだデータだけをリストアしたり、異なるバージョン間でダンプファイルを使ったリストアがどうにもうまくいかない場合等に CSV でのエクスポート・インポートは有用です。

SQL コマンド例

それでは実際にコマンド例を見てみましょう。

とりあえず一般的なエクスポート

SELECT
  *
FROM
  users
ORDER BY id
INTO OUTFILE
  "/tmp/users.csv"  -- 出力先のファイル名
FIELDS
  TERMINATED BY ',' -- カンマで値を区切る
  ENCLOSED BY '"' -- " で値を囲む
  ESCAPED BY '\\' -- \ でエスケープする
LINES
  TERMINATED BY '\r\n' -- \r\n で改行する
;

単純に ORDER BY id しただけのレコードをカンマ区切りでファイルに掃き出しています。ファイルの出力先は MySQL の実行ユーザで書き込み可能な場所である必要があるので注意してください。

出力したファイルからインポート

CSV ファイルからデータをインポートする SQL 例は以下です。

LOAD DATA LOCAL INFILE
  "/tmp/users.csv"
INTO TABLE
  new_users
FIELDS
  TERMINATED BY ','
  ENCLOSED BY '"'
  ESCAPED BY '\\'
LINES
  TERMINATED BY '\r\n'
;

既存のレコードを上書きしたい場合は上記に REPLACE を追加します。

LOAD DATA LOCAL INFILE
  "/tmp/users.csv"
REPLACE INTO TABLE -- REPLACE を追記
  new_users
FIELDS
  TERMINATED BY ','
  ENCLOSED BY '"'
  ESCAPED BY '\\'
LINES
  TERMINATED BY '\r\n'
;

もうちょっと色々やってみる

皆さんお気付きとは思いますが、mysqldump と違い INTO OUTFILE を使ったエクスポートは SQL の実行結果をファイルに掃き出しているだけです。つまり、SQL の機能を使ってデータの移行が可能になるという事です。以下に例をあげておきます。

条件に合致するデータだけ移行したい

削除フラグが立っていないデータだけをファイルに掃き出す例です。

SELECT
  *
FROM
  users
WHERE
  delete_flag != 1
ORDER BY id
INTO OUTFILE
  "/tmp/users.csv"
FIELDS
  TERMINATED BY ','
  ENCLOSED BY '"'
  ESCAPED BY '\\'
LINES
  TERMINATED BY '\r\n'
;

データを整形して移行したい

古いシステムから新しいシステムに移行する際、バリデーションルールがより厳格になるのはよくある事だと思います。新しいシステムで NG なデータを OK になるよう整形した上で移行する事もある程度は可能になります。

例えばメールアドレスを小文字に統一して、ファイルにエクスポートするには。

SELECT
  id,
  name,
  LOWER(email),
  delete_flag,
  created,
  modified
FROM
  users
ORDER BY id
INTO OUTFILE
  "/tmp/users.csv"
FIELDS
  TERMINATED BY ','
  ENCLOSED BY '"'
  ESCAPED BY '\\'
LINES
  TERMINATED BY '\r\n'
;

ちなみに

データ中に , （カンマ）や " （ダブルクオテーション）、 \ （バックスラッシュ）が含まれていてなんだかエクスポート・インポートがうまくいかない場合があるかもしれません。そんな時は以下のように区切り文字にタブを使う事である程度うまくいくはずです。

SELECT
  *
FROM
  users
ORDER BY id
INTO OUTFILE
  "/tmp/users.csv"
FIELDS
  TERMINATED BY '\t' -- 区切り文字をタブに
LINES
  TERMINATED BY '\r\n'
;

この場合 LOAD DATA でインポートする際も FIELDS TERMINATED BY '\t' とするのを忘れないように！

終わりに

仕事柄データ移行をする事が多い中でよく使う Tips です。特に MySQL-3.2 系から 5.0 系へのデータ移行時には救われました！