PDO のプリペアドステートメントで配列をバインドして一括インサートする方法

はじめに

実務にて、生の PHP で連想配列をデータベースにバルクインサート（Bulk insert）する必要があり、配列を foreach、MySQL の INSERT IGNORE INTO … VALUES で SQL 文を作り、PDO の prepare と bindValue で複数行を一気に挿入して解決した情報になります。

検証環境

MAMP 5.5 / マニュアル
PHP 7.3.8 / マニュアル
MySQL 5.7.26 / マニュアル

解説

下記のようなテーブルがある、email カラムは Unique Key です。

このテーブルに下記のような連想配列を INSERT IGNORE したい。

$users = [
  [
    'name' => '雪花ラミィ',
    'email' => 'lamy@example.com',
    'password' => 'lamy'
  ],
  [
    'name' => '桃鈴ねね',
    'email' => 'nene@example.com',
    'password' => 'nene'
  ],
  [
    'name' => '獅白ぼたん',
    'email' => 'botan@example.com',
    'password' => 'botan'
  ],
  [
    'name' => '魔乃アロエ',
    'email' => 'aloe@example.com',
    'password' => 'aloe'
  ],
  [
    'name' => '尾丸ポルカ',
    'email' => 'polka@example.com',
    'password' => 'polka'
  ]
];

結論から言うと、下記のような一括インサートするメソッドを定義すると良い。要点は 2 つ。

<?php
class UserModel {
  private $pdo;

  public function __construct(PDO $pdo) {
    $this->pdo = $pdo;
  }

  public function bulk_insert(array $users): void {
    $sql = 'INSERT IGNORE INTO t_users (name, email, password, created_at) VALUES';

    $p1 = [];
    foreach ($users as $k1 => $v1) {
      $p2 = [];
      foreach ($v1 as $k2 => $v2) $p2[] = ':' . $k2 . $k1;
      $p1[] = '(' . implode(',', $p2) . ', NOW())';
    }
    $sql .= implode(',', $p1);

    try {
      $sth = $this->pdo->prepare($sql);
      foreach ($users as $k1 => $v1) {
        foreach ($v1 as $k2 => $v2) $sth->bindValue(':' . $k2 . $k1, $v2);
      }
      $sth->execute();
    } catch (PDOException $e) {
      exit($e->getMessage());
    }
  }

}

要点 1: 下記の部分で VALUES のSQL 文と名前付きプレースホルダを foreach と implode で作成している。

$p1 = [];
foreach ($users as $k1 => $v1) {
  $p2 = [];
  foreach ($v1 as $k2 => $v2) $p2[] = ':' . $k2 . $k1;
  $p1[] = '(' . implode(',', $p2) . ', NOW())';
}
$sql .= implode(',', $p1);

$sql を var_dump すると、下記のように VALUES の後ろが key + 連番になっている。

(:name0,:email0,:password0, NOW()),(:name1,:email1,:password1, NOW()),...

要点 2: 下記の部分で名前付きプレースホルダに対して foreach と bindValue でバインドしている。

foreach ($users as $k1 => $v1) {
  foreach ($v1 as $k2 => $v2) $sth->bindValue(':' . $k2 . $k1, $v2);
}

呼び出し側は下記のように記述している。

<?php
require_once __DIR__ . '/constants.php';
require_once __DIR__ . '/classes/UserModel.php';

$dsn = 'mysql:host=' . DB_CONF['host'] . ';dbname=' . DB_CONF['dbname'] . ';charset=' . DB_CONF['charset'];
$user = DB_CONF['user'];
$pass = DB_CONF['pass'];
$options = [
  PDO::ATTR_EMULATE_PREPARES => false
];

$users = [
  [
    'name' => '雪花ラミィ',
    'email' => 'lamy@example.com',
    'password' => 'lamy'
  ],
  [
    'name' => '桃鈴ねね',
    'email' => 'nene@example.com',
    'password' => 'nene'
  ],
  [
    'name' => '獅白ぼたん',
    'email' => 'botan@example.com',
    'password' => 'botan'
  ],
  [
    'name' => '魔乃アロエ',
    'email' => 'aloe@example.com',
    'password' => 'aloe'
  ],
  [
    'name' => '尾丸ポルカ',
    'email' => 'polka@example.com',
    'password' => 'polka'
  ]
];

$pdo = new PDO($dsn, $user, $pass);
$user_model = new UserModel($pdo);
$users = $user_model->bulk_insert($users);

定数ファイルは下記のように記述している。

<?php
const DB_CONF = [
  'host' => 'localhost',
  'dbname' => 'test',
  'charset' => 'utf8mb4',
  'user' => 'root',
  'pass' => 'root'
];

実行すると連想配列がデータベースに一括インサートされる。

再度実行すると INSERT IGNORE によって email カラムの値重複はスキップされる。

以上です。

サイバー・ノーガード戦法編

よい子はマネしちゃいけないサイバー・ノーガード解法も残しておきます。

<?php
class UserModel {
  private $pdo;

  public function __construct(PDO $pdo) {
    $this->pdo = $pdo;
  }

  public function bulk_insert(array $users): void {
    $sql = 'INSERT IGNORE INTO t_users (name, email, password, created_at) VALUES';

    // $p1 = [];
    // foreach ($users as $k1 => $v1) {
    //   $p2 = [];
    //   foreach ($v1 as $k2 => $v2) $p2[] = ':' . $k2 . $k1;
    //   $p1[] = '(' . implode(',', $p2) . ', NOW())';
    // }
    // $sql .= implode(',', $p1);

    foreach ($users as $user) {
      $sql .= "(
        '{$user['name']}',
        '{$user['email']}',
        '{$user['password']}',
        NOW()
      ),";
    }
    $sql = rtrim($sql, ',');

    try {
      $sth = $this->pdo->prepare($sql);
      // foreach ($users as $k1 => $v1) {
      //   foreach ($v1 as $k2 => $v2) $sth->bindValue(':' . $k2 . $k1, $v2);
      // }
      $sth->execute();
    } catch (PDOException $e) {
      exit($e->getMessage());
    }
  }

}

どこからか「SQL Injection」と書かれたマサカリが飛んで来るでしょう。