PHPでLOAD DATA INFILEをした話

エンチャント逆引きを作ったときにdbを構築したのですが、頻繁にデータを追加しないといけないのが面倒でLOAD DATA INFILEを使おうと思いつきました。
しかしこのスペースを借りているusamimi.infoさんではdb接続を内部からのみ許可しているので、phpからアクセスする他なくスクリプトを書きました。
(もしphpなしでできる方法があったら教えてください)

PDOで接続

あらかじめ’name’->’filename’の形の連想配列$pathを宣言してあります。
LOAD DATA LOCAL INFILEを使うときにはPDO::MYSQL_ATTR_LOCAL_INFILE => true
を設定しておかないとLOCALが使えません。
$queryにクエリ文を配列の形で追加していき、最後にimplodeで繋げてexecしています。
最初このやり方を見たときは頭いいなあって思いました(小並感)。
csvによって区切り文字や囲み文字が違うので分岐させているのですが、統一しておけばよかったです。めちゃくちゃ面倒くさい。

「”」と「’」に気をつける

最初はmysqlで通るクエリをそのまま$queryに追加していたのですが、

FAILEDSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘LINES TERMINATED BY ‘ ” at line 1

のようなエラーが発生し続けていました。

エスケープのあたりで引っかかっているのはわかったのですが、ちゃんとエスケープしてるにもかかわらずなぜエラーになるのかさっぱりわかりませんでした。

実はこれまで変数への文字列代入の際にずっとシングルクォーテーションを使っていて、今回も単純にクエリ文をシングルクォーテーションで囲んでやっていました。

実はこれが罠。


エスケープシーケンスは、「’」(シングルクォーテーション)で囲まれていても展開されず、そのまま表示されてしまいますので必ず「”」(ダブルクォーテーション)で囲むようにしましょう。

PHP 改行、タブ(エスケープシーケンス)の使い方 – Web Development Blog

ええそうです。これをすっかり忘れていたアホは私です。
というわけでクエリ文をダブルクォーテーションで囲み、スクリプトは無事動くようになりましたとさ。めでたしめでたし。

追記(2019/01/29)

ローカル環境(MySQL 8.0.13)だとLOAD DATA INFILEで追加できたcsvがサーバー(MySQL 5.5.13)では500 Internal Errorで追加できないことがありました。

ファイルサイズは500kb~700kbなのでサイズ超過というわけではないのですが、BigDumpを使うことで追加することができました。

BigDump: Staggered MySQL Dump Importer

bigdump.phpを開き、

$db_server = ‘サーバーアドレス’;
$db_name = ‘データベース名’;
$db_username = ‘ユーザー名’;
$db_password = ‘パスワード’;

を入力してサーバーへアップロード。
同じディレクトリに追加したいsqlファイルをアップロードしてbigdump.phpにアクセスします。

Start ImportでDBへ追加することができます。

コメントする

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です