PHPからMYSQLコマンドでMariaDBの「LOAD DATA LOCAL INFILE」でCSVインポートする方法
「LOAD DATA LOCAL INFILE」でCSVインポートすると速い
INSERT処理として最速なのが多分LOAD DATA LOCAL INFILE。
バルクインサートよりも速いので活用していこう。
PHPからPDOでも実行できますが、
別サーバーからMYSQLコマンドで実行する方法を紹介します。
（実験としてやったので記録するだけ）
※csvがDBサーバーにあるならLOAD DATA INFILEを使う方が速い
＜環境＞
・MariaDB：10.6.11
・PHP：8.2.1
・Ubuntu 22.04.1 LTS
実現したいこと
PDOじゃなくてMYSQLコマンドでログインしてやりたい。
PHPはシェルコマンドを実行することができるので、
ただPHPでubuntuを操るような感じです。
普通はPDOでやります。
なんでこんな方法をやろうと思ったのか？ですが、
MYSQLコマンドでインポートする方が速いのでは？という確認から。
実際、MYSQLから実行するとCPUを全部使うので速かったりする。
このあたりは是非確認してみてください。
１、まず、MYSQLコマンドをパスワード無しで使えるようにする
sudo visudo
#追記
nginx ALL=(ALL) NOPASSWD: /usr/bin/mysql
PHPのユーザーを「nginx」にしている場合です。
これでPHPから自由にMYSQLコマンドを使えるようになる。
２、SQLを書いたテキストファイルを作る
/var/www/sql.txt
LOAD DATA LOCAL INFILE \"/var/www/test.csv\" INTO TABLE testtable FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"';
MYSQLコマンドで実行するファイルを選択できるので、
SQLを書いたファイルを作成します。
この方法が汎用性あって一番良いと思う。
取り込むCSVは/var/www/test.csvの部分。
区切り文字は「,」、囲い文字は「”」を指定しています。
３、外部のDBへログインするためのファイルを作る
/var/www/db.txt
[client]
user = xxxxxxxxx
password = 'xxxxxxxxxxxxx'
host = 192.168.0.1
ログイン系のコマンドをこうしてファイルとして書いておくことができます。
パスワードの入力ができないので、おそらくこの方法しかない。
４、MYSQLコマンドで実行する
＜PHP＞
$cmd = 'sudo mysql --defaults-extra-file=/var/www/db.txt -D dbname < /var/www/sql.txt';
shell_exec($cmd);
これでMYSQLコマンドでログイン→外部DBサーバーへログイン→SQLの実行ができます。
ファイルの中身を都度「file_put_content」で作成しても良い。
そうすると変数で中身を変更できるので、ループ処理で違うSQLを実行できます。
以上
MYSQLコマンドだと何でもできるので覚えておきたい
シェルスクリプトでも可能とは思いますが、
自分はPHPの処理と合わせてやりたいのでPHPでやっています。
CSVの作成→CSVインポートという処理にすると実用的。
データ量が少ない場合はバルクインサートでも速度は大差無いですが、
数万件となるとCSVにした方が速いと思います。
セレクト結果をCSVにして、そのままインポートするだけなのでプログラムも簡単。
次回はその方法を書きたいと思います。
