MySQLの「load data LOCAL infile」の「LOCAL」って何？どこ？

MySQLの「load data LOCAL infile」は強力です。
CSVを驚きのスピードでインポートすることができ、かつ
レンタルサーバーなど制約が厳しい環境に大きなデータを取り込むことを可能にしてくれます。
そんな「load data LOCAL infile」の「LOCAL」ってそもそも何よ？
という点を理解しないと、せっかく便利な機能があってもエラーに悩みます。
ここではそのLOCALについてご紹介します。

1 MySQLの「load data LOCAL infile」の「LOCAL」って何？
- 1.1 PC端末上に、MySQL,Apacheがサービスとして稼働しているテスト環境の場合
- 1.2 レンタルサーバー上のMySQL,Apacheに対してインポートする場合

MySQLの「load data LOCAL infile」の「LOCAL」って何？

普通、LOCALと聞いて何を思いますか。
「自分が今操作しているPCがローカルだろ」が普通だと思います。

では、まず１つの例から。

基本的に以下でご紹介したインポートを例にご紹介しています。

データインポートSQL実行文(load data LOCAL infile)・法人の基本３情報のデータ(法人番号)

PC端末上に、MySQL,Apacheがサービスとして稼働しているテスト環境の場合

WordPressなどを運用していて、カスタマイズを行うような場合、
ローカル環境にApacheが存在して、localhostや127.0.0.1などでブログを表示し、
カスタマイズをしている場合などがこのケースです。

正にローカル環境ですので、
MySQLへのインポートもmysqldumpなどでサクッとできるかもしれません。
でも、元データがCSVだったりするとちょっと面倒だったりします。

そんな時は「load data LOCAL infile」で幸せになれるかもしれません。

こんな感じです。
実行環境はWindows8 x64, Apache2.4×64, MySQL5.6 Win64です。

load data LOCAL infile "H:/subversion/（などなど）/std3info_sjis/01_hokkaido_all_20180629.csv" 
into table gov_nta_houjin_bangou_cp932 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '' 
LINES STARTING BY '' TERMINATED BY '\n' 
IGNORE 0 LINES 
(sequenceNumber, corporateNumber, process, correct, updateDate, changeDate, name, nameImageId, kind, prefectureName,cityName, streetNumber, addressImageId, prefectureCode, cityCode, postCode, addressOutside, addressOutsideImageId,closeDate, closeCause, successorCorporateNumber, changeCause, assignmentDate, latest, enName, enPrefectureName, enCityName, enAddressOutside, furigana);

load data LOCAL infile "H:/subversion/（などなど）/std3info_sjis/01_hokkaido_all_20180629.csv"

into table gov_nta_houjin_bangou_cp932

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY ''

LINES STARTING BY '' TERMINATED BY '\n'

IGNORE 0 LINES

(sequenceNumber, corporateNumber, process, correct, updateDate, changeDate, name, nameImageId, kind, prefectureName,cityName, streetNumber, addressImageId, prefectureCode, cityCode, postCode, addressOutside, addressOutsideImageId,closeDate, closeCause, successorCorporateNumber, changeCause, assignmentDate, latest, enName, enPrefectureName, enCityName, enAddressOutside, furigana);

これは分かりやすく、すべてがローカルに揃っている為、
インポートする対象のファイルも

「H:/subversion/（などなど）/std3info_sjis/01_hokkaido_all_20180629.csv」です。
当然「C:\hogehoge\01_hokkaido_all_20180629.csv」などでも問題ありません。

レンタルサーバー上のMySQL,Apacheに対してインポートする場合

さて、この場合のLOCALはどこか？です。

ヘルプには以下のように書かれています。

13.2.6 LOAD DATA INFILE 構文

13.2.6 LOAD DATA INFILE 構文
MySQL

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [PARTITION (partition_name,...)] [CHARACTER SET charset_name] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number {LINES | ROWS}] [(col_name_or_user_var,...)] [SET col_name = expr,...]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
 [REPLACE | IGNORE]
 INTO TABLE tbl_name
 [PARTITION (partition_name,...)]
 [CHARACTER SET charset_name]
 [{FIELDS | COLUMNS}
 [TERMINATED BY 'string']
 [[OPTIONALLY] ENCLOSED BY 'char']
 [ESCAPED BY 'char']
 ]
 [LINES
 [STARTING BY 'string']
 [TERMINATED BY 'string']
 ]
 [IGNORE number {LINES | ROWS}]
 [(col_name_or_user_var,...)]
 [SET col_name = expr,...]

LOAD DATA INFILE ステートメントは、非常に高速にテキストファイルからテーブルに行を読み取ります。LOAD DATA INFILE は、SELECT ... INTO OUTFILE を補完するものです。(セクション13.2.9.1「SELECT … INTO 構文」を参照してください。)テーブルからファイルにデータを書き込むには、SELECT ... INTO OUTFILE を使用します。そのファイルをテーブルに読み戻すには、LOAD DATA INFILE を使用します。FIELDS および LINES 句の構文は、両方のステートメントで同じです。どちらの句もオプションですが、両方が指定される場合は、FIELDS を LINES の前に指定する必要があります。

（中略）

LOCAL キーワードは、あとで説明されているように、ファイルの予測される場所やエラー処理に影響を与えます。LOCAL は、サーバーとクライアントの両方がそれを許可するように構成されている場合にのみ機能します。たとえば、mysqld が --local-infile=0 で起動された場合、LOCAL は機能しません。セクション6.1.6「LOAD DATA LOCAL のセキュリティーの問題」を参照してください。

LOCAL キーワードは、ファイルが見つかることが予測される場所に影響を与えます。

LOCAL が指定されている場合、ファイルはクライアントホスト上のクライアントプログラムによって読み取られ、サーバーに送信されます。このファイルは、その正確な場所を指定するためにフルパス名として指定できます。相対パス名として指定されている場合、その名前は、クライアントプログラムが起動されたディレクトリを基準にして解釈されます。LOCAL を LOAD DATA とともに使用している場合は、そのファイルのコピーがサーバーの一時ディレクトリ内に作成されます。これは tmpdir または slave_load_tmpdir の値によって決定されるディレクトリではなく、オペレーティングシステムの一時ディレクトリであり、MySQL Server では構成できません。(システムの一時ディレクトリは通常、Linux システムでは /tmp、Windows では C:\WINDOWS\TEMP です。)このディレクトリ内にコピーのための十分な領域がないと、LOAD DATA LOCAL ステートメントが失敗する場合があります。

LOCAL が指定されていない場合、ファイルはサーバーホスト上にある必要があり、直接サーバーによって読み取られます。サーバーは、次のルールを使用してファイルを見つけます。

ファイル名が絶対パス名である場合、サーバーはそれを指定されたとおりに使用します。

ファイル名が 1 つ以上の先行コンポーネントを含む相対パス名である場合、サーバーは、サーバーのデータディレクトリを基準にしてファイルを検索します。

先行コンポーネントを含まないファイル名が指定されている場合、サーバーは、デフォルトデータベースのデータベースディレクトリ内でそのファイルを探します。

LOCAL 以外のケースでは、これらのルールは、./myfile.txt という名前のファイルがサーバーのデータディレクトリから読み取られるのに対して、myfile.txt として指定されたファイルはデフォルトデータベースのデータベースディレクトリから読み取られることを示します。たとえば、db1 がデフォルトデータベースである場合、次の LOAD DATA ステートメントは、このステートメントが明示的に db2 データベース内のテーブルにファイルをロードしているにもかかわらず、db1 のデータベースディレクトリからファイル data.txt を読み取ります。

PHP

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

1

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

セキュリティー上の理由から、サーバー上にあるテキストファイルを読み取る場合、そのファイルはデータベースディレクトリ内に存在するか、またはすべてのユーザーから読み取り可能のどちらかである必要があります。また、サーバーファイルに対して LOAD DATA INFILE を使用するには、FILE 権限が必要です。セクション6.2.1「MySQL で提供される権限」を参照してください。LOCAL 以外のロード操作では、secure_file_priv システム変数が空以外のディレクトリ名に設定されている場合、ロードされるファイルはそのディレクトリ内に存在する必要があります。

LOCAL を使用すると、クライアントが接続を経由してファイルの内容をサーバーに送信する必要があるため、サーバーが直接ファイルにアクセスできるようにした場合より少し遅くなります。その一方で、ローカルファイルをロードするために FILE 権限は必要ありません。

（以下略）

（抜粋引用）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.6 LOAD DATA INFILE 構文

抜粋でもこの長さのヘルプなので嫌になりますが、
単純にLOCALを利用すると、現在実行している環境からファイルを取得してデータが送信され、
データベースに格納できると読み取れます。

ここで最初に思うのが、「C:\data.csvとかでもインポートできちゃう？マジか！？」です。

ですが、それは一旦、脆くも崩れ去ります。

外部アクセス可能なMySQLサーバーなら正に「C:\…」なローカルも可能

レンタルサーバーのMySQLが外部アクセスを認めている（許可できる）ような場合には、
外部からMySQLに接続して、SQLを直接発行できますので、

「これマジなんです」と言う事になりますが、

共有レンタルサーバーのMySQLで外部アクセスが可能なケースは稀です。

例えば、KAGOYAさんのMySQLプランとかは、それができますね。

ただ、許可IPアドレスを指定してアクセスしますので、自宅が固定グローバルIP環境などでなければ、
グローバルIPの割当てが変わる度に設定変更が必要です。
（ブレーカーが落ちたとか・・・グローバルIPが変わりますね）

外部データベースサーバーとして単独でご利用可能な単機能プラン

データベースプランでは、独立した専用のDBサーバーをご利用いただけます。現在ご利用中のサーバーにシステムをインストールする必要がありません。外部データベースサーバーとしてご利用いただいた場合、メインサーバーリソースの節約を実現し、CMSなどの利用時における動作速度の改善が期待できます。また、不正侵入予防システム（IPS）を標準装備していますので、お客様のデータを不正なアクセスや攻撃から守ります。

データベースプラン｜レンタルサーバーのカゴヤ・ジャパン　（アフィリエイトリンクではない）

私も１つ契約はしています。

ただ、やっぱり外部からアクセスすると、若干もっさりするのは致し方ないかなと。
でも、１つそういう自由な環境があると、

「最悪あそこに逃がせばいっか^^」と保険的に気持ちが楽だったりします。
月額500円が、実運用されないまま毎月流出していますが。。。

ただ、これでインポートしたいって事は、CSVファイルがデカいってケースなので、
SQL発行して、ファイルアップロードしてインポートって、
どことなくエラーになりそうな気がしますよね。

できればファイル転送は済ませておきたい。
正直な心情だと思います。

では、本題。

レンタルサーバー上のphpMyAdminやPHPファイルから実行した場合

レンタルサーバー上に設置したphpMyAdminやPHPファイルは、
当然ですがCMS(WordPressなど)を利用する場合がほとんどですから、MySQLにもアクセスできます。

ただ、この場合ApacheとMySQLがどのように構成されているかは知る由もありません。

同じ物理サーバー上に共存しているかもしれないし、

別の専用データベースサーバーにMySQLだけがあって、
Apacheとはネットワークで繋がっているだけかもしれない。

それでも、PHPからMySQLにはアクセスできる環境であることは確かです。

そして、この場合の「LOCAL」は、

PHPが実行されている環境＝Webサーバーを示します。

MySQLがどこにあろうと関係なく、接続ができてSQLの発行ができれば十分です。

あとは、Webサーバー上でSQLが実行できる環境があればいいという事になります。

自分でPHPを書いても良し。

phpMyAdminからSQLを発行してもいい。

それさえ押さえれば、CSVをFTPでアップロードして、そのファイルを一瞬でMySQLに投入できるのです。

ただし、サーバーによっては注意が必要です。

その phpMyAdmin はどこで実行されていますか？

です。

XSERVERであればコントロールパネルから事前に用意されたphpMyAdminが利用できます。

phpMyAdminにアクセスするためにURLが提供されていて、
クリックするだけで利用できます。（要ログイン）

その際のドメインは、、、「phpmyadmin******.xserver.jp」のような感じです。
ちなみに、MySQLにアクセスするドメインは「mysql******.xserver.jp」のような感じです。
※「******」部は同じ文字列ではありません、文字数もそれぞれ異なります。

そもそもphpMyAdminがどこにインストールされているのかが分からないのです。

ちなみにphpMyAdminからエクスポートする際に、
サーバー上にエクスポートする場合に選択するオプションでは、以下のように書かれています。

□ サーバ上のディレクトリ /var/lib/phpMyAdmin/save/ に保存する

Webサーバー上の自分が管理下にあるディレクトリでもありませんし、
そもそも、このディレクトリに対しての書き込み権限も、アクセス権もありません。

出力する事さえできないのです。

要はこのphpMyAdminがアクセス可能なローカルファイルに対して、
自分がアクセスすることができないので、このアプローチは利用できません。

※phpMyAdminから「public_html」はアクセスできるかもしれないし、できないかもしれない。

だったら、自前のPHPプログラムでやればいい

もしくは「自分でphpMyAdminをインストールすればいい」でも可という事です。

FTPでアクセスできて、ファイルまでのパスが通せて、SQLが発行できればいいわけですから。

自分はPHPでプログラムを書いて実行しましたが、問題なくインポートできました。

以下の様な感じです。

データインポートSQL実行文(load data LOCAL infile)・法人の基本３情報のデータ(法人番号)

<?php
$db = 'db';
$host = 'host';
$user = 'user';
$pass = 'pass';
$dir = "/dev/ntagojp_unicode/";   //csvをアップロードしたディレクトリ

$con = new mysqli($host, $user, $pass, $db);
$enc = 'utf8mb4';  //sjis, cp932などエンコードを指定

$con->query(" set character_set_connection = {$enc};");
$con->query(" set character_set_server = {$enc};");
$con->query(" set character_set_database = {$enc};");

$con->query(" set character_set_client = {$enc};");
$con->query(" set character_set_results = {$enc};");

$data_date = date("Ymd");
$data_date = "20180629";		//適宜上書きで日付指定
$pref = array( 0 => array('01', "hokkaido"));		//47都道府県＋海外データを適宜指定
$filename = "{$pref[0][0]}_{$pref[0][1]}_all_{$data_date}.csv";

//ヒアドキュメント構文は無用な\nが挿入されここでは使用しない。
$sql = " load data LOCAL infile '{$_SERVER['DOCUMENT_ROOT']}{$dir}{$filename}' into table gov_nta_corpnum_{$enc} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' ";
$sql .= " LINES STARTING BY '' TERMINATED BY ". "'\\n'" . " IGNORE 0 LINES ";
$sql .= " (sequenceNumber, corporateNumber, process, correct, updateDate, changeDate, name, nameImageId, kind, prefectureName,cityName, streetNumber, addressImageId, prefectureCode, cityCode, postCode, addressOutside, addressOutsideImageId,closeDate, closeCause, successorCorporateNumber, changeCause, assignmentDate, latest, enName, enPrefectureName, enCityName, enAddressOutside, furigana);";

$msql_result = $con->query($sql);
if( $msql_result === false ){
	$msg = "SQLの実行に失敗しました";
}else{
	$msg = "インポート終了";
}
$cnt_sql = "SELECT prefectureName, count(prefectureCode) FROM gov_nta_corpnum_utf8mb4 GROUP BY prefectureCode;";
$cntresult = $con->query($cnt_sql);
if($cntresult->num_rows > 0){
	while ($row = $cntresult->fetch_assoc()) {
		$arr[] = $row;
	}
}else{
	$arr = false;
}
echo $msg . '<br/>' .print_r($arr, true ) . '<br/><code>' . $sql . '</code>' ;

<?php

$db = 'db';

$host = 'host';

$user = 'user';

$pass = 'pass';

$dir = "/dev/ntagojp_unicode/"; //csvをアップロードしたディレクトリ

$con = new mysqli($host, $user, $pass, $db);

$enc = 'utf8mb4'; //sjis, cp932などエンコードを指定

$con->query(" set character_set_connection = {$enc};");

$con->query(" set character_set_server = {$enc};");

$con->query(" set character_set_database = {$enc};");

$con->query(" set character_set_client = {$enc};");

$con->query(" set character_set_results = {$enc};");

$data_date = date("Ymd");

$data_date = "20180629"; //適宜上書きで日付指定

$pref = array( 0 => array('01', "hokkaido")); //47都道府県＋海外データを適宜指定

$filename = "{$pref[0][0]}_{$pref[0][1]}_all_{$data_date}.csv";

//ヒアドキュメント構文は無用な\nが挿入されここでは使用しない。

$sql = " load data LOCAL infile '{$_SERVER['DOCUMENT_ROOT']}{$dir}{$filename}' into table gov_nta_corpnum_{$enc} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' ";

$sql .= " LINES STARTING BY '' TERMINATED BY ". "'\\n'" . " IGNORE 0 LINES ";

$sql .= " (sequenceNumber, corporateNumber, process, correct, updateDate, changeDate, name, nameImageId, kind, prefectureName,cityName, streetNumber, addressImageId, prefectureCode, cityCode, postCode, addressOutside, addressOutsideImageId,closeDate, closeCause, successorCorporateNumber, changeCause, assignmentDate, latest, enName, enPrefectureName, enCityName, enAddressOutside, furigana);";

$msql_result = $con->query($sql);

if( $msql_result === false ){

$msg = "SQLの実行に失敗しました";

}else{

$msg = "インポート終了";

}

$cnt_sql = "SELECT prefectureName, count(prefectureCode) FROM gov_nta_corpnum_utf8mb4 GROUP BY prefectureCode;";

$cntresult = $con->query($cnt_sql);

if($cntresult->num_rows > 0){

while ($row = $cntresult->fetch_assoc()) {

$arr[] = $row;

}

}else{

$arr = false;

}

echo $msg . ' ' .print_r($arr, true ) . ' <code>' . $sql . '</code>' ;

phpMyAdminがWebの公開ディレクトリにある状態は、
あんまり気持ちがいいものではないので、お勧めはしませんができるはず。

phpMyAdminでやるならディレクトリ名を
「51CA673EA8E89A868706FD309777A101AC63A409」とか、
推測できなそうなディレクトリにして、インストールですかね。
これは「phpMyAdmin」文字列を「SHA1」ハッシュ変換したものですが。

インストールしてみるなら、以下のような手順でどうぞ。

phpMyAdminでサーバー上でエクスポート・インポートする準備

と言う訳で、Webサーバーを一旦介するものの、
「LOCAL = Webサーバー」という認識で、「load data LOCAL infile」が利用できます。

CSVファイルへのアクセス権は「700」でWebサーバーにのみ許可すれば十分です。

うまく使うと、非常に強力で便利な機能ですので、
少しチャレンジしてみてはいかがでしょう？

本サイトで公開しているツールについては、
十分にテストは行っておりますが個人で作成している為、
潜在的なバグがないとは言い切れません。
その為、ツールを用いた結果については十分検証の上ご利用ください。
当サイトおよび、管理人は如何なる損害もその責を負いません。

当サイト内のコンテンツおよび画像において、
出典・引用の外部著作権者の明記がないものは、
すべて管理人による著作物です。
当サイトでご紹介しておりますコンテンツの著作権の放棄は致しません。

サイト内コンテンツを引用される際にはご連絡は不要です。
ただし、出典元として当サイト(個別記事)へのリンクをお願いいたします。
申し訳ございませんが、無断転載、複製をお断りさせて頂いております。

コンテンツを有益であると感じていただけましたら非常に光栄です。
ありがとうございます。