IPアドレスから地理的位置情報(GeoIPLocation)の検索機能を公開しました。
大量レコードをレンタルサーバーのMySQLへ一括インポートのコツ
レンタルサーバーに対してレコード数の多いCSVファイルを一括インポートするのは、
決して楽な作業ではありませんが「load data LOCAL infile」を利用すると、
レンタルサーバーであっても大量のデータのインポートが行え便利です。
ここではXSERVERに対して国税庁の公開している「法人の基本３情報のデータ(法人番号)」を、
実際にインポートしてみてハマった実例からご紹介します。
本投稿は以下で実際にインポートを行った際のメモになります。
目次 [すべて展開]
大量レコードをレンタルサーバーのMySQLへ一括インポートのコツ
レンタルサーバーを利用していてストレスとなるのが大量のデータインポートです。
SSHなどに慣れている方であれば、
mysqldumpなどを使ってサクッと処理してしまうかもしれません。
XSERVERもSSHには対応しているので、
それでも良さそうだったのですが、接続がうまくいかず今回は一旦断念しました。
mysqldumpで新たにCSVファイルからインポートするには、
一工夫が必要になったりするかもしれません。（やったことない）
そこで、誰でもできる方法で大量のCSVを、
レンタル共有サーバー上のMySQLに取りこめないかと試行錯誤をしました。
CSVファイルのphpAdmin経由のインポートをそのままインポートツールで行うと、
データのサイズによっては時間がかかり過ぎて、
途中でエラー画面が出てしまうかもしれません。よく出会う症状でしょう。
Web取り込み時のタイムアウト・「load data LOCAL infile」を利用したCSV取込
そこで今回はPHPのプログラムを利用してインポートを実施しました。
ただ地味に手こずる事と思います。
人それぞれの環境によってできたりできなかったりと障壁はそれぞれだと思います。
ですが、私の環境でひとまずうまくいったという方法を共有いたします。
load data LOCAL infileの構文ヘルプはかなり長いが要確認
MySQLには「LOAD DATA INFILE 構文」があり、
非常に高速にCSVファイルのインポートを行ってくれます。
エラー内容が確認しにくかったりと苦労する部分もありますが、
どうしても大きなCSVファイルをインポートしなければならない場合には非常に便利です。
（公式）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.6 LOAD DATA INFILE 構文
phpMyAdminからのSQL発行は、利用できない（模様）
今回はPHPでプログラムを書いてインポートを行っています。
phpMyAdmin側から実行すると以下の様なエラーメッセージがでてうまく取り込めませんでした。
#1148 – The used command is not allowed with this MariaDB version
エラーメッセージの内容としてはMySQLバージョンによって対応していないと読み取れます。
MySQLは5.5がレンタルサーバー上で稼働しています。（XSERVSERでは5.7を推奨しています）
MySQL5.7のphpMyAdminで実行した場合は、以下の様なメッセージに変わりました。
#1148 – The used command is not allowed with this MySQL version
ただ、このメッセージが出ている同じ環境ですが、
今回のPHPによる処理では無事取り込めています。
ヘルプを読む限りは、phpMyAdminでは「load data LOCAL infile」は、
サポートされないという事が原因のようです。
インポートする際に、データのエンコードと合わせる
現在利用しているMySQLのデフォルトエンコードは「UTF-8」にて設定しています。
国税庁の「法人の基本３情報のデータ(法人番号)」のデータは、
以下の通り２つのエンコードに対応しています。
ファイル形式・文字コード
ファイル形式は、CSV及びXML形式の２種類、文字コードは「Shift-JIS（JIS第一・第二水準）」と「Unicode（JIS第一～第四水準）」の２種類に対応しています。
ファイル形式との組合せで「CSV・Shift-JIS」、「CSV・Unicode」及び「XML・Unicode」の３種類のファイルを用意しています。
尚、「法人の基本３情報のデータ(法人番号)」はWebAPIを提供しています。
通常は自DBに対してデータのインポートを行わずWebAPI経由で参照すれば十分であるはずです。
そこを訳あって今回は自DBに取り込む必要があり実施しています。
「Shift-JIS」と「Unicode」に対応していると書かれていますが、
実際、「Shift-JIS」がMySQLで言うところの「sjis」なのか「cp932」なのか分からず悩み、
また「Unicode」が「UTF-8」なのか「UTF-16」なのか分からず、苦戦しました。
とりあえずのところインポートの実行前に「set character_set_XXX」にて、
エンコードの指定を行うのですが、
ダウンロードファイルが「Shift-JIS」なら「sjis」「cp932」のどちらも取り込めました。
ダウンロードファイルが「Unicode」なら「UTF8mb4」を指定する事で取り込むことができました。
MySQLのクライアントとサーバー側の双方のエンコードを一時的に変更するSQLを実行し、
データのエンコードを正しく解釈されるように処理しています。
set character_set_connection = UTF8mb4;
set character_set_results = UTF8mb4;
set character_set_client = UTF8mb4;
set character_set_server = UTF8mb4;
set character_set_database = UTF8mb4;
これは現在の接続時のみのエンコード指定として有効なもので、
再接続時には無効になり、デフォルトエンコードに戻されます。
SQL実行時に都度、セットする必要があります。
SQLの組み立て時にヒアドキュメント構文は避けた方が無難
PHPからSQLを組み立てる際に便利なのがヒアドキュメント構文で、
複数行のテキストを一括して変数に格納する事ができます。
以下はダメだった例
ヒアドキュメント構文を利用してSQLを組み立ててSQLを実行しても、
通常は意識することなく問題なく利用できます。
ですが、今回「load data LOCAL infile」を上記のようにすると、
どうにもうまく動かなかったのに悩みました。
各行の末尾には「\r\n」の改行コードが実際には付加されています。（Windowsの場合）
デバッグ実行した際などにはその改行コードの確認もできますが、
どうも、その改行コードが邪魔しているのか、
ヒアドキュメント構文を止めて、単独でSQLを組み立てていくとうまくいきました。
無用な躓きを減らす意味でもこの場合ヒアドキュメントはさせた方が無難かもしれません。
また以下の通りSQLも単独で発行した方が無難です。
「load data LOCAL infile」SQLの発行は単独で行う
上記のエンコード指定とSQLの発行を、同時に発行した場合には私はうまく取り込めませんでした。
以下の様なSQLを組み立てたの場合です。（SQLは上記と同じです）
以下はダメだった例
このSQLを変数に格納し、
$con = new mysqli($host, $user, $pass, $db);
$msql_result = $con->query($tmp);
としてしまう様なアプローチです。
mysqliクラスのquery()はSQLの一括実行には対応していると認識していますが、
うまくいきませんでした。
ちなみに、mysql_query()関数では、
複数のSQLの一括実行はサポートされない事が明記されています。
mysql_query() は、 ひとつのクエリを送信します (複数クエリの送信はサポートしません)。 送信先は、
link_identifierで指定したサーバー上にある、現在アクティブなデータベースです。
SQL一括発行を止めて、それぞれを別々に実行するようにすると、動作しました。
少なくとも「load data LOCAL infile」は単独実行が無難でかと思います。
以下は実行例の抜粋
SQL特殊文字のエスケープ処理は忘れずに
上のSQLでもエスケープはしていますが、MySQLの特殊文字は以下があります。
「’」や「”」を文字列として扱うには、エスケープする必要があります。
表 9.1 特殊文字エスケープシーケンス
エスケープシーケンス シーケンスが表す文字 \0ASCII NUL ( 0x00) 文字。\'単一引用符 (「 '」) 文字。\"二重引用符 (「 "」) 文字。\bバックスペース文字。 \n改行 (ラインフィード) 文字。 \r復帰改行文字。 \tタブ文字。 \ZASCII 26 (Ctrl+Z)。表に続いて記されている注釈を参照してください。 \\バックスラッシュ (「 \」) 文字。\%「 %」文字。表に続いて記されている注釈を参照してください。\_「 _」文字。表に続いて記されている注釈を参照してください。
今回のSQLで言えば、以下の様な部分です。
PHP側でSQLを組み立てた際に、特殊文字のエスケープが必要でした。
以下は一部抜粋。
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘\“‘ ESCAPED BY ”
LINES STARTING BY ” TERMINATED BY “. “‘\\n‘” . ” IGNORE 0 LINES (sequenceNumber, ….
MySQLWorkBenchを利用して直接ローカルに対して、
SQLを発行した場合には、エスケープする必要がなかったので、
PHPから実行する場合には、この点は気づきにくいかもしれません。
取込後のデータベースファイルサイズには注意を
今回の国税庁の「法人の基本３情報のデータ(法人番号)」を自前のDBでホストするとなると、
2018/7/6時点におけるデータでは、
4,841,895レコードにもなり、データベース容量も1.5Gに達しています。
460万レコード・1.5GBテーブル・「load data LOCAL infile」を利用したCSV取込
正直、レンタルサーバー上ではちょっと重たいかなという状況です。
大きなファイルをDBにインポートする際には、
ご契約のレンタルサーバーの制限にもご注意ください。
XSERVERでは、上限目安は1Gとされていますが、
一応、1.5Gは取りこむことはできています。（別途制限を受ける可能性もあります）
最終的には以下の様なPHPからSQLを発行し実行
詳細は以下でご紹介していますが、実行したPHPをご紹介しておきます。
色々と躓きつつも「load data LOCAL infile」でCSVのインポートが利用できると、
非常に膨大なデータをレンタルサーバー上のMySQLにもサクッと取り込むことができます。
地味に苦労はしますが、一回でも自分の環境に合ったできる方法が見つかれば、
後はそのソースの編集だけで、流用ができるようになって便利なハズです。
少し、チャレンジしてみてはいかがでしょうか。
本サイトで公開しているツールについては、
十分にテストは行っておりますが個人で作成している為、
潜在的なバグがないとは言い切れません。
その為、ツールを用いた結果については十分検証の上ご利用ください。
当サイトおよび、管理人は如何なる損害もその責を負いません。
当サイト内のコンテンツおよび画像において、
出典・引用の外部著作権者の明記がないものは、
すべて管理人による著作物です。
当サイトでご紹介しておりますコンテンツの著作権の放棄は致しません。
サイト内コンテンツを引用される際にはご連絡は不要です。
ただし、出典元として当サイト(個別記事)へのリンクをお願いいたします。
申し訳ございませんが、無断転載、複製をお断りさせて頂いております。
コンテンツを有益であると感じていただけましたら非常に光栄です。
ありがとうございます。
公開日：
最終更新日：2019/06/20
シェア頂けると新機能開発の励みになります！！
最後までお読みいただきありがとうございました。
便利！と思って頂けたらシェアを是非お願いします。
ご意見・ご批判でも感じた事を気にせずご指摘ください。
アカウント新規作成の為、是非フォローもお願いします。(無言OK)
※このページでこんな情報も一緒に見れたら便利！などの
アイディア・ご意見もお聞かせください。
また弊サイトはすべて広告費と個人の自腹にて運営・開発を行っています。
皆様、お仕事中でお忙しいことと存じますが、
ご興味がおありの広告が目に留まりましたら
広告主様のサイトもご覧いただけますと励みになります。
ご意見・ご質問はTwitterのご利用が便利です。
非公開でのご連絡には「Message」をご利用ください。
QRコードからもこのURLを開けます。
リンク先URL： https://ipvx.info/everyones-db/mysql/load-data-local-infile/
