この記事はQiita x Code Polaris共催！女性ITエンジニアが作るアドベントカレンダー Advent Calendar 2022の9日目の記事です。
Qiita初投稿です。このカレンダーに参加することで背中を押してもらい記事を書くことが出来ました。ありがとうございます。
実務半年ほどのよわよわエンジニアなので認識違いなどありましたら指摘いただけるとありがたいです。
概要
業務の中で大量データ（最大で40万件ほどのレコードのテーブル）をMySQLの旧DBから新DBへ移行する機会があり、LOAD DATA LOCAL INFILEを使ったのですが、その時一筋縄ではいかず苦労したので、備忘録もかけてこの記事を書きました。
この記事で伝えたいこと
-
LOAD DATA LOCAL INFILEがざっと何をするのか -
LOAD DATA LOCAL INFILEでデータをロードするときに気を付けること - DBのデータをCSVに書き出すときに気を付けること
この記事で書かないこと
-
LOAD DATA LOCAL INFILEのパラメータやオプションなどの詳しい説明は公式に譲ります。
LOAD DATA LOCAL INFILEって？
そもそもMySQLにLOAD DATA INFILEという構文がありましてこれは、CSVなどのテキストファイルからデータをテーブルに取り込みます。
LOAD DATA INFILEはLOCAL修飾子を付けないとサーバー上にファイルが無いといけないのですが、LOCALを付けるとクライアント側のファイルを使用できます。
テーブルにデータを入れる際にdumpファイルでインポートする方法もありますが、dumpはSQLの集まりなので結果的にINSERT文をSQLで実行しています。
リファレンスによるとLOAD DATAステートメントはINSERTステートメントより20倍速いそうです。
今回40万件のテーブルがあり移行する時間を短くしたかったのでLOAD DATA LOCAL INFILEを選定するに至りました。
環境
PHP8.0.23
Laravel 9.23.0
MySQL8.0.29
サンプルコード
<?php
namespace App\Console\Commands;
use Illuminate\Console\Command;
use Exception;
use App\Services\Migrate\OldUserImporterService;
class DatabaseImporter extends Command
{
protected $signature = 'import:all';
public function handle()
{
try {
$service = new OldUserImporterService();
$service->exec();
} catch (Exception $e) {
//エラー処理
}
return 'SUCCESS';
}
}
コマンドで実行する実装だったのでLaravelのCommandを使用して書いてます。
上のサンプルだとphp artisan import:allと打つとデータ移行が実行されます。
コマンドで実行されるサービスは以下になります。
ここでは移行元のuserテーブルのデータをCSVに書き出しています。
<?php
namespace App\Services;
use Exception;
use DB;
use App\Models\OldUser;
use App\Libraries\FileCtrl;
class OldUserImporterService
{
private $_file_name = 'OldUserImporterService';
public function exec()
{
$count = OldUser::count();
if ($count > 0) {
//ファイル操作をいい感じにやるライブラリ（主題じゃないので解説省略）
$f = new FileCtrl();
$f->open($this->_file_name);
$insert_counter = 0;
//移行元のデータをCSVに書き出す
$records = DB::table('old_users')->get();
if ($records) {
foreach ($records as $record) {
$set_data = '';
$set_data .= '"' . addslashes($record->id) . '",';
$set_data .= is_null($record->display_name) ? 'NULL' : '"'. addslashes($record->display_name) .'"';
$set_data .= '"' . addslashes($record->email) . '",';
$set_data .= "\n";
$f->write($set_data);
$insert_counter++;
}
$f->close();
}
}
if ($insert_counter > 0) {
//移行先のDBを指定する
DB::connection('new_connection');
DB::statement('TRUNCATE databases.new_users;');
foreach ($f->get_files() as $file) {
$this->_import($file);
// 処理したファイルは削除する
unlink($file);
}
} else {
$f->unlinks();
}
return;
}
}
肝心のLOAD DATA部分はこちら。
private function _import($file_path)
{
try {
DB::beginTransaction();
//移行先のDBを指定する
DB::connection('new_connection');
//外部キー制約無効
DB::statement('SET foreign_key_checks = 0;');
// CSV取り込み
$insert_sql = "LOAD DATA LOCAL INFILE '" . $file_path . "' INTO TABLE database.new_user "
. "FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' "
. "(id,"
. "name,"
. "email);";
DB::statement($insert_sql);
DB::statement('SET foreign_key_checks = 1;');
DB::commit();
} catch (Exception $e) {
DB::rollback();
//エラー処理
}
return;
}
気を付けること（主題）
1.local_infileの設定をサーバーとクライアント両方で有効にする
このようなエラーが出たときはサーバー側かクライアント側どちらかまたは両方のlocal_infileが無効になっています。
ERROR 3950 (42000): Loading local data is disabled; this must be
enabled on both the client and server side
サーバー側:
デフォルトで無効になっているので
mysql> SET GLOBAL local_infile=on;
mysql> select @@local_infile;
+----------------+
| @@local_infile |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
クライアント側:
MySQLログイン時に以下のようにオプションを指定します。
$ mysql -u root -p --local_infile=1
Laravelの場合config/database.phpに下記を追加します。
'mysql' => [
//省略
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
+ PDO::ATTR_EMULATE_PREPARES => true,
+ PDO::MYSQL_ATTR_LOCAL_INFILE => true,
]) : [],
],
2. 予約語やハイフン含むテーブル名はバッククォートで囲む
これはLOAD DATA関係なくMySQLの約束事ですが、MySQL側で特別な意味を持つ単語を予約語（SELECT、DELETE、BIGINなど）といい、予約語のカラム名やテーブル名はバッククォートで囲まないと構文エラーになります。
予約語一覧
予約語を使うのはなるべく避けたいですが意図せず引っかかってる場合もありますのでご注意ください。
(`key`, `group`, `select`)
あとハイフンが入ったテーブル名もバッククォートで囲まないと構文エラーになります（最近知りました…）。
INTO TABLE `data-base`.user
3.CSVに書き出すときエスケープ処理を入れる
この部分ですね。
$set_data .= '"' . addslashes($record->email) . '",';
初歩的な部分ですが、CSVの区切り文字,カンマや、囲み文字"ダブルクォート、'シングルクォート、などがデータに含まれてるとCSVが正しく記述できません。
業務では、正しく記述されなくてもエラー起きないのでそのままLOAD DATAされてしまって移行時にデータが壊れて旧DBと新DBで差分が出てしまう事態になりました…。
というわけでエスケープ処理が必要です。
PHPにはそのものズバリなaddslashes関数がありましてエスケープが必要な文字にバックスラッシュを付けて返してくれます。
今回の例だとエスケープしたデータを"ダブルクォートで囲んで,カンマで区切っています。
4.NULLの扱いについて確認する
これもCSVを書き出すときの注意ですが、以下の部分では旧DBのデータがNULLだった場合'NULL'を入れる処理をしています。（PHPのfwrite()で書き出してます）
$set_data .= is_null($record->display_name) ? 'NULL' : '"'. addslashes($record->display_name) .'"';
CSVに書き出したときに以下の差異があります。
//DBのNULLデータをそのまま書き出した場合
"19",,"email"
//'NULL'に変換して書き出した場合
"19",NULL,"email"
NULLが空欄に変換されてる！！！
当然エラーが出ずそのままLOAD DATAされますが空文字がNULLに変換されるわけもなく、どうなるかというとリファレンスにさらっとこう書かれています。
入力行に含まれるフィールドが少なすぎる場合、入力フィールドがないテーブルカラムはそのデフォルト値に設定されます。 デフォルト値の割り当てについては、セクション11.6「データ型デフォルト値」で説明されています。
引用元：https://dev.mysql.com/doc/refman/8.0/ja/load-data.html
明示的にDEFAULTを指定していないカラムの場合暗黙的なデフォルト値になります。
暗黙的なデフォルトは次のように定義されます。
- 数値型のデフォルトは 0 です。ただし、例外として AUTO_INCREMENT 属性で宣言された整数型または浮動小数点型のデフォルトは、そのシーケンスの次の値になります。
- TIMESTAMP 以外の日付と時間型のデフォルトには、「ゼロ」値が適切です。 explicit_defaults_for_timestamp システム変数が有効な場合、これは TIMESTAMP にも当てはまります (セクション5.1.8「サーバーシステム変数」を参照してください)。 それ以外の場合、テーブルの最初の TIMESTAMP カラムのデフォルト値は現在の日付と時間になります。 セクション11.2「日時データ型」を参照してください。
- ENUM ではない文字列型のデフォルト値は空の文字列です。 ENUM のデフォルトは、最初の列挙値です。
引用元：https://dev.mysql.com/doc/refman/8.0/ja/data-type-defaults.html
これも業務での移行時に旧DBと新DBで差分が出てしまう事故になりました…。
CSVに書き出すときの挙動とMySQLのデフォルト値の挙動を理解していなかったため起きた事故ですのでベテランの方は当たり前に考慮されてるかもしれませんが失敗談として記しました。
皆さんもお気を付けください。
（ここまで面倒なら別の技術選定した方がいいかもしれませんが）