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

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

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

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

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

table名:doraemon_users
id(int/AI) name(VARCHAR) gender(VARCHAR) type(VARCHAR)

複数カラムに対応した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'];

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

Array
(
    [0] => Array
        (
            [name] => のび太
            [gender] => man
            [type] => human
        )

    [1] => Array
        (
            [name] => ドラえもん
            [gender] => man
            [type] => robot
        )

    [2] => Array
        (
            [name] => ジャイアン
            [gender] => man
            [type] => human
        )

    [3] => Array
        (
            [name] => スネ夫
            [gender] => man
            [type] => human
        )

    [4] => Array
        (
            [name] => しずか
            [gender] => woman
            [type] => human
        )

    [5] => Array
        (
            [name] => ドラミ
            [gender] => woman
            [type] => robot
        )

)

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

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

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

PHPのforeach文を徹底解説【サンプルコード有】
PHPで配列の繰り返し処理を行うforeach文の記述についてまとめました。ループ内で使えるcontinue文やbreak文など、様々なケースをサンプルコード付きで詳しく解説しております。
//配列設定
$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'];

//SQL文作成処理
$sql = "INSERT INTO
        doraemon_users
        (
        name
        ,gender
        ,type
        ) 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)";

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

$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」の記述を末尾に追記します。

//配列設定
$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'];

//SQL文作成処理
$sql = "INSERT INTO
        doraemon_users
        (
        name
        ,gender
        ,type
        ) 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();
以上、PDOのprepareで複数行を一括INSERTする方法、でした。

コメント

タイトルとURLをコピーしました