通过PHP PDO将多个值插入MySQL数据库 - Insert multiple values via PHP PDO into MySQL database

我想通过PHP数据对象一次插入20个值。目前我是这样做的，看起来非常丑陋：

    $stmt = $this->pdo->prepare("INSERT INTO <tablename> (`uid`, `mid`, `status`) 
                                VALUES  (:uid, :mid1, :status), (:uid, :mid2, :status), 
                                        (:uid, :mid3, :status), (:uid, :mid4, :status),
                                        (:uid, :mid5, :status), ... and so on ...");
    $stmt->bindParam(':uid', $this->uid);
    $random_mid = array_rand(range(1,20), 5);
    $ref = new Mooney($random_mid[0]);
    $myMid1 = $ref->getMid();
    $stmt->bindParam(':mid1', $myMid1);
    $ref = new Mooney($random_mid[1]);
    $myMid2 = $ref->getMid();
    $stmt->bindParam(':mid2', $myMid2);
    $ref = new Mooney($random_mid[2]);
    $myMid3 = $ref->getMid();
    $stmt->bindParam(':mid3', $myMid3);
    ... and so on ...
    $status = 'open'; // default starting value of status
    $stmt->bindParam(':status', $status);
    $stmt->execute();

使用for循环是否有更好的解决方案？希望您能提供更好的解决方案/更漂亮的解决方案。感谢您的时间和帮助！

编辑1：现在多亏了@Jurik，代码可以工作，看起来像这样：

private function insertValuesAtOnce() {
    $stmt = $this->pdo->prepare('INSERT INTO `<tablename>` (`uid`, `mid`, `status`) VALUES  (:UID, :MID, :STATUS)');
    $random_mid = $this->UniqueRandomNumbersWithinRange(1, 20, 7);
    require_once('<some path>');
    $status = 'open';
    foreach($random_mid AS $val){
        $ref = new Mooney($val);
        $myMid = $ref->getMid();
        $stmt->execute(array(
            ':UID' => $this->uid,
            ':MID' => $myMid,
            ':STATUS' => $status
        ));
    }
}

但在我看来，这不是"一次插入多行"，而是更多的"多次插入单行"

编辑2:因为似乎不可能一次插入所有值，所以我将使用该解决方案逐行插入它们，但将其封装到事务中！这将运行时间从03915548324585秒/串行化提高到0074591159820557秒/串行。

private function insertValuesAtOnce() {
    $stmt = $this->pdo->prepare('INSERT INTO `<tablename>` (`uid`, `mid`, `status`) VALUES  (:UID, :MID, :STATUS)');
    $random_mid = $this->UniqueRandomNumbersWithinRange(1, 20, 7);
    require_once('<some path>');
    $status = 'open';
    // Beginn Transaction (ACID)     
    $this->pdo->beginTransaction();
    foreach($random_mid AS $val){
        $ref = new Mooney($val);
        $myMid = $ref->getMid();
        $stmt->execute(array(
            ':UID' => $this->uid,
            ':MID' => $myMid,
            ':STATUS' => $status
        ));
    }
    // End Transaction (ACID)
    $this->pdo->commit();
}

<?php  
  $db = new PDO('mysql:host=localhost;dbname=<SOMEDB>', '<USERNAME>', 'PASSWORD');
  $query = $db->prepare(
    'INSERT INTO `user_mooney` (`uid`, `mid`, `status`)
    VALUES  (:UID, :MID, :STATUS)'
  );
  foreach($random_mid AS $val){
    $ref = new Mooney($val);
    $myMid = $ref->getMid();
    $query->execute(array(
      ':UID' => $uid,
      ':MID' => $myMid,
      ':STATUS' => $status
    ));
  }
  $query->commit();

如果commit抛出错误，您可以调用$query->rollback();，因此您有一个组数据库。

编辑：从问题编辑2中获得正确答案

<?php  
    private function insertValuesAtOnce() {
        $stmt = $this->pdo->prepare('INSERT INTO `<tablename>` (`uid`, `mid`, `status`) VALUES  (:UID, :MID, :STATUS)');
        $random_mid = $this->UniqueRandomNumbersWithinRange(1, 20, 7);
        require_once('<some path>');
        $status = 'open';
        // Beginn Transaction (ACID)     
        $this->pdo->beginTransaction();
        foreach($random_mid AS $val){
            $ref = new Mooney($val);
            $myMid = $ref->getMid();
            $stmt->execute(array(
                ':UID' => $this->uid,
                ':MID' => $myMid,
                ':STATUS' => $status
            ));
        }
        // End Transaction (ACID)
        $this->pdo->commit();
    }