Table of contents
MySQLでTSVファイルをインポートすることは非常に便利な機能です。TSV(Tab Separated Values)はスプレッドシートのようなテーブル形式のデータを、タブ文字で区切って記述したテキストファイル形式です。データをTSVファイルに保存しておけば、MySQLにスムーズにインポートできます。
この記事では、MySQLにTSVファイルをインポートする手順を、簡単な実例を交えて分かりやすく解説したいと思います。
準備
まずは、適当なTSVファイルを用意しましょう。ここではサンプルとして、以下のようなusers.tsv
ファイルを作成します。このファイルには名前、メールアドレス、年齢のデータが含まれています。
name email age
John Doe john@example.com 32
Jane Smith jane@example.net 28
Bob Roberts bob@example.org 45
次に、MySQLにテーブルを作成します。カラム名と型がTSVファイルに合わせてあります。
CREATE TABLE users (
name VARCHAR(50),
email VARCHAR(50),
age INT
);
TSVファイルのインポート
ファイルのインポートには、LOAD DATA INFILE
文を使用します。このコマンドの書式は以下の通りです。
LOAD DATA INFILE 'ファイルパス'
INTO TABLE テーブル名
FIELDS TERMINATED BY 'フィールド区切り文字'
ENCLOSED BY ''
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
実際にusers.tsv
をインポートするコマンドは以下のようになります。
LOAD DATA INFILE '/path/to/users.tsv'
INTO TABLE users
FIELDS TERMINATED BY '\t'
ENCLOSED BY ''
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
注目すべき点は以下の通りです。
FIELDS TERMINATED BY '\t'
: フィールドの区切りがタブ文字(\t
)であることを指定ENCLOSED BY ''
: フィールドがクォート記号で囲まれていないことを指定LINES TERMINATED BY '\n'
: 行の終端がネクスト記号(\n
)であることを指定IGNORE 1 ROWS
: ファイルの最初の1行をスキップすることを指定(ヘッダ行を無視)
インポートが成功すれば、users
テーブルに新しいレコードが追加されているはずです。
SELECT * FROM users;
+------------+----------------+------+
| name | email | age |
+------------+----------------+------+
| John Doe | john@example.com | 32 |
| Jane Smith | jane@example.net | 28 |
| Bob Roberts| bob@example.org | 45 |
+------------+----------------+------+
このように、LOAD DATA INFILE
を使えば、TSVファイルからMySQLにスムーズにデータをインポートできます。ファイル形式の指定さえ正しければ、大量のデータを手軽にインポートすることができます。
データのエクスポートやインポートは、データベース運用で欠かせない作業です。TSVなどのシンプルなフォーマットを利用すれば、柔軟なデータ移行が可能になります。MySQLのインポート機能を上手に活用してみてくださいね。
MySQLでTSVファイルをインポートする際の追加ポイント
ロケール設定
TSVファイルのエンコーディングがデフォルトのロケール設定と異なる場合は、LOAD DATA INFILE
の実行時にエラーが発生する可能性があります。この問題を回避するには、一時的にロケール設定を変更します。
SET NAMES 'エンコーディング名';
例えば、TSVファイルがUTF-8エンコーディングの場合:
SET NAMES 'utf8mb4';
LOAD DATA INFILE 'users.tsv' INTO TABLE users {...};
インポート元ファイルのパス
LOAD DATA INFILE
でファイルパスを指定する際は、絶対パスかサーバー上の相対パスを使う必要があります。ただしセキュリティ上の理由で、この文の使用にはMySQLの特権が必要となります。
LOAD DATA INFILE '/var/local/users.tsv' INTO TABLE users {...};
--local-infileオプション
MySQLサーバーの--local-infile
オプションが無効になっていると、LOAD DATA INFILE
が使えなくなります。この場合、オプションを有効にするかクライアント側からのインポートを検討する必要があります。
NULL値の指定
TSVファイル内の空のフィールドをNULLとして扱いたい場合は、LOAD DATA
のFIELDS
句でNULL指定文字
を追加します。
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '' NULL DEFINED AS '\N'
ここでは\N
がNULLを表す文字列として指定されています。
インポート時の整合性チェック
LOAD DATA INFILE
では、一般的に制約チェック(NOT NULLなど)はスキップされます。予期せぬデータがインポートされるリスクを避けるには、ステージングテーブル経由でデータを移動し、移動先テーブルで制約チェックすることをお勧めします。
CREATE TEMPORARY TABLE stage LIKE users;
LOAD DATA INFILE 'users.tsv' INTO TABLE stage {...};
INSERT INTO users SELECT * FROM stage WHERE 条件...;
トランザクションの利用
LOAD DATA INFILE
はトランザクション対象外のステートメントですが、ロールバック可能なトランザクション内で実行すれば、データのインポートをトランザクション単位でコミットまたはロールバックできます。
START TRANSACTION;
LOAD DATA INFILE 'users.tsv' INTO TABLE users {...};
-- 他の処理...
COMMIT;
TSVファイルのインポートは基本的な機能ですが、細かいオプションや注意点も存在します。状況に合わせて適切な設定を行うことで、よりスムーズでデータ整合性の高いインポートが可能になります。