AWS RDSのMariaDB/MySQLでLOAD_FILEと同じ処理をする

AWS RDSのMariaDB/MySQLはRDS上で稼働しています。その為、EC2などのmysqlコマンドからSQLを叩いてローカルのファイルをカラムの値としてロードするLOAD_FILE('ローカルファイル名')が使えません。

バックエンドAPIや管理フロントエンドがまだない状態で、mysqlコマンドから画像やpdfなどのサンプルバイナリファイルをRDSの*BLOBのカラムへ格納したかったのですが、海外含めてググっても不可能と言うレスしかついていなかったので、ややトリッキーですが解決方法を記します。

sample.sql

-- ◆ローカルにあるサンプル用の画像素材などを一時保存するテンポラリテーブル
--
-- AWS RDSのMariaDBはLOAD_FILE('サーバ側ローカルファイル名')が使えないので
-- テンポラリテーブルを作成し、クライアント側のローカルバイナリファイルを LOAD DATA LOCAL INFILE で読み込んで、
-- そのテーブルのデータをサンプル画像として各種本テーブルへ挿入する。
-- 使う際は、LOAD_FILE('ファイル名') と書くところを (SELECT data FROM tmp_res WHERE file='ファイル名') と記述。
DROP TABLE IF EXISTS tmp_res;
CREATE TEMPORARY TABLE tmp_res (
  -- バイナリデータを一時保存するカラム
  data LONGBLOB not null,
  -- LOAD DATA で無理やり１ファイルをdataに格納するので、それ以外のパラメータはdataの後に定義する事
  id   int4 unsigned not null primary key auto_increment,
  file varchar(256) unique,
  ext  char(4)
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- ◆ローカルのバイナリファイルをLOAD DATA LOCAL INFILEでリモートのRDSへ格納する
-- LOAD DATA は本来 CSV などのテキストファイル用なので、FIELDS TERMINATED BY と LINES TERMINATED BY には読み込むファイルに絶対に含まれないような値を指定しておく事
-- 画像ファイルを１つテンポラリテーブルへ格納
LOAD DATA LOCAL  INFILE './bin/logo.png' INTO TABLE tmp_res CHARACTER SET binary FIELDS TERMINATED BY '\Z\t\0\b\n\r\N' ESCAPED BY '' LINES TERMINATED BY '\Z\t\0\b\n\r\N';
UPDATE tmp_res SET file='./bin/logo.png' ,ext='png'  WHERE id=(SELECT MAX(id) from tmp_res);
-- pdfファイルを１つテンポラリテーブルへ格納
LOAD DATA LOCAL  INFILE './bin/docs/doc.pdf' INTO TABLE tmp_res CHARACTER SET binary FIELDS TERMINATED BY '\Z\t\0\b\n\r\N' ESCAPED BY '' LINES TERMINATED BY '\Z\t\0\b\n\r\N';
UPDATE tmp_res SET file='./bin/docs/doc.pdf' ,ext='pdf' WHERE id=(SELECT MAX(id) from tmp_res);
-- 以下、上記と同じ調子でサンプルバイナリファイルをtmp_resへ保存していく

-- ◆バイナリデータを格納したい本テーブル(例)
drop table if exists bin;
create table bin (
    id      bigint unsigned not null primary key auto_increment,
    bin     LONGBLOB,  -- バイナリデータ(最大4G)
    bin_ext CHAR(4)    -- 拡張子
) ENGINE=InnoDB AUTO_INCREMENT=1;

-- tmp_resテーブルからバイナリデータを得てカラムへINSERT。その他の情報(ここではext)が欲しい場合は同じ感じで。
INSERT INTO bin (bin,bin_ext) VALUES ( (SELECT data FROM tmp_res WHERE file='./bin/logo.png'), (SELECT ext FROM tmp_res WHERE file='./bin/logo.png') );
INSERT INTO bin (bin,bin_ext) VALUES ( (SELECT data FROM tmp_res WHERE file='./bin/docs/doc.pdf'), (SELECT ext FROM tmp_res WHERE file='./bin/docs/doc.pdf') );

後は、普通にmysqlコマンドで流し込む（ローカルバイナリファイルはLOAD DATA LOCAL INFILEで参照可能な場所に置いておいてください）

DATABASE=データベース名
HOST=xyz.yyyyyyyy.ap-northeast-1.rds.amazonaws.com
PORT=3306
PASS=xxxxxxxx
/usr/bin/mysql -h $HOST -P $PORT -u root -p$PASS $DATABASE < sample.sql