技術資料

ファイルに入っているデータをMySQLへ読み込みたいとき、LOAD DATA LOCAL INFILEが使えます。テストデータを用意するときなどに、行ごとにINSERT文を書かずに投入できるので便利です。

ただ、サーバー側の設定を有効にしただけでは、読み込めないことがあります。LOCALを使うには、サーバーとクライアントの両方で許可が必要です。

今回は、基本の書き方と設定の確認方法、3行だけのファイルを一時テーブルへ読み込む手順を整理します。説明の対象はMySQL 8.0系、操作はWindowsのPowerShellとMySQLのmysqlコマンドを想定しています。

LOAD DATA LOCAL INFILEの基本

基本的には、読み込むファイルと、投入先のテーブルを指定します。テーブルは先に作っておきます。

LOAD DATA LOCAL INFILE 'ファイルの絶対パス'
INTO TABLE table_name
CHARACTER SET ascii
LINES TERMINATED BY '\r\n'
(id);

この例は、ASCIIのファイルからCRLFで区切られた各行を読み、id列へ入れる形です。実際には、ファイルの文字コード、列の区切り、改行に合わせて指定を変えます。CSVなら列の区切りや引用符の指定も必要になるので、まずは1列だけのファイルで流れを確認してみます。

LOCALの有無で、ファイルを読む場所が変わります。

ここでいう「ローカル」は、MySQLサーバーではなく、クライアントから見たローカルです。手元のmysqlコマンドから実行するなら、手元のPCにファイルを置きます。詳しい構文は、MySQL公式のLOAD DATAの説明を参照してください。

サーバー側の設定を確認する

以降は、信頼できるローカルの検証用MySQLと、検証用DBを使う想定です。本番DBや既存の業務テーブルでは試さず、接続設定のホスト・ポート・DB名を確認してから進めてください。Workbenchでの接続やSQL実行の基本は、MySQL Workbench入門の記事でも説明しています。

接続後に、バージョン、DB名、SQLモードと、サーバー側の設定を確認します。

SELECT VERSION(), DATABASE(), @@session.sql_mode;

SHOW GLOBAL VARIABLES LIKE 'local_infile';

local_infileがONなら、サーバー側ではLOCALを許可しています。OFFなら、検証環境で変更してよいことを確認したうえで、権限のあるユーザーが次を実行します。元の値は控えておきましょう。

SET GLOBAL local_infile = ON;

SHOW GLOBAL VARIABLES LIKE 'local_infile';

これは自分の接続だけの変更ではなく、サーバー全体に影響します。local_infileの公式説明でも、Globalの設定として定義されています。

MySQL 8.0では、グローバル変数の変更に原則としてSYSTEM_VARIABLES_ADMIN、または非推奨のSUPER権限が必要です。権限エラーが出たら管理者へ確認し、読み込みのためにアプリ用ユーザーへ管理権限を追加することは避けます。詳細は、システム変数の変更権限を確認してください。

SET GLOBALは実行中の値を変更するもので、起動設定を保存する操作ではありません。再起動後も使う設定との違いは、システム変数の設定方法に説明されています。今回は確認に必要な間だけ有効にする想定です。

クライアント側でもLOCALを許可する

次は、データを投入するクライアント側です。MySQLのmysqlコマンドでは、接続時に--local-infile=1を指定します。

次のapp_userとapp_dbは、それぞれ検証用のユーザー名・DB名に置き換えてください。ポートも接続先に合わせます。

mysql --host=127.0.0.1 --port=3306 --user=app_user --password --local-infile=1 app_db

パスワードは表示されたプロンプトで入力します。コマンドへ直接書く必要はありません。既にmysqlコマンドで接続していた場合は、その接続を終えて、このオプションを指定して接続し直します。

この指定は、そのmysqlコマンドの接続に対するものです。mysqlで読み込めても、WorkbenchやPHPの接続が同じ設定になるわけではありません。利用するクライアントごとに許可を確認します。オプションの説明は、mysqlクライアントの公式資料を参照してください。

信頼できる接続先に限定する

LOCALではクライアントがファイルを読み、サーバーへ送ります。不正なサーバーから、指定したものとは別のファイルを要求されるリスクもあるため、LOCALを許可したクライアントで信頼できないサーバーへ接続しないでください。

また、SQLにファイル名を書くだけで、読み込み可能なディレクトリを制限できるわけではありません。必要な対策は、LOAD DATA LOCALのセキュリティ上の考慮事項を確認してください。

3行のファイルを一時テーブルへ読み込む

確認用ファイルを作る

別のPowerShellを開き、書き込み可能な専用フォルダーにファイルを用意します。以下のC:\mysql_testは例なので、環境に合わせて変更してください。変更した場合は、後のSQLのパスも合わせます。

New-Item -ItemType Directory -Path 'C:\mysql_test' -Force

$loadTestPath = 'C:\mysql_test\load_test.txt'
$loadTestBytes = [System.Text.Encoding]::ASCII.GetBytes("1`r`n2`r`n3`r`n")
$loadTestStream = [System.IO.File]::Open(
    $loadTestPath,
    [System.IO.FileMode]::CreateNew,
    [System.IO.FileAccess]::Write
)
try {
    $loadTestStream.Write($loadTestBytes, 0, $loadTestBytes.Length)
}
finally {
    $loadTestStream.Dispose()
}

内容は1・2・3の3行で、ASCII、改行はCRLFです。CreateNewを使っているので、同名ファイルが既にあればエラーになり、上書きしません。エラーが出た場合は続けず、既存ファイルを確認するか別名を使ってください。

同じ接続内でテーブルを作ってロードする

LOCALを許可して接続したmysqlコマンドへ戻り、検証用DBが選択されていることを確認します。ここからのSQLは、各操作の成功を確認しながら順に実行してください。

SELECT DATABASE(), @@session.sql_mode;

CREATE TEMPORARY TABLE local_infile_check (
    id INT NOT NULL
);

この接続でlocal_infile_checkという一時テーブルをまだ作っていないことを確認して実行します。作成に失敗した場合は、ロードへ進まないでください。

一時テーブルの作成には、検証用DBに対するCREATE TEMPORARY TABLES権限が必要です。一時テーブルはその接続でのみ使え、接続を閉じると削除されます。作成後に切断した場合は、再接続して作成からやり直します。詳しくは、MySQL公式の一時テーブルの説明を参照してください。

続けてファイルを読み込み、その直後に警告を確認します。

LOAD DATA LOCAL INFILE 'C:/mysql_test/load_test.txt'
INTO TABLE local_infile_check
CHARACTER SET ascii
LINES TERMINATED BY '\r\n'
(id);

SHOW WARNINGS;

SQLのパスでは、Windowsでもスラッシュを使えます。ここではバックスラッシュのエスケープを避けるため、この形にしています。

なお、上の改行指定は、SQLモードにNO_BACKSLASH_ESCAPESが含まれていない前提です。このモードがあると文字列中のバックスラッシュの解釈が変わるので、そのまま実行せず、改行の指定方法を合わせてください。文字列の扱いは、MySQL公式の文字列リテラルの説明で確認できます。

警告・件数・値を確認する

SHOW WARNINGSは、別のSELECTなどを挟まず、LOAD DATAの直後に実行します。後からでは確認したいSQLの警告が残っていない場合があるためです。使い方は、SHOW WARNINGSの公式説明も参照してください。

LOCALでは、REPLACEを付けない場合、入力値の解釈エラーが警告になってロードが続くことがあります。「SQLが成功したから全部正しい」とは判断せず、警告が出たら内容を確認します。

警告を確認したら、件数と値を調べます。

SELECT COUNT(*) AS loaded_rows
FROM local_infile_check;

SELECT id
FROM local_infile_check
ORDER BY id;

今回の入力ファイルから求める期待結果は、次のとおりです。MySQLで採取した実行結果ではありません。

• LOAD DATAでエラーが発生せず、直後のSHOW WARNINGSに警告がない
• loaded_rowsが3
• idの値が、並び順どおりに1・2・3の3行

この例には主キーを付けていないので、ロードだけをもう一度実行すると同じ3行が追加されます。やり直す場合は、自分がこの確認のために作った一時テーブルを削除して作り直し、空の状態からロードします。

読み込めない場合の確認ポイント

エラー番号や文面はバージョン・クライアントによって異なるため、表示されたメッセージを残して確認します。

LOCALを使うロードには、サーバー上のファイルを読むためのFILE権限は不要です。また、secure_file_privはLOCALなしのサーバー側ファイル読み込みに関する制限なので、LOCALが拒否されたときに、まずこの設定を緩めるという対応はしません。LOCALの許可と、クライアントがファイルを読めるかを先に確認します。

確認後は作成物と設定を片付ける

同じ接続内で、今回作成した一時テーブルを削除します。TEMPORARYを付けることで、通常のテーブルを削除する操作と区別します。

DROP TEMPORARY TABLE local_infile_check;

自分が今回作ったファイルも、PowerShellから削除します。パスを変えていた場合は、そのファイルだけを指定してください。

Remove-Item -LiteralPath 'C:\mysql_test\load_test.txt'

サーバーのlocal_infileが元はOFFで、今回の確認のためにONへ変更した場合は、他の利用への影響を確認してから戻します。元からONだった場合は、この操作は不要です。

SET GLOBAL local_infile = OFF;

SHOW GLOBAL VARIABLES LIKE 'local_infile';

最後にLOCALを許可していたmysqlコマンドの接続を終了します。次回LOCALが不要なら、--local-infile=1を付けずに接続します。ほかのクライアントで接続設定を変更した場合も、今後必要なければ元へ戻しておきましょう。

LOAD DATA LOCAL INFILEは、ファイルからデータを入れる手段として便利です。サーバー側とクライアント側の設定を分けて確認し、最初は小さなファイルで、警告・件数・値まで確かめるところから始めるとよいかと思います。