- 2005-05-10 (火)
- MySQL
タブ区切りテキスト等の外部ファイルをMySQLのDB/テーブルに読み込む方法について、MySQL.com Documentation から要点のみを抽出してメモってみました。今まとめておかないと速攻で忘却の彼方に葬ってしまいそうなので。一応、現時点で最新バージョンの MySQL 4.1.x 系を前提に話を進めていますが、特記がない限りはバージョン 3.x - 4.0.x でも問題無く通用するかと思われます。あと、MySQL.com Documentation は英語版の引用を用いてメモってますが、あるいは日本語版のDocumentationの該当箇所をざーっと最初から最後まで読んだ方が全体的な理解は早いかもしれません... （日本語版は、決して英語版Documentationの忠実な翻訳では無い ...って事に気づいてから、ヘンなこだわりもってます）。
- 概要
- LOAD DATA ... INFILE 構文 - SYNTAX
- LOAD DATA ... INFILE 構文 - LOCAL オプションについて
- LOAD DATA ... INFILE 構文 - REPLACE, IGNORE オプション
- LOAD DATA ... INFILE 構文 - FIELDS オプション
- LOAD DATA ... INFILE 構文 - LINES オプション
- LOAD DATA ... INFILE 構文 - IGNORE n LINES オプション
- LOAD DATA ... INFILE 構文 - NULL の扱い
- LOAD DATA ... INFILE 構文 - カラム数の不一致があった場合の挙動
- LOAD DATA ... INFILE 構文 - SHOW WARNINGSについて
- mysqlimport - SYNTAX
- mysqlimport - --replace, --ignore オプションの挙動について
- mysqlimport - 複合キーのテーブルにインポート出来ない？
- 参照情報
概要
インポート方法には
mysql client からは LOAD DATA ... INFILE 構文を
shell prompt からは mysqlimport コマンドを
の２通りがあります。どちらも機能はほぼ同等ですが利便性では、MySQLバージョン4.1.1より導入された
SHOW WARNINGS 構文 - 前回実行したSQLで発生したWARNINGの詳細情報を表示する機能
を利用できる、という点で若干 LOAD DATA ... INFILE 構文の方が便利かと思います。以下、それぞれの方法について要点解説していきます。
LOAD DATA ... INFILE 構文 - SYNTAX
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]
]
[LINES
[STARTING BY '']
[TERMINATED BY '\n']
]
[IGNORE number LINES]
[(col_name,...)]
超基本的な使い方例:
mysql> LOAD DATA INFILE '/path/to/file.txt' INTO TABLE tbl_hoge;
LOAD DATA ... INFILE 構文 - LOCAL オプションについて
If the LOCAL keyword is specified, it is interpreted with respect to the client end of the connection (v3.22.6 or later) . If LOCAL is specified, the file is read by the client program on the client host and sent to the server.
LOCALオプションをつける事により、クライアント端末側に存在するファイルを指定する事ができます。指定されたファイルはクライアント側で読み込まれた後にサーバ側に転送される、と。なので、LOCALオプションをつけない場合は読み込むファイルはMySQLサーバ上になければダメみたいです。
to use LOAD DATA INFILE on server files, you must have the FILE privilege.
LOCALオプションをつけない場合（サーバ上のファイルを読み込む場合）は
ユーザに対して FILE権限 が与えられている必要がある
そうです。通常運用で考えると FILE権限をもらえるユーザってのはそれなりに信頼されている場合に限られていると思うので、サーバ共有型のホスティングサービスとかだと FILE権限は付与されていないケースが多いのでは（ = LOCALオプション必須）。
LOAD DATA ... INFILE 構文 - REPLACE, IGNORE オプション
REPLACE および IGNORE オプションは共に、インポート中のデータに主キーの重複があった際の対処方法について指定するオプションです。
If you specify REPLACE, input rows replace existing rows (in other words, rows that have the same value for a primary or unique index as an existing row) . If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped.
REPLACE を指定した場合、主キーの重複があった場合には既存のレコードを削除した上でファイル側のレコードをインポートします（ファイル側のデータがイキになる）。IGNORE を指定した場合はその逆に、ファイル側のレコードを無視して次の行の処理に進みます（データベース側の既存データがイキになる）。
If you don't specify either option, the behavior depends on whether or not the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation.
REPLACE, IGNORE どちらも指定しなかった場合の挙動は、LOCALオプションの有無により変わるそうな。
- LOCAL無 → 重複レコードを発見した時点でエラーになる = その時点で処理強制終了
- LOCAL有 → IGNORE を指定した場合と同様 （重複レコードが無視される）
LOAD DATA ... INFILE 構文 - FIELDS オプション
delimiter（区切り文字）を指定したい場合は
FIELDS TERMINATED BY ','
と記述します（例はカンマ区切り）。
各カラム中の文字列の前後を " 等で囲みたい場合は
FIELDS ENCLOSED BY '"'
と指定します。これだとすべての文字列が " で囲まれますが、テーブル定義上の CHAR型 および VARCHAR型 カラムの文字列のみを " で囲みたい場合は以下のように OPTIONALLY 接頭語を追加します。
FIELDS OPTIONALLY ENCLOSED BY '"'
文中のエスケープ文字を指定したい場合は
FIELDS ESCAPED BY '\\'
と記述します（例はバックスラッシュ）。
これら３つを同時に指定する場合は以下のように繋げた形で記述できます:
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
これらFIELDSオプションを一切指定しない場合のデフォルト設定は以下の記述と同等の挙動になります:
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LOAD DATA ... INFILE 構文 - LINES オプション
行末を示す改行コードを指定したい場合は
LINES TERMINATED BY '\r\n'
と記述します（例はCR+LF）。
行頭になにかしらの接頭文字列がついていて、それらを読み込む際に無視したい場合は
LINES STARTING BY '>'
と記述します（例は行頭の引用符を無視する）。以下、わかりやすい解説文を引用：
mysql> LOAD DATA INFILE '/tmp/test.txt'
-> INTO TABLE test LINES STARTING BY "xxx";
With this you can read in a file that contains something like:
xxx"Row",1
something xxx"Row",2
And just get the data ("row",1) and ("row",2).
なお、これら LINES オプションを一切指定しない場合のデフォルト設定は以下の記述と同等の挙動になります:
LINES TERMINATED BY '\n' STARTING BY ''
LOAD DATA ... INFILE 構文 - IGNORE n LINES オプション
読み込むタブ区切りテキストファイルの先頭行にヘッダ情報／カラム名情報がついている場合などに、このオプションを指定する事で、先頭行を任意の行数分スキップさせることができます。以下ヘッダ行を１行スキップさせる場合の記述例：
IGNORE 1 LINES
LOAD DATA ... INFILE 構文 - NULL の扱い
Handling of NULL values varies according to the FIELDS and LINES options in use:
*For the default FIELDS and LINES values, NULL is written as a field value of \N for output, and a field value of \N is read as NULL for input (assuming that the ESCAPED BY character is '\').
*If FIELDS ENCLOSED BY is not empty, a field containing the literal word NULL as its value is read as a NULL value. This differs from the word NULL enclosed within FIELDS ENCLOSED BY characters, which is read as the string 'NULL'.
*If FIELDS ESCAPED BY is empty, NULL is written as the word NULL.
説明めんどうくさいので省略。引用文の通りです /w
LOAD DATA ... INFILE 構文 - カラム数の不一致があった場合の挙動
If an input line has too many fields, the extra fields are ignored and the number of warnings is incremented.
読み込みテキストファイル側のカラム数が多かった場合、Warning としてカウントされた上で、余分なカラム分の情報はすべて無視されます。
If an input line has too few fields, the table columns for which input fields are missing are set to their default values.
読み込みテキストファイル側のカラム数が少なかった場合は、Warning としてカウントされた上で、足りないカラム分の情報はそれらカラムのデフォルト値が代入された上でレコードがテーブル側にINSERTされます。
LOAD DATA ... INFILE 構文 - SHOW WARNINGSについて
LOAD DATA 実行中に発生した Warning は、処理終了後に SHOW WARNINGS を実施する事で詳細内容を参照できます：
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'id' at row 1 |
| Warning | 1261 | Row 1 doesn't contain data for all columns |
| Warning | 1265 | Data truncated for column 'id' at row 2 |
| Warning | 1261 | Row 2 doesn't contain data for all columns |
+---------+------+--------------------------------------------+
4 rows in set (0.00 sec)
デフォルトでは最高で64個の Warning を参照可能な設定になっていますが、このMAX表示数／記憶数は環境変数 max_error_count にて任意の数を設定可能です：
mysql> set max_error_count = 999;
Query OK, 0 rows affected (0.00 sec)
mysqlimport - SYNTAX
shell> mysqlimport [options] db_name textfile1 [textfile2 ...]
[options] で指定可能なオプション値については MySQL.com Documentation - mysqlimport の項 を直接参照してください。LOAD DATA ... INFILE 構文で指定できた事はほぼ同様に指定可能です。それらに加えてユーザID指定だとかパスワード指定だとかのオプション値が加わっている感じです。
For each text file named on the command line, mysqlimport strips any extension from the filename and uses the result to determine the name of the table into which to import the file's contents. For example, files named patient.txt, patient.text, and patient all would be imported into a table named patient.
とあるように、引数で指定するテキストファイル名／パス が、そのままインポート先のテーブル名になっている必要があります。これは LOAD DATA ... INFILE構文 に比べると若干使い勝手が悪いと思える点かもしれません。
超基本的な使い方例:
shell> mysqlimport -u hoge -p -L test_db table_hoge.txt
mysqlimport - --replace, --ignore オプションの挙動について
MySQL.com Documentation - mysqlimport ページの記述では少々言葉足らずな感がありますが、自分の環境で実際にテストしてみたところ、LOAD DATA ... INFILE 構文での REPLACE | IGNORE オプションと挙動はまったく同じでした。-L (--local) オプションの有無によって挙動が異なる点についても同様なので、LOAD DATA ... INFILE 構文側の解説を参照してください。
mysqlimport - 複合キーのテーブルにインポート出来ない？
MySQL.com Documentation - mysqlimport のページ下部にあるユーザコメント投稿にて、こんな報告がありました：
Posted by Tanveer Ahmed on June 19 2004 1:31pm
It doesn't work with composite keys. If you have a table with two keys, mysqlimport will skip all the records from file that match with atleast one of the keys in existing records. It could be a better idea to have a parameter to specify the keys.
インポート先のテーブルの主キーが、複数カラムから構成されている場合だと正しくインポートできないぜ〜って報告なのですが・・・ 自分の環境（MySQL4.1.10）にて実際にテストしてみたところ、特に問題なくインポートできていました。バグを直してくれたのか、それとも報告自体が勘違いだったのか・・・よく分かりませんが、とりあえず 4.1.10 では複合キーのテーブルに対しても問題なくインポートできています。
参照情報
MySQL.com Documentation - LOAD DATA ... INFILE 構文について
MySQL.com Documentation - mysqlimport コマンドについて
O'REILLY社 - MySQLクックブック vol.1 - Paul Dubois著、赤井誠+他 訳