MYSQLi bind_result分配过多的内存 [英] MYSQLi bind_result allocates too much memory

问题描述

我正在尝试从MYSQL提取多行，但是当将变量绑定到结果时，MYSQLi会耗尽内存以进行分配，因为它试图一次获取所有行并缓冲整个LONGBLOB大小，即使在不必要时也是如此. /p>

还在此处中讨论了该错误.一位张贴者似乎已使用mysqli_stmt_store_result解决了问题，但并未详细说明(mysqli_stmt_store_result是一种程序化方法(不是OO)方法.

致命错误:允许的内存大小为 134217728字节已用尽(尝试 分配4294967296字节)

理想情况下，无论如何我还是更愿意使用fetch_object()，但是我不知道如何使其与已准备好的语句一起运行.

  public function display() {
    $page_offset = ($this->get_page_number()- 1)
                      * $this->notes_per_page;
    if ($page_offset < 0) {
      $page_offset = 0;
    }
    $sql = "SELECT title, date_posted, text, url
              FROM notes ORDER BY date_posted DESC
              LIMIT ?, ?";
    $results = $this->query($sql, "ii", $page_offset, $this->notes_per_page);
    $results->bind_result(&$title, &$date_posted, &$text, &$url);
    //while ($row = $result->fetch_object()) { //store_result()) {
      //echo 'success';
      //var_dump($row);
    //}
    //$this->write($results);
  }

  // Here is the query function that $this->db->query() above refers to.
  public function query() {
    $args = func_get_args();
    $statement = $this->db->prepare($args[0]);
    $args = array_slice($args, 1);
    call_user_func_array(array($statement, 'bind_param'), &$args);
    $statement->execute();
    return $statement;
  }

感谢所有帮助！

解决方案

我已经通过使用以下代码解决了这个问题.由于某些返回的数据似乎已损坏，仍然存在问题，但是我认为这值得提出自己的问题.对我来说棘手的是，需要在mysqli对象上调用store_result()，而在语句上调用fetch_object().

  public function display() {
    $page_offset = ($this->get_page_number()- 1)
                      * $this->notes_per_page;
    if ($page_offset < 0) {
      $page_offset = 0;
    }
    $sql = "SELECT title, date_posted, text, url
              FROM notes ORDER BY date_posted DESC
              LIMIT ?, ?";
    $results = $this->query($sql, "ii", $page_offset, $this->notes_per_page);
    $results = $this->db->store_result();
    while ($row = $results->fetch_object()) {
      var_dump($row);
    }
    //$this->write($results);
  }

I'm trying to fetch multiple rows from MYSQL, but when binding variables to the result, MYSQLi runs out of memory to allocate as it tries to fetch all rows at once and buffers the full LONGBLOB size even when not necessary.

The error is also discussed here. One poster seems to have solved the problem using mysqli_stmt_store_result, but does not elaborate as to exactly how (and mysqli_stmt_store_result is a procedural (not OO), method.

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4294967296 bytes)

Ideally, I'd prefer to be using fetch_object() anyway, but I can't figure out how to get it to function with my prepared statement.

  public function display() {
    $page_offset = ($this->get_page_number()- 1)
                      * $this->notes_per_page;
    if ($page_offset < 0) {
      $page_offset = 0;
    }
    $sql = "SELECT title, date_posted, text, url
              FROM notes ORDER BY date_posted DESC
              LIMIT ?, ?";
    $results = $this->query($sql, "ii", $page_offset, $this->notes_per_page);
    $results->bind_result(&$title, &$date_posted, &$text, &$url);
    //while ($row = $result->fetch_object()) { //store_result()) {
      //echo 'success';
      //var_dump($row);
    //}
    //$this->write($results);
  }

  // Here is the query function that $this->db->query() above refers to.
  public function query() {
    $args = func_get_args();
    $statement = $this->db->prepare($args[0]);
    $args = array_slice($args, 1);
    call_user_func_array(array($statement, 'bind_param'), &$args);
    $statement->execute();
    return $statement;
  }

Thanks for any and all help!

解决方案

I've solved this by using the following code. There is still a problem as some returned data appears mangled, but I believe that deserves its own question. What was tricky for me was that store_result() needs to be called on the mysqli object, while fetch_object() needs to be called on the statement.

  public function display() {
    $page_offset = ($this->get_page_number()- 1)
                      * $this->notes_per_page;
    if ($page_offset < 0) {
      $page_offset = 0;
    }
    $sql = "SELECT title, date_posted, text, url
              FROM notes ORDER BY date_posted DESC
              LIMIT ?, ?";
    $results = $this->query($sql, "ii", $page_offset, $this->notes_per_page);
    $results = $this->db->store_result();
    while ($row = $results->fetch_object()) {
      var_dump($row);
    }
    //$this->write($results);
  }

这篇关于MYSQLi bind_result分配过多的内存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！