萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 導出 MySQL 數據到 CSV 文件(指定編碼、大數據流式輸出)

核心需求

將 MySQL 查詢結果導出為 CSV 供下載,需解決:編碼轉換(UTF-8 → GBK/CP1252)、大數據集內存溢出、CSV 字段轉義規範、HTTP 下載響應頭。

流式輸出生產級實現

<?php
/**
 * 導出 MySQL 查詢結果為 CSV 下載
 * @param PDO        $pdo       已連接的 PDO 實例
 * @param string     $sql       SELECT 語句
 * @param array      $params    綁定參數
 * @param string     $filename  下載文件名
 * @param string     $charset   輸出編碼(預設 CP1252 兼容 Excel)
 * @param int        $batchSize 分批讀取行數
 */
function exportCsv(PDO $pdo, string $sql, array $params, string $filename, string $charset = 'CP1252', int $batchSize = 1000): void {
    // 1. 設置響應頭
    header('Content-Type: text/csv; charset=' . $charset);
    header('Content-Disposition: attachment; filename="' . rawurlencode($filename) . '"');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Pragma: public');
    header('Expires: 0');

    // 2. 打開輸出流
    $out = fopen('php://output', 'w');
    
    // 3. 可選:寫入 BOM(Excel 識別 UTF-8 需 BOM)
    if (strtoupper($charset) === 'UTF-8') {
        fwrite($out, "\xEF\xBB\xBF");
    }

    // 4. 準備語句
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);

    // 5. 寫入表頭
    $firstRow = true;
    while (true) {
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if (empty($rows)) break;

        if ($firstRow) {
            fputcsv($out, array_keys($rows[0]));
            $firstRow = false;
        }

        // 6. 分批寫入數據行
        foreach ($rows as $row) {
            // 編碼轉換
            if (strtoupper($charset) !== 'UTF-8') {
                $row = array_map(fn($v) => is_string($v) ? mb_convert_encoding($v, $charset, 'UTF-8') : $v, $row);
            }
            fputcsv($out, $row);
        }
        
        // 釋放語句游標(某些驅動需要)
        $stmt->closeCursor();
        // 如果還有更多結果集(多語句),繼續;否則跳出
        if (!$stmt->nextRowset()) break;
    }

    fclose($out);
    exit;
}

// ===== 使用示例 =====
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false, // 關鍵:非緩衝查詢,流式讀取
]);

exportCsv($pdo, 
    'SELECT id, username, email, created_at FROM users WHERE status = ?', 
    [1], 
    'users_export_' . date('Ymd_His') . '.csv'
);

關鍵技術點

要點說明
php://output直接輸出到 HTTP 響應體,不佔用內存
MYSQL_ATTR_USE_BUFFERED_QUERY => false非緩衝查詢,逐行從 MySQL 服務器拉取,支援百萬級導出
fputcsv()自動處理字段包含逗號、引號、換行符的轉義(雙引號包裹、雙引號轉義)
編碼轉換mb_convert_encoding 逐字段轉換,避免中文亂碼
BOMUTF-8 導出加 BOM 確保 Excel 直接打開不亂碼

常見坑與解決

1. 導出超時

set_time_limit(0); // 取消腳本超時
ini_set('memory_limit', '256M'); // 適當提高內存(流式實際佔用很小)

2. Excel 打開科學計數法/前導零丟失

CSV 本質是純文本,無法控制 Excel 顯示格式。解決方案:

  • 字段前加 \t(製表符)強制文本:fputcsv($out, array_map(fn($v) => "\t$v", $row))
  • 或導出為 HTML 表格/真實 Excel(使用 PhpSpreadsheet)。

3. 大字段截斷

MySQL max_allowed_packet 限制單行大小,超大文本字段需分塊讀取或排除。

總結

流式導出 CSV 核心:非緩衝查詢 + php://output + fputcsv + 編碼轉換。此方案內存佔用恆定(~幾 MB),可支撐千萬行導出。若需複雜格式(合併儲存格、樣式、多工作表),請改用 PhpSpreadsheet 但需注意其高內存佔用。

copyright © 萬盛學電腦網 all rights reserved