MySQL 儲存二進位檔案完整指南：BLOB 欄位、PHP 讀寫與大檔案突破

前言：資料庫存檔的優缺點

將圖片、PDF、文件等二進位檔案存入 MySQL BLOB 欄位，優點：交易一致性、備份簡單、權限控制集中、跨伺服器同步容易；缺點：資料庫膨脹、備份還原慢、記憶體壓力大、不利 CDN 分發。適用場景：檔案數量中等、需強一致性、存取頻率低、檔案較小（< 1MB）。

資料表設計

CREATE TABLE `file_store` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `filename` VARCHAR(255) NOT NULL COMMENT '原始檔名',
    `mime_type` VARCHAR(100) NOT NULL COMMENT 'MIME 類型',
    `file_size` INT UNSIGNED NOT NULL COMMENT '檔案大小（位元組）',
    `data` LONGBLOB NOT NULL COMMENT '二進位內容',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_filename` (`filename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='二進位檔案儲存表';

BLOB 型別選擇：

PHP 寫入檔案（含安全防護）

<?php
/**
 * 將檔案存入資料庫
 *
 * @param PDO $pdo 資料庫連線
 * @param string $filePath 本地檔案路徑
 * @return int|false 插入 ID 或失敗
 */
function storeFile(PDO $pdo, string $filePath) {
    // 1. 基本驗證
    if (!file_exists($filePath) || !is_readable($filePath)) {
        throw new InvalidArgumentException("File not found or not readable: $filePath");
    }

    $fileSize = filesize($filePath);
    if ($fileSize === 0) {
        throw new RuntimeException("File is empty: $filePath");
    }

    // 2. 檢測 MIME 類型（防偽裝）
    $finfo = new finfo(FILEINFO_MIME_TYPE);
    $mimeType = $finfo->file($filePath);
    $allowedMimes = ['image/jpeg', 'image/png', 'image/gif', 'application/pdf', 'text/plain'];
    if (!in_array($mimeType, $allowedMimes, true)) {
        throw new RuntimeException("Disallowed file type: $mimeType");
    }

    // 3. 讀取檔案內容（大檔案建議分塊讀取，此處示範小檔）
    $data = file_get_contents($filePath);
    if ($data === false) {
        throw new RuntimeException("Failed to read file: $filePath");
    }

    // 4. 使用 Prepared Statement 防 SQL 注入（不需 addslashes）
    $sql = "INSERT INTO `file_store` (`filename`, `mime_type`, `file_size`, `data`) VALUES (?, ?, ?, ?)";
    $stmt = $pdo->prepare($sql);

    // 5. 綁定參數：LOB 需用 PDO::PARAM_LOB
    $filename = basename($filePath);
    $stmt->bindValue(1, $filename, PDO::PARAM_STR);
    $stmt->bindValue(2, $mimeType, PDO::PARAM_STR);
    $stmt->bindValue(3, $fileSize, PDO::PARAM_INT);
    $stmt->bindValue(4, $data, PDO::PARAM_LOB);

    if (!$stmt->execute()) {
        throw new RuntimeException("Insert failed: " . implode(', ', $stmt->errorInfo()));
    }

    return (int)$pdo->lastInsertId();
}

// 使用範例
try {
    $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false, // 使用真實預備語句
    ]);
    $id = storeFile($pdo, '/path/to/image.jpg');
    echo "File stored with ID: $id\n";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

PHP 讀取/輸出檔案

<?php
/**
 * 從資料庫讀取檔案並輸出（支援 Range 請求、快取標頭）
 */
function serveFile(PDO $pdo, int $id) {
    $sql = "SELECT `filename`, `mime_type`, `file_size`, `data` FROM `file_store` WHERE `id` = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$id]);
    $file = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$file) {
        http_response_code(404);
        echo 'File not found';
        return;
    }

    // 快取標頭
    header('Content-Type: ' . $file['mime_type']);
    header('Content-Length: ' . $file['file_size']);
    header('Content-Disposition: inline; filename="' . rawurlencode($file['filename']) . '"');
    header('Cache-Control: public, max-age=31536000'); // 1 年
    header('ETag: "' . md5($file['data']) . '"');
    header('Last-Modified: ' . gmdate('D, d M Y H:i:s', time()) . ' GMT');

    // 支援 If-None-Match / If-Modified-Since
    if (isset($_SERVER['HTTP_IF_NONE_MATCH']) && $_SERVER['HTTP_IF_NONE_MATCH'] === '"' . md5($file['data']) . '"') {
        http_response_code(304);
        return;
    }

    // 輸出二進位資料
    echo $file['data'];
}

大檔案（> 2MB）突破限制

PHP 預設 upload_max_filesize、post_max_size、memory_limit、MySQL max_allowed_packet 皆可能限制大檔案處理。

關鍵設定調整

分塊讀寫策略（避免記憶體爆炸）

// 寫入：使用 PDO::PARAM_LOB + 串流（需 PDO 驅動支援）
$stmt->bindValue(4, fopen($filePath, 'rb'), PDO::PARAM_LOB);

// 讀取：分塊輸出
function serveFileStream(PDO $pdo, int $id) {
    $stmt = $pdo->prepare("SELECT `data`, `mime_type`, `file_size` FROM `file_store` WHERE `id` = ?");
    $stmt->execute([$id]);
    $stmt->bindColumn(1, $lob, PDO::PARAM_LOB);
    $stmt->fetch(PDO::FETCH_BOUND);

    header('Content-Type: ' . $mime_type);
    header('Content-Length: ' . $file_size);

    // 從 LOB 串流讀取並輸出
    while (!feof($lob)) {
        echo fread($lob, 8192);
        flush(); // 立即發送給客戶端
    }
}

替代方案：檔案系統 + 資料庫記錄（推薦生產環境）

大多數現代架構採用 物件儲存（S3/MinIO/GCS）或本地檔案系統 + 資料庫存路徑：

CREATE TABLE `file_meta` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `storage_driver` VARCHAR(20) NOT NULL, -- 'local', 's3', 'minio'
    `bucket` VARCHAR(100) DEFAULT NULL,
    `path` VARCHAR(500) NOT NULL,          -- 相對路徑或物件鍵
    `filename` VARCHAR(255) NOT NULL,
    `mime_type` VARCHAR(100) NOT NULL,
    `file_size` BIGINT UNSIGNED NOT NULL,
    `checksum` VARCHAR(64) NOT NULL,       -- SHA256 校驗
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

總結

MySQL BLOB 儲存檔案在小型專案、強一致性需求下可行，但需精心調整 php.ini、my.cnf、PDO 參數綁定、記憶體管理。生產環境大檔案、高併發、CDN 分發場景，強烈建議採用 物件儲存 + 資料庫元資料 架構，將二進位資料移出關聯式資料庫，發揮各層專長。