使用fputcsv()函数从mysql表写入12,000条记录到csv文件时存在内存和文件大小问题 - Memory and File size issue with writing 12,000 records from mysql table to csv file using fputcsv() function

我有一个12,000条记录的数据库，我想写到csv文件，我在php代码下面尝试过，但文件大小变成980kb数据开始删除后，文件大小开始减少。成功从mysql表中取出数据。csv文件写入问题。请看下面我的代码

<?php
include './connection.php';
set_time_limit(0);
ini_set('memory_limit', '1024M');
$connection;
$db_link;
    $output_file = "db_data.csv";
    try {
        $csv_file = fopen($output_file,"b");
        $value = array('Name','Address','Phone Number','International Phone Number','Website');
            if(!empty($value)){
                fpassthru($csv_file);
                fputcsv($csv_file, $value,',');
                fclose($csv_file);
            }       
    } catch (Exception $e) {
        echo $message = $e->getMessage();
    }

        $connection = new Connection('1.2.3.8','admin','pass','automtn');
        $db_link = $connection->connect();
        $low = 0;
        $high = 100;
        $query = "SELECT name,formatted_address,formatted_phone_number,international_phone_number,website "
                . " FROM `fetch_data` ORDER BY id ASC LIMIT $low OFFSET $high";
        while($result = mysqli_query($db_link,$query)){
            $data = array();
            while ($row = mysqli_fetch_row($result)) {                
                $data[] = $row;                
            }
            write_to_csv($data);
            unset($data);
            $low+=100;
            $high+=100;
            $query = "SELECT name,formatted_address,formatted_phone_number,international_phone_number,website "
                . " FROM `fetch_data` ORDER BY id ASC LIMIT $low OFFSET $high";
        }
function write_to_csv($results) {
    global $output_file;
    $row = 0;
    try {
        $csv_file = fopen($output_file,"b");
        $fetched_data = $results;
        unset($results);
        foreach ($fetched_data as $value) {
            if(!empty($value)){
                fpassthru($csv_file);
                fputcsv($csv_file, $value, ',');
            }            
        }
        fclose($csv_file);
        unset($fetched_data);
    } catch (Exception $e) {
        echo $message = $e->getMessage();
    }
}

?>

每次都写到文件的开头。坦率地说，我很惊讶模式b本身不会抛出错误;除非文档过期。

使用附加模式书写"a"(或"ab")

$csv_file = fopen($output_file,"ab");

既然你保存了一个全局文件名，为什么不省去一些麻烦而保存一个全局文件句柄呢?您不必每次写入时都花时间打开和关闭文件。

问题是我的低高逻辑，我已经用$limit改变了它，它将保持不变，$offset每次增加100，现在工作良好

<?php
include './connection.php';
set_time_limit(0);
ini_set('memory_limit', '1024M');
$connection;
$db_link;
    $output_file = "db_data.csv";
    try {
        $csv_file = fopen($output_file,"a+");
        $value = array('Name','Address','Phone Number','International Phone Number','Website');
            if(!empty($value)){
                fpassthru($csv_file);
                fputcsv($csv_file, $value,',');
                fclose($csv_file);
            }       
    } catch (Exception $e) {
        echo $message = $e->getMessage();
    }

        $connection = new Connection('1.2.3.8','admin','pass','automtn');
        $db_link = $connection->connect();
        $limit = 100;
        $offset = 0;
        $query = "SELECT name,formatted_address,formatted_phone_number,international_phone_number,website "
                . " FROM `fetch_data` ORDER BY id ASC LIMIT $limit OFFSET $offset ";
        while($result = mysqli_query($db_link,$query)){
            $data = array();
            while ($row = mysqli_fetch_row($result)) {                
                $data[] = $row;                
            }
            write_to_csv($data);
            unset($data);
            $offset +=100;
            $query = "SELECT name,formatted_address,formatted_phone_number,international_phone_number,website "
                . " FROM `fetch_data` ORDER BY id ASC LIMIT $low OFFSET $offset ";
        }
function write_to_csv($results) {
    global $output_file;
    $row = 0;
    try {
        $csv_file = fopen($output_file,"a+");
        $fetched_data = $results;
        unset($results);
        foreach ($fetched_data as $value) {
            if(!empty($value)){
                fpassthru($csv_file);
                fputcsv($csv_file, $value, ',');
            }            
        }
        fclose($csv_file);
        unset($fetched_data);
    } catch (Exception $e) {
        echo $message = $e->getMessage();
    }
}

?>