バルクインサートは、複数行をまとめて送信したり、ファイルから一括で読み込んだりして、大量データの投入に伴う往復通信や処理回数を減らす方法です。ただし、速く読み込めたことと、欠落や誤変換なく取り込めたことは同じではありません。件数、警告、重複、取り消し方法まで設計しておきましょう。
この記事の実装例はMySQL 8.4・InnoDBを対象にしています。SQL ServerのBULK INSERTやPostgreSQLのCOPYとは構文や権限が異なります。まず少量の架空データと一時テーブルで手順を確認し、その後、利用環境の制約に合わせて実装してください。
バルクインサートの方式とDBMSの違い
- 複数行のINSERT：
INSERT ... VALUES (...), (...)で複数行を送ります。アプリケーションから渡す値は、ドライバーのパラメーター機能を使います。 - INSERT … SELECT：同じDB内の別テーブルから取り込む方法です。検証用ステージングテーブルからの反映にも使えます。
- ファイル読み込み：MySQLでは
LOAD DATAを使います。入力形式、読み取るホスト、権限の確認が必要です。
MySQL公式のINSERTの最適化ガイドでも、複数行をまとめる方法やLOAD DATAが紹介されています。ただし、何倍速くなるかはデータ量、インデックス、ストレージ、通信などで変わるため、固定の倍率は前提にしません。
- MySQL 8.4：
LOAD DATA INFILEはサーバー上のファイル、LOAD DATA LOCAL INFILEはクライアント側のファイルを読みます。詳しい違いは後述します。 - SQL Server：BULK INSERTを使います。SQL Server 2017以降のCSV対応など、製品・バージョン・実行環境で指定可能なオプションやファイルアクセス条件が変わります。
BEGIN TRY ... END CATCHはT-SQLの構文で、MySQLには貼り付けられません。 - PostgreSQL：COPYで読み込みます。ファイル名を指定する
COPYはサーバー側、psqlの\copyはクライアント側のファイルを扱います。\copyはpsqlのコマンドで、一般のSQL文とは区別します。
実装例：一時テーブルで投入・検証・取り消しを確認する
次の例は、商品コード・商品名・数量の3列を取り込みます。本番ではない検証用データベースを選択した、新しい専用接続で実行してください。既存トランザクションのない状態とし、接続を途中で切り替えず、各段階の確認が終わってから進めます。全文を無条件に一括実行するスクリプトではありません。
テーブル名のtmp_bulk_32963_*はこの記事の検証専用です。両方ともTEMPORARYなので接続内だけで見え、切断時に削除されます。作成権限がない環境では、管理者が用意した検証環境を使い、権限やサーバー設定を自己判断で変更しないでください。一時テーブルの仕様も確認してください。
1．前提を確認し、テーブルを用意する
SELECT VERSION() AS mysql_version,
@@SESSION.sql_mode AS sql_mode,
@@SESSION.autocommit AS autocommit;
CREATE TEMPORARY TABLE tmp_bulk_32963_stage (
row_no BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
sku_raw TEXT,
product_name_raw TEXT,
quantity_raw TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TEMPORARY TABLE tmp_bulk_32963_inventory (
sku VARCHAR(20) NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
quantity INT UNSIGNED NOT NULL,
CHECK (quantity <= 999999)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;MySQL 8.4であること、autocommit = 1であること、STRICT_TRANS_TABLESなどの厳格モードが有効であることを確認します。以降のCSV例の\nは通常のSQL文字列エスケープを前提とするため、NO_BACKSLASH_ESCAPESが有効な環境にはそのまま流用しません。条件が異なる場合はここで止め、利用環境に合わせてレビューしてください。
ステージング側は数値も文字列で受け、変換前の値を検査します。ただしTEXTも無制限ではなく、読み込み時の文字コード変換や長さの問題は別途警告確認が必要です。テーブル作成などのDDLはデータ反映の前に済ませます。通常のDDLは暗黙のコミットを起こすものがあり、一時テーブルの作成自体もROLLBACKでは取り消せません。暗黙のコミットの対象を確認しましょう。
2．少量のデータをステージングに入れる
INSERT INTO tmp_bulk_32963_stage
(sku_raw, product_name_raw, quantity_raw)
VALUES
('P001', 'ノート', '10'),
('P002', 'ペン, 青', '20'),
('P003', 'ケース "小"', '15');
SHOW COUNT(*) WARNINGS;
SHOW WARNINGS;期待値は投入3行、警告数0です。警告があれば反映へ進まず、原因を調べます。後述のCSVを使う場合は、このINSERTの代わりに空のステージングへ読み込みます。両方実行すると同じデータが重複します。
3．件数・形式・重複を検証する
この例の入力ルールは「商品コードは半角大文字・数字・ハイフンの1～20文字」「商品名は1～100文字で空白のみや改行を不可」「数量は半角数字1～6桁」です。数量は0～999999になり、符号、小数、単位付き文字列を受け入れません。業務で必要なルールに置き換えてください。
SELECT COUNT(*) AS staged_rows
FROM tmp_bulk_32963_stage;
-- 結果が0行であること。数値への変換はまだ行わない。
SELECT row_no, sku_raw, product_name_raw, quantity_raw
FROM tmp_bulk_32963_stage
WHERE sku_raw IS NULL
OR CHAR_LENGTH(sku_raw) NOT BETWEEN 1 AND 20
OR REGEXP_LIKE(sku_raw, '[^A-Z0-9-]', 'c')
OR product_name_raw IS NULL
OR CHAR_LENGTH(product_name_raw) NOT BETWEEN 1 AND 100
OR NOT REGEXP_LIKE(product_name_raw, '[^[:space:]]', 'c')
OR LOCATE(CHAR(10), product_name_raw) > 0
OR LOCATE(CHAR(13), product_name_raw) > 0
OR quantity_raw IS NULL
OR CHAR_LENGTH(quantity_raw) NOT BETWEEN 1 AND 6
OR REGEXP_LIKE(quantity_raw, '[^0-9]', 'c');
-- ファイル内・バッチ内の重複。結果が0行であること。
SELECT sku_raw, COUNT(*) AS duplicate_count
FROM tmp_bulk_32963_stage
GROUP BY sku_raw
HAVING COUNT(*) > 1;
-- 反映先に同じキーがないこと。結果が0行であること。
SELECT s.sku_raw
FROM tmp_bulk_32963_stage AS s
JOIN tmp_bulk_32963_inventory AS t ON t.sku = s.sku_raw;サンプルではstaged_rows = 3、残り3つのSELECTはすべて0行が期待値です。どれかが違えば反映を中止します。これらのSELECTは不正行を表示するだけで、自動停止はしません。自動化する場合は、結果をアプリケーションで判定してから次の処理を呼び出してください。正規表現の照合指定はMySQLのREGEXP_LIKEに従っています。
実際の反映先では、主キー以外のUNIQUE制約、外部キーの参照先、日付の妥当性なども確認します。ここでは検証中に変更されない一時テーブルを使っています。本番の共有テーブルでは事前SELECT後に他処理が書き込めるため、事前チェックだけに依存せず、制約を残して反映時のエラーも処理します。
4．反映を試し、ROLLBACKで戻す
START TRANSACTION;
INSERT INTO tmp_bulk_32963_inventory (sku, product_name, quantity)
SELECT sku_raw, product_name_raw, CAST(quantity_raw AS UNSIGNED)
FROM tmp_bulk_32963_stage;
-- INSERT直後に警告を確認する。
SHOW COUNT(*) WARNINGS;
SHOW WARNINGS;
SELECT COUNT(*) AS inserted_rows, SUM(quantity) AS total_quantity
FROM tmp_bulk_32963_inventory;
-- 最初の練習では確定せず、必ず取り消す。
ROLLBACK;
SELECT COUNT(*) AS rows_after_rollback
FROM tmp_bulk_32963_inventory;警告0、反映直後はinserted_rows = 3・total_quantity = 45、ROLLBACK後はrows_after_rollback = 0が期待値です。ステージングへの投入はトランザクション開始前に確定しているため、ステージングの3行は残ります。これは実行時に照合する期待値であり、利用環境での実測値ではありません。
本番の確定処理では、同じ接続で全検証を通過した場合だけCOMMITします。エラーや想定外の警告があればROLLBACKし、後続のCOMMITを実行しない制御が必要です。MySQLの文エラーが常にトランザクション全体を取り消すわけではありません。また、非トランザクションのテーブルや、外部への副作用まで戻せるとは限りません。InnoDBのエラー処理とトランザクション制御の仕様を確認してください。
CSVをLOAD DATAで取り込む場合
サーバー側とクライアント側を区別する
LOAD DATA INFILE：DBサーバーが読める場所にファイルが必要です。通常の表への操作権限に加え、FILE権限、OS側の読み取り権限、secure_file_privの制限を満たす必要があります。自分のPCのパスを指定してもサーバーには届きません。LOAD DATA LOCAL INFILE：クライアントがファイルを読み、サーバーへ送ります。FILE権限は不要ですが、サーバーとクライアントの双方がLOCALを許可している必要があります。許可されていない環境で、無断で有効化する解決策は採りません。
サーバー側の制限はSELECT @@GLOBAL.secure_file_priv;で確認できます。ディレクトリならそこに制限され、NULLなら対象のファイル操作が無効です。空文字だからといって安全という意味ではありません。管理者が承認した取り込み方法を利用してください。secure_file_privの仕様を参照してください。
LOCALでは、信頼できないサーバーが意図しないクライアントファイルを要求するリスクもあります。接続先の本人性確認、読み込み可能なディレクトリの制限などが重要です。エラー回避だけを目的に設定を緩めず、LOAD DATA LOCALのセキュリティ上の注意に沿って判断します。
CSVの入力仕様を先に決める
ここではUTF-8（BOMなし）、LF改行、カンマ区切り、先頭にヘッダー1行という仕様に固定します。引用符はダブルクォート、フィールド内の引用符は2個に重ね、セル内改行は扱いません。空文字やNULLがある行は検証で止める方針です。ヘッダーはsku,product_name,quantityの順と内容を事前に確認します。
sku,product_name,quantity
P001,ノート,10
P002,"ペン, 青",20
P003,"ケース ""小""",15このファイルを、前のINSERTを実行していない空のステージングへ読み込む例です。パスは架空の例なので、サーバー上の承認済みディレクトリにある実ファイルに置き換えます。
LOAD DATA INFILE '/approved-import/inventory.csv'
INTO TABLE tmp_bulk_32963_stage
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(sku_raw, product_name_raw, quantity_raw);
SHOW COUNT(*) WARNINGS;
SHOW WARNINGS;文字コードとフィールド指定はMySQL 8.4のLOAD DATA仕様を参照してください。この例のIGNORE 1 LINESは先頭の1行を飛ばす指定で、重複エラーを無視するIGNORE修飾子とは別です。ヘッダー名を照合する機能ではないため、列順が違っていても自動では直りません。
- CRLFのファイルをそのまま使うなら、
LINES TERMINATED BY '\r\n'に合わせます。LFとCRLFが混在したファイルは、取り込み前に形式を統一します。 - 拡張子が.csvでも文字コードは保証されません。Shift_JISやUTF-16のデータをUTF-8と宣言するだけでは変換できません。実際の文字コードを確認し、変換後の文字化けも点検します。
- 引用符内のカンマ・引用符、空文字、NULL表現、改行を含むセルをテストします。このサンプルの対象外となるセル内改行や別のエスケープ方式は、対応するCSVパーサーで検証・正規化してから扱います。
- 読み込み後は先ほどの形式・重複検証へ戻ります。入力レコード数とステージング件数を照合し、エラーや警告のあるステージングはそのまま反映しません。
LOCALはファイルの場所だけの違いではありません。REPLACEを併用しないLOCALは、重複やデータ解釈エラーについてIGNOREに似た継続動作になり、エラーが警告へ変わる場合があります。「成功」と表示されても全行が正しく入ったとは限りません。非LOCALでもSQLモードなどの条件に依存するため、どちらでも警告と件数を確認します。
SHOW WARNINGSに表示される明細数は保存上限の影響を受けます。必ず直前の投入処理についてSHOW COUNT(*) WARNINGSで総数を確認し、明細を保存してから別のSQLへ進みます。後のSQLで診断情報が入れ替わることに注意してください。SHOW WARNINGSの仕様に詳細があります。
重複データの扱いを決める
主キーが重複したときの方針は、速度ではなく業務上の意味で決めます。上の実装例は重複を検出したら全体を止める方針です。単にINSERT IGNOREを付けると、重複だけでなく一部の不正値も警告に変わり、スキップや値の補正が起きるため、一般的な安全策にはなりません。
- 停止：通常のINSERTと制約を使い、エラー時に反映トランザクションを戻します。想定していない重複を見逃したくない場合に適します。
- スキップ：既存行を維持するという業務ルールを明示し、対象キーと除外件数を記録します。エラーを一括で無視するだけでは検証の代わりになりません。
- 更新：新しい値で置き換える列を明示します。バッチ内で同じキーが複数ある場合も、入力順に任せず採用ルールを決めます。
MySQLで重複時の更新が必要なら、次のように新しい行の別名を使えます。これは上の「重複時は停止」手順とは別の選択肢で、数量を加算するのではなく上書きする例です。
INSERT INTO tmp_bulk_32963_inventory (sku, product_name, quantity)
VALUES ('P001', 'ノート', 12) AS incoming
ON DUPLICATE KEY UPDATE
product_name = incoming.product_name,
quantity = incoming.quantity;更新句で挿入予定値を参照する旧来のVALUES(列名)は、MySQL 8.4では非推奨です。これは複数行INSERTのVALUES句そのものが非推奨という意味ではありません。ON DUPLICATE KEY UPDATEの公式仕様に従い、行・列の別名を使います。複数のUNIQUEキーがある表では、どの衝突でどの行が更新されるかも慎重に確認します。
アプリケーションではパラメーターと例外処理を使う
SQL文字列へ値を連結するのではなく、ドライバーに値を別引数で渡します。次はMySQL Connector/Python用の1バッチ分の例です。専用の既存接続cnxと、形式・範囲・重複を検証済みの小さなリストrowsを受け取ります。接続や認証の設定、インストールは含みません。検証用一時テーブルを同じ接続で作成していることが前提です。
def insert_validated_batch(cnx, rows):
if cnx.in_transaction:
raise ValueError("Use a dedicated connection without an active transaction")
if not rows:
return 0
cur = cnx.cursor()
try:
cnx.start_transaction()
cur.executemany(
"INSERT INTO tmp_bulk_32963_inventory "
"(sku, product_name, quantity) VALUES (%s, %s, %s)",
rows,
)
inserted = cur.rowcount
cur.execute("SHOW COUNT(*) WARNINGS")
warning_count = cur.fetchone()[0]
if warning_count != 0 or inserted != len(rows):
raise RuntimeError("Unexpected warnings or inserted row count")
cnx.commit()
return inserted
except Exception:
cnx.rollback()
raise
finally:
cur.close()%sはConnector/Pythonのパラメーターマーカーです。Pythonの%演算やf文字列で値を埋め込む用途ではありません。表名・列名は値パラメーターで置き換えず、コード側で固定するか許可リストから選びます。executeのパラメーター仕様とexecutemanyの仕様を参照してください。
この関数を複数回呼ぶと、バッチごとに確定します。後のバッチが失敗しても、前のCOMMIT済みデータまでは戻りません。ファイル全体の原子性が必要なら、呼び出し側でトランザクション境界を設計し直す必要があります。長大な単一トランザクションとの負荷の違いも測定してください。
また、COMMITの応答待ちで接続が切れた場合は、サーバー側で確定済みか不明なことがあります。例外を受けたことやROLLBACKを試みたことだけで未反映と決め付けず、取込ID・ファイルのハッシュ・処理履歴などで結果を照合してから再試行します。無条件の再送は重複や二重更新の原因になります。
性能を改善するときに守ること
- まず同じデータで投入件数、経過時間、警告数、反映後の件数・集計値を比較します。測定用のデータや条件を固定し、速さだけで成功を判定しません。
- バッチ件数は、例えば数百行から段階的に増やして比較します。これは目安であり最適値ではありません。長い文字列やBLOBがある場合は行数だけでなく、SQLのバイト数、ドライバーのメモリ使用量、max_allowed_packetも確認します。
- 同時投入数を増やすと、ロック競合、デッドロック、ディスクI/O、ログ、レプリカの遅延が増える場合があります。1接続での基準値を取り、負荷監視しながら調整します。
- コミット回数を減らすとオーバーヘッドを減らせる場合がありますが、ロックやログが不要になるわけではありません。大きなトランザクションは失敗時の取り消しにも時間がかかります。
ALTER TABLE ... DISABLE KEYSは、MyISAMの非UNIQUEインデックス更新を制御する機能です。InnoDBの全インデックスや外部キーをまとめて無効にする機能ではありません。この記事のInnoDB例では使いません。制約チェックやログを無効化する操作も、汎用的な高速化手順として追加しないでください。
取り込みの記録とセキュリティ
- 必要な表・ファイルだけを扱える専用の実行経路を使い、接続情報やパスワードをSQLやログに書き込みません。CSVにも機密データが含まれ得るので、保存場所・送信先・保存期間を確認します。
- 取込ID、入力ファイルの識別情報、入力件数、反映件数、除外・更新件数、警告数、開始・終了時刻、成功・失敗を記録します。エラー調査に必要な行番号やキーは残しつつ、個人情報や全文データを不要にログへ複写しません。
- 確定後の取り消しはROLLBACKではできません。バックアップ、復元確認、再実行ルール、重複防止の仕組みを事前に用意します。
MySQL Enterprise Auditのaudit_logは商用のMySQL Enterprise Editionに含まれる拡張で、すべてのMySQL 8.x環境で同じように使えるとは限りません。製品・提供範囲を確認し、既存の監査方針やマネージドDBの機能に合わせます。データ取込のために監査プラグインを無条件でインストールしたり、グローバル設定を変更したりする必要はありません。
まとめ
大量データの取り込みは、DBMSに合った方式を選び、ステージングへの投入、警告確認、データ検証、トランザクション内での反映に分けると問題を追いやすくなります。MySQLではLOCALのエラー処理、CSVの入力仕様、重複時の方針を特に確認しましょう。小さな架空データでROLLBACKまで確かめてから、件数とバイト数を見ながら段階的に規模を上げることが重要です。
コメント