PDO导致内存溢出

Question

为了让脚本在PHP5.5中工作，我实际上将mysql-extension改为PDO。对于mysql扩展，它工作得很好，但是对于PDO，它会导致内存溢出。

下面是我使用的函数：

    protected function getData($table) {
  global $db;
  $insert = '';
  $stmt = $db->query("-- " . __LINE__ . __FILE__ . "
    SELECT *
    FROM   " . $table
  );
  if ($result = $stmt->fetchAll()) {
    $insert_into = "INSERT INTO `" . $table . "` VALUES ". PHP_EOL;
    $insert  = "/*!40000 ALTER TABLE `" . $table . "` DISABLE KEYS */;" . PHP_EOL;
    $insert .= $insert_into;
    $countRow  = 0;
    $split_tmp = '';
    foreach ($result as $row) {
      $insert_tmp = "(";
      foreach ($row as $data) {
        if (!isset($data)) {
          $insert_tmp .= 'NULL,';
        } else if ($data != '') {
          $insert_tmp .= "'" . addslashes($data) . "',";
        } else {
          $insert_tmp .= "'',";
        }
      }
      $insert_tmp = rtrim($insert_tmp, ',') . '),' . PHP_EOL;
      $insert .= $insert_tmp;
      if ($this->querySplit) {
        $split_tmp .= $insert_tmp;
        if ($countRow > $this->maxRow && strlen($split_tmp) > $this->maxLength) {
          $countRow     = 0;
          $split_tmp = '';
          $insert  = rtrim($insert, PHP_EOL . ',') . ';' . PHP_EOL;
          $insert .= "/*!40000 ALTER TABLE `" . $table . "` ENABLE KEYS */;" . PHP_EOL . PHP_EOL;
          $insert .= "/*!40000 ALTER TABLE `" . $table . "` DISABLE KEYS */;" . PHP_EOL;
          $insert .= $insert_into;
        }
      }
      $countRow ++;
    }
    $insert = rtrim($insert, PHP_EOL . ',') . ';' . PHP_EOL;
    $insert  .= "/*!40000 ALTER TABLE `" . $table . "` ENABLE KEYS */;" . PHP_EOL. PHP_EOL;
  }
  return $insert;
}

甚至更改为

$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

帮不上忙。

memory_limit设置为256MB。

Answer 1

在处理大量数据时，使用fetchAll是一个非常糟糕的主意，请使用fetch()进行以下调整

protected function getData($table) {
  global $db;
  $insert = '';
  $stmt = $db->query("-- " . __LINE__ . __FILE__ . "
    SELECT *
    FROM   " . $table
  );
  if ($result = $stmt->rowCount() > 0) {
    $insert_into = "INSERT INTO `" . $table . "` VALUES ". PHP_EOL;
    $insert  = "/*!40000 ALTER TABLE `" . $table . "` DISABLE KEYS */;" . PHP_EOL;
    $insert .= $insert_into;
    $countRow  = 0;
    $split_tmp = '';
    while($row = $stmt->fetch()) {
      $insert_tmp = "(";
      foreach ($row as $data) {
        if (!isset($data)) {
          $insert_tmp .= 'NULL,';
        } else if ($data != '') {
          $insert_tmp .= "'" . addslashes($data) . "',";
        } else {
          $insert_tmp .= "'',";
        }
      }
      $insert_tmp = rtrim($insert_tmp, ',') . '),' . PHP_EOL;
      $insert .= $insert_tmp;
      if ($this->querySplit) {
        $split_tmp .= $insert_tmp;
        if ($countRow > $this->maxRow && strlen($split_tmp) > $this->maxLength) {
          $countRow     = 0;
          $split_tmp = '';
          $insert  = rtrim($insert, PHP_EOL . ',') . ';' . PHP_EOL;
          $insert .= "/*!40000 ALTER TABLE `" . $table . "` ENABLE KEYS */;" . PHP_EOL . PHP_EOL;
          $insert .= "/*!40000 ALTER TABLE `" . $table . "` DISABLE KEYS */;" . PHP_EOL;
          $insert .= $insert_into;
        }
      }
      $countRow ++;
    }
    $insert = rtrim($insert, PHP_EOL . ',') . ';' . PHP_EOL;
    $insert  .= "/*!40000 ALTER TABLE `" . $table . "` ENABLE KEYS */;" . PHP_EOL. PHP_EOL;
  }
  return $insert;
}