MYSQLi bind_result 分配过多内存

发布于 2024-10-18 16:58:54 字数 1483 浏览 3 评论 0原文

我试图从 MYSQL 中获取多行，但是当将变量绑定到结果时，MYSQLi 会耗尽内存来分配，因为它尝试一次获取所有行并缓冲完整的 LONGBLOB 大小，即使不需要也是如此。

此处也讨论了该错误。一张海报似乎已经使用 mysqli_stmt_store_result 解决了问题，但没有详细说明具体如何解决（并且 mysqli_stmt_store_result 是一种过程（不是面向对象）方法。

致命错误：允许的内存大小为 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;
  }

感谢您的任何帮助！

原文

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半世蒼涼 2024-10-25 16:58:54

我已经使用以下代码解决了这个问题。仍然存在一个问题，因为一些返回的数据似乎被破坏了，但我相信这值得它自己的问题。对我来说棘手的是需要在 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'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);
  }

回复收藏 0 原文