mysqli_query 单个查询产生 PHP 致命错误:允许的内存大小已耗尽 [英] mysqli_query single query gerenating PHP fatal error: Allowed memory size exhausted

问题描述

我有一个包含 300 万条记录的 Mysql 表(名为base")，它仅由 3 个 int(10) 字段组成.

I have a Mysql table (named "base") with 3 million records, which is composed of only 3 int(10) fields.

当我尝试运行这个简单的查询 $result = mysqli_query($db, 'SELECT * FROM base') 时，php 显示错误:

When I try to run this simple query $result = mysqli_query($db, 'SELECT * FROM base'), php shows the error:

PHP 致命错误:允许的内存大小为 134217728 字节已用完(试图分配 10485768 字节)在 C:\xampp\htdocs\combina.php 上第 17 行

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 10485768 bytes) in C:\xampp\htdocs\combina.php on line 17

我认为 mysqli_query 不应该将所有表记录加载到内存中，对吗?

I think mysqli_query should not load all the table records into memory, right?

为什么内存溢出?

我在 Windows 10、PHP 7.2 上使用 XAMPP 安装.

I'm using XAMPP installation on Windows 10, PHP 7.2.

代码如下:

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "comb";

$db = mysqli_connect($servername, $username, $password, $dbname);
if (!$db) {
    die("Falha na conexão: " . mysqli_connect_error());
}
$result = mysqli_query($db, 'SELECT * FROM base');

推荐答案

我研究并意识到 mysqli_query 命令实际上将所有记录加载到 RAM 中.也就是说，对于大表，它实际上会产生内存限制.

I researched and realized that the mysqli_query command actually loads ALL records into RAM. That is, for large tables, it will actually generate a memory limitation.

我找到的解决方案是使用 mysqli_real_query 的组合(这将激活查询而不加载 RAM 中的所有记录，mysqli_use_result(将返回结果对象未缓冲)，最后mysqli_fetch_row 它将逐行读取结果.

The solution I found was to use the combination of mysqli_real_query (which will activate the query without loading all the records in RAM, mysqli_use_result (which will return the resulting object not buffered ) and finally mysqli_fetch_row which will read the result line by line.

这样，所提出问题的解决方案或多或少:

In this way the solution to the presented problem would be more or less:

$db = mysqli_connect($servername, $username, $password, $dbname);
mysqli_real_query($db, 'SELECT * FROM base');
$result = mysqli_use_result($db);
while ($row = mysqli_fetch_row($result)) {
    ...

这篇关于mysqli_query 单个查询产生 PHP 致命错误:允许的内存大小已耗尽的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！