データ移行や初期データ投入の場面では、こういった状況によく直面します。数万行のINSERT文を実行するのは時間がかかるうえ、1行ずつトランザクションを発生させるためサーバーへの負荷も大きくなります。
MySQLには、CSVファイルをテーブルへ直接流し込む
LOAD DATA INFILEという構文があります。この記事では、RHEL 9系／Ubuntu 22.04 LTS上のMySQL 8.0を想定環境として説明します。LOAD DATA INFILEの基本構文から、secure_file_priv（セキュアファイル制限）の設定確認、FIELDS・LINES・IGNOREオプションの指定方法、IGNORE／REPLACEによる重複行の処理、文字化けを防ぐCHARACTER SET指定、そして運用でよく引っかかるエラーの対処まで解説します。この記事のポイント
・LOAD DATA INFILEはINSERT文より大幅に高速でCSVを取り込める
・secure_file_privの確認とCSV配置場所が動作の大前提になる
・FIELDS／LINES／IGNOREでCSVの書式に合わせた取り込みができる
・IGNOREとREPLACEで重複行の処理方法を使い分ける
でも安心してください。プロのエンジニアはコマンドを暗記していません。
「現場で使える型」を効率よく使いこなしているだけです。
なぜLOAD DATA INFILEを使うのか（INSERTとの速度差）
MySQLでCSVデータをテーブルへ取り込む方法はいくつかあります。最もシンプルなのはINSERT文ですが、行数が増えると次の問題が起きます。・1行ごとにサーバーとの通信が発生する（ネットワークオーバーヘッド）
・行ごとにインデックスの更新処理が走る
・AUTO_COMMITが有効だと1行ごとにディスクへの書き込みが発生する
LOAD DATA INFILEはこれらの非効率を解消するために設計されたコマンドです。ファイルの内容を一括で読み込んでパースし、テーブルへ流し込むため、INSERT文に比べてインポート速度が10倍以上になるケースも珍しくありません。MySQLの公式ドキュメントでも「INSERTより20倍速い」と記載されているほど、大量データの取り込みに向いた構文です。一方、LOAD DATA INFILEにはファイルの置き場所に制限があるという注意点があります。MySQL 8.0では
secure_file_priv変数でアクセス可能なディレクトリを制限しているため、指定外のパスにファイルを置いても「ERROR 1290」が出て動きません。この設定の確認が最初のステップになります。事前準備：secure_file_privの確認とCSVファイルの配置
LOAD DATA INFILEを使うには、まずsecure_file_privの設定を確認します。このシステム変数は「MySQLがファイルの読み書きを許可するディレクトリ」を指定するものです。MySQLクライアントにログインして次のコマンドを実行します。
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+-----------------------+ | Variable_name | Value | +------------------+-----------------------+ | secure_file_priv | /var/lib/mysql-files/ | +------------------+-----------------------+ 1 row in set (0.00 sec)
・パスが表示される → そのディレクトリにのみファイルを配置できる
・空文字（''） → ディレクトリ制限なし（セキュリティ上は推奨しない）
・
NULL → LOAD DATA INFILEが無効になっているRHEL/CentOS系ではデフォルトが
/var/lib/mysql-files/になっていることが多いです。インポートしたいCSVファイルは、SHOW VARIABLESで表示されたディレクトリへ配置します。# CSVファイルをsecure_file_privのディレクトリへコピーする # cp /path/to/data.csv /var/lib/mysql-files/ # chmod 644 /var/lib/mysql-files/data.csv
secure_file_privを空にして制限を外すことは推奨しません。本番環境では必ず指定ディレクトリを使うか、後述のLOAD DATA LOCAL INFILEを検討してください。基本構文と最小構成のインポート手順
1. テーブルを作成する
まず、取り込み先となるテーブルを用意します。ここでは次のような従業員データのCSVを例に使います。# employees.csv の内容例（ヘッダーなし） # 1001,山田太郎,sales,500000 # 1002,佐藤花子,engineering,600000 # 1003,鈴木次郎,sales,550000
mysql> CREATE TABLE employees ( -> emp_id INT NOT NULL PRIMARY KEY, -> name VARCHAR(100), -> dept VARCHAR(50), -> salary INT -> ); Query OK, 0 rows affected (0.03 sec)
2. CSVファイルを配置する
# CSVをsecure_file_privのディレクトリへ配置する # cp employees.csv /var/lib/mysql-files/ # chmod 644 /var/lib/mysql-files/employees.csv # ls -la /var/lib/mysql-files/employees.csv
-rw-r--r-- 1 root root 87 Nov 3 10:00 /var/lib/mysql-files/employees.csv
3. LOAD DATA INFILEを実行する
mysql> LOAD DATA INFILE '/var/lib/mysql-files/employees.csv' -> INTO TABLE employees -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n'; Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
・
Records：取り込まれた行数・
Skipped：IGNOREオプションで重複スキップした行数・
Warnings：型の不一致などで値が変換された行数（0が望ましい）MySQLを含むLinuxサーバー上のデータベース運用を体系的に学びたい方には、Linux Master Pro Seminarのカリキュラムが参考になります。バックアップ・レプリケーション・トラブルシュートをハンズオン形式で習得できます。
書式オプションの指定（FIELDS・LINES・IGNORE）
実際のCSVは「カンマ区切り」だけでなく、ダブルクォートで囲まれたフィールドや、Windowsで作成されたCRLF改行など、さまざまな書式があります。これらをLOAD DATA INFILEに正確に伝えるオプションを見ていきます。1. FIELDS TERMINATED BYで区切り文字を指定する
カンマ区切りの場合はFIELDS TERMINATED BY ','を指定します。タブ区切り（TSV）ならFIELDS TERMINATED BY '\t'にします。# タブ区切りTSVのインポート例 mysql> LOAD DATA INFILE '/var/lib/mysql-files/employees.tsv' -> INTO TABLE employees -> FIELDS TERMINATED BY '\t' -> LINES TERMINATED BY '\n'; Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
2. FIELDS ENCLOSED BYで囲み文字を指定する
フィールドがダブルクォートで囲まれている場合はOPTIONALLY ENCLOSED BY '"'を指定します。OPTIONALLYを付けると、囲まれていないフィールドも許容するので実務上は安全です。# 例：1001,"山田太郎","sales",500000 のようなCSV mysql> LOAD DATA INFILE '/var/lib/mysql-files/employees.csv' -> INTO TABLE employees -> FIELDS TERMINATED BY ',' -> OPTIONALLY ENCLOSED BY '"' -> LINES TERMINATED BY '\n'; Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
3. LINES TERMINATED BYで改行コードを指定する
Linux環境で作成したCSVの改行コードは\n（LF）ですが、Windows環境で作成したCSVは\r\n（CRLF）になっています。WindowsのCSVをインポートする際にLINES TERMINATED BYの指定を変えないと、行の末尾に\rが残って意図しない値になることがあります。# Windows CSV（CRLF改行）のインポート例 mysql> LOAD DATA INFILE '/var/lib/mysql-files/employees_win.csv' -> INTO TABLE employees -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\r\n'; Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
# fileコマンドで改行コードを確認する # file /var/lib/mysql-files/employees_win.csv
/var/lib/mysql-files/employees_win.csv: ASCII text, with CRLF line terminators
dos2unixコマンドでLFに変換してからインポートすると、LINES TERMINATED BYの指定が不要になって安全です。# dos2unixをインストールして改行コードを変換する（RHEL/CentOS系） # yum install -y dos2unix # dos2unix /var/lib/mysql-files/employees_win.csv
4. IGNORE N LINESでヘッダー行をスキップする
CSVの1行目がカラム名のヘッダー行になっている場合はIGNORE 1 LINESでスキップします。# 1行目がヘッダー行（emp_id,name,dept,salary）の場合 mysql> LOAD DATA INFILE '/var/lib/mysql-files/employees.csv' -> INTO TABLE employees -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n' -> IGNORE 1 LINES; Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
重複行の処理：IGNOREとREPLACEの違い
テーブルにPRIMARY KEYやUNIQUEインデックスが設定されている場合、既存データと重複するキーを持つ行を取り込もうとするとエラーになります。この挙動を制御するのがIGNOREとREPLACEの2つのオプションです。IGNOREオプション：重複するキーを持つ行が来た場合、その行をスキップします。既存データを保護したい場合に使います。
# 重複行はスキップして取り込む mysql> LOAD DATA INFILE '/var/lib/mysql-files/employees.csv' -> IGNORE -> INTO TABLE employees -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n' -> IGNORE 1 LINES; Query OK, 2 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 1 Warnings: 0
# 重複行は既存レコードを削除して上書きする mysql> LOAD DATA INFILE '/var/lib/mysql-files/employees.csv' -> REPLACE -> INTO TABLE employees -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n' -> IGNORE 1 LINES; Query OK, 5 rows affected (0.01 sec) Records: 3 Deleted: 2 Skipped: 0 Warnings: 0
文字化けを防ぐCHARACTER SET指定
MySQLのデフォルト文字セットをutf8mb4に設定していても、CSVファイル自体がShift_JIS（cp932）やEUC-JPで保存されている場合、そのままインポートすると文字化けします。CSVの文字コードをMySQLに伝えるにはCHARACTER SET句を使います。# UTF-8（utf8mb4）のCSVをインポートする場合 mysql> LOAD DATA INFILE '/var/lib/mysql-files/employees.csv' -> CHARACTER SET utf8mb4 -> INTO TABLE employees -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n' -> IGNORE 1 LINES; Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
# Shift_JIS（cp932）のCSVをインポートする場合 mysql> LOAD DATA INFILE '/var/lib/mysql-files/employees_sjis.csv' -> CHARACTER SET cp932 -> INTO TABLE employees -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n' -> IGNORE 1 LINES; Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
# nkfコマンドで文字コードを確認する # nkf --guess /var/lib/mysql-files/employees.csv
UTF-8
よくあるエラーと対処法
1. ERROR 1290：secure_file_privの制限
エラーメッセージ：ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement原因：指定したCSVファイルのパスが
secure_file_privで許可されたディレクトリ外にある。対処：ファイルを
SHOW VARIABLES LIKE 'secure_file_priv';で確認したディレクトリへ移動する。どうしても任意パスを使いたい場合はLOCAL版を使う方法もありますが、セキュリティリスクを十分考慮してください。# クライアント側のファイルを読み込むLOCAL版（--local-infileが必要） # mysql --local-infile=1 -u root -p mysql> SET GLOBAL local_infile = 1; mysql> LOAD DATA LOCAL INFILE '/home/user/employees.csv' -> INTO TABLE employees -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n' -> IGNORE 1 LINES; Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
2. ERROR 13：Permission denied
エラーメッセージ：ERROR 13 (HY000): Can't get stat of '/var/lib/mysql-files/employees.csv' (Errcode: 13 - Permission denied)原因：CSVファイルのパーミッションがMySQLプロセス（mysqldユーザー）から読めない設定になっている。
対処：ファイルに読み取り権限を付与する。
# ファイルのパーミッションを確認する # ls -la /var/lib/mysql-files/employees.csv # 読み取り権限がない場合は付与する # chmod 644 /var/lib/mysql-files/employees.csv
3. 行末に余計な文字が混入する（改行コードの不一致）
症状：最後のカラムの値に\r（CR）が混じり、VARCHAR型のフィールドで余分なバイトが入る。原因：WindowsのCRLF改行ファイルに
LINES TERMINATED BY '\n'のみを指定している。対処：LINES TERMINATED BYを
'\r\n'に変更するか、インポート前にdos2unixで変換する。4. Warningsが出て一部データが壊れる
症状：「Warnings: N」が表示され、想定と違う値が入る。原因：CSV内のデータ型とテーブルのカラム定義が合っていない（文字列が数値列に入った等）。
対処：
SHOW WARNINGS;で詳細を確認し、テーブル定義またはCSVの内容を見直す。mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------+ | Warning | 1265 | Data truncated for column 'salary' at row 2 | +---------+------+---------------------------------------------------+ 1 row in set (0.00 sec)
本記事のまとめ
LOAD DATA INFILEのよく使う構成パターンをまとめます。| やりたいこと | 構文・指定方法 |
|---|---|
| カンマ区切りCSVを取り込む（基本） | LOAD DATA INFILE '/path/data.csv' INTO TABLE t FIELDS TERMINATED BY ',' |
| ダブルクォート囲みに対応する | LOAD DATA INFILE '/path/data.csv' INTO TABLE t FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' |
| Windows CSV（CRLF改行）に対応する | LOAD DATA INFILE '/path/data.csv' INTO TABLE t LINES TERMINATED BY '\r\n' |
| ヘッダー行をスキップする | LOAD DATA INFILE '/path/data.csv' INTO TABLE t LINES TERMINATED BY '\n' IGNORE 1 LINES |
| 重複行をスキップする | LOAD DATA INFILE '/path/data.csv' IGNORE INTO TABLE t FIELDS TERMINATED BY ',' |
| 重複行を上書きする | LOAD DATA INFILE '/path/data.csv' REPLACE INTO TABLE t FIELDS TERMINATED BY ',' |
| 文字セットを指定する | LOAD DATA INFILE '/path/data.csv' CHARACTER SET utf8mb4 INTO TABLE t |
| クライアント側のファイルを読む | LOAD DATA LOCAL INFILE '/path/data.csv' INTO TABLE t FIELDS TERMINATED BY ',' |
LOAD DATA INFILEは大量データのインポートに欠かせない実務スキルです。
secure_file_privの設定確認と、FIELDS／LINES句によるCSV書式の指定をマスターすれば、本番環境への移行作業や定期的なデータ投入を効率よく行えるようになります。
現場で通用する安全なLinuxサーバー構築の「型」を体系的に身につけたい方へ、MySQLを含むLinuxサーバー運用の全体像を2日間のハンズオン形式で学べる機会があります。
3,100名以上が実践した「型」を無料で公開中
プロのエンジニアはコマンドを暗記していません。
「現場で使える型」を効率よく使いこなしているだけです。
その「型」を図解60Pにまとめた入門マニュアルを、完全無料でプレゼントしています。
姓・名・メールの3つだけ／30秒／解除は3秒 ／ 詳細はこちら
- 前のページへ：PostgreSQLのパーティショニングを設定する方法｜RANGE・LIST・HASHパーティション作成と性能改善の手順
- この記事の属するカテゴリ：データーベース管理へ戻る
無料メルマガで学習を続ける
Linuxの実践スキルをメールで毎週お届け。
登録は30秒、解除もいつでも可。
登録無料・いつでも解除できます