【PHP】PDOのprepareで複数行を一括INSERTする方法

2019年12月2日

PHPのPDOオブジェクトで、prepareを使用しながら複数行を一括でインサートする処理を何度か使用する機会がありましたので備忘録として残しておきます。

1つ1つINSERTするよりも実行時間が大幅に削減できるため、覚えておくと便利です。

また、SQLインジェクション攻撃への対策にもなりますので、ユーザーから送信された複数データを登録したい場合においても有用です。

参考 PDOでprepareの使い方【PHP】

参考 PHPでSQLインジェクション攻撃の対策方法【PDO】

今回の例における、MySQLテーブルは以下の構成のものを使用します。

複数カラムに対応したprepareの複数行INSERT

挿入予定のデータを二次元配列で設定する

挿入するデータを配列で、かつ添え字（キー）はMySQLデータベースのテーブルのカラム名に合わせて設定を行います。

$aryInsert = [];
$aryInsert[] = ['name' => 'のび太', 'gender' => 'man', 'type' => 'human'];
$aryInsert[] = ['name' => 'ドラえもん', 'gender' => 'man', 'type' => 'robot'];
$aryInsert[] = ['name' => 'ジャイアン', 'gender' => 'man', 'type' => 'human'];
$aryInsert[] = ['name' => 'スネ夫', 'gender' => 'man', 'type' => 'human'];
$aryInsert[] = ['name' => 'しずか', 'gender' => 'woman', 'type' => 'human'];
$aryInsert[] = ['name' => 'ドラミ', 'gender' => 'woman', 'type' => 'robot'];

設定した配列を展開するとこんな感じです。

foreachでSQL文とbind処理を繰り返す

先ほど用意した配列を使用して、件数分のSQL文の追記とbind処理を行います。

繰り返し処理はお馴染みのforeach文を使用します。

参考 PHPのforeachを徹底解説【サンプルコード有】

//配列設定
$aryInsert = [];
$aryInsert[] = ['name' => 'のび太', 'gender' => 'man', 'type' => 'human'];
$aryInsert[] = ['name' => 'ドラえもん', 'gender' => 'man', 'type' => 'robot'];
$aryInsert[] = ['name' => 'ジャイアン', 'gender' => 'man', 'type' => 'human'];
$aryInsert[] = ['name' => 'スネ夫', 'gender' => 'man', 'type' => 'human'];
$aryInsert[] = ['name' => 'しずか', 'gender' => 'woman', 'type' => 'human'];
$aryInsert[] = ['name' => 'ドラミ', 'gender' => 'woman', 'type' => 'robot'];

$aryColumn = array_keys($aryInsert[0]);

//SQL文作成処理
$sql = "INSERT INTO
        doraemon_users
        (".implode(',', $aryColumn).")
        VALUES";

$arySql1 = [];
//行の繰り返し
foreach($aryInsert as $key1 => $val1){
    $arySql2 = [];
    //列（カラム）の繰り返し
    foreach($val1 as $key2 => $val2){
        $arySql2[] = ':'.$key2.$key1;
    }
    $arySql1[] = '('.implode(',', $arySql2).')';
}

$sql .= implode(',', $arySql1);

//bind処理
$sth = $pdo -> prepare($sql);
foreach($aryInsert as $key1 => $val1){
    foreach($val1 as $key2 => $val2){
        $sth -> bindValue(':'.$key2.$key1, $val2);
    }
}

//実行処理
$sth -> execute();

二重ループで少しややこしいですが、実際に実行されるクエリは以下の形となります。

$sql = "INSERT INTO
        doraemon_users
        (name,gender,type)
        VALUES
        (:name0,:gender0,:type0),(:name1,:gender1,:type1),(:name2,:gender2,:type2),(:name3,:gender3,:type3),(:name4,:gender4,:type4),(:name5,:gender5,:type5)";

挿入行とカラム毎に配列を繰り返し、implode関数で配列を結合し、SQL文を生成しています。

参考 PHPのimplodeで配列を結合し文字列にする方法

そして、挿入される行と列（カラム）に対してすべてにバインド処理を行うため、こちらでも二重でループを行います。
下記はループによって、実際に行うバインド処理の数です。

$sth -> bindValue(':name0', 'のび太');
$sth -> bindValue(':gender0', 'man');
$sth -> bindValue(':type0', 'human');
$sth -> bindValue(':name1', 'ドラえもん');
$sth -> bindValue(':gender1', 'man');
$sth -> bindValue(':type1', 'robot');
$sth -> bindValue(':name2', 'ジャイアン');
$sth -> bindValue(':gender2', 'man');
$sth -> bindValue(':type2', 'human');
$sth -> bindValue(':name3', 'スネ夫');
$sth -> bindValue(':gender3', 'man');
$sth -> bindValue(':type3', 'human');
$sth -> bindValue(':name4', 'しずか');
$sth -> bindValue(':gender4', 'woman');
$sth -> bindValue(':type4', 'human');
$sth -> bindValue(':name5', 'ドラミ');
$sth -> bindValue(':gender5', 'woman');
$sth -> bindValue(':type5', 'robot');

bindValueの第一引数が被らないように、配列の添え字（キー）を結合することで、全クエリ違う値として登録できます。

ON DUPLICATE KEY UPDATEで上書き処理を追記したい場合

例えばテーブルのカラム「name」にUNIQUE制約があり、その場合は他のカラムを上書きしたい場合は「ON DUPLICATE KEY UPDATE」の記述を末尾に追記します。

参考 【PHP】PDOで「ON DUPLICATE KEY UPDATE」の使い方

//配列設定
$aryInsert = [];
$aryInsert[] = ['name' => 'のび太', 'gender' => 'man', 'type' => 'human'];
$aryInsert[] = ['name' => 'ドラえもん', 'gender' => 'man', 'type' => 'robot'];
$aryInsert[] = ['name' => 'ジャイアン', 'gender' => 'man', 'type' => 'human'];
$aryInsert[] = ['name' => 'スネ夫', 'gender' => 'man', 'type' => 'human'];
$aryInsert[] = ['name' => 'しずか', 'gender' => 'woman', 'type' => 'human'];
$aryInsert[] = ['name' => 'ドラミ', 'gender' => 'woman', 'type' => 'robot'];

$aryColumn = array_keys($aryInsert[0]);

//SQL文作成処理
$sql = "INSERT INTO
        doraemon_users
        (".implode(',', $aryColumn).")
        VALUES";

$arySql1 = [];
//行の繰り返し
foreach($aryInsert as $key1 => $val1){
    $arySql2 = [];
    //列（カラム）の繰り返し
    foreach($val1 as $key2 => $val2){
        $arySql2[] = ':'.$key2.$key1;
    }
    $arySql1[] = '('.implode(',', $arySql2).')';
}

$sql .= implode(',', $arySql1);

//既にnameが存在する場合は、gender、typeを上書き
$sql .= ' ON DUPLICATE KEY UPDATE
        gender = VALUES(gender)
        ,type = VALUES(type)';

//bind処理
$sth = $pdo -> prepare($sql);
foreach($aryInsert as $key1 => $val1){
    foreach($val1 as $key2 => $val2){
        $sth -> bindValue(':'.$key2.$key1, $val2);
    }
}

//実行処理
$sth -> execute();