萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 直接儲存二進位檔案 (圖片/文件) 實作教程

架構決策:資料庫存檔 vs 檔案系統/物件存儲

維度資料庫 (BLOB)檔案系統/物件存儲 (S3/MinIO/OSS)
事務一致性✅ ACID 原子性❌ 需應用層補償
備份/還原單一檔案/邏輯備份需分離備份策略
效能 (大檔)❌ 緩衝池污染、複制延遲✅ 流式傳輸、CDN 加速
查詢/檢索✅ SQL 關聯查詢需元資料表映射
擴展性受限於 DB 磁碟/記憶體近乎無限水平擴展
成本高 (高效能儲存)低 (廉價儲存)
建議:< 1MB 且強一致性需求 (身份證、合同、小圖標) → 可考慮 BLOB;其餘 → 物件存儲 + DB 存元資料 (URL、Hash、大小、MIME)。

資料庫存檔實作 (教學用)

1. 建表

CREATE DATABASE binary_data CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE binary_data;

CREATE TABLE `binary_data` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(255) DEFAULT '',
  `bin_data` LONGBLOB NOT NULL,          -- 最大 4GB
  `filename` VARCHAR(255) NOT NULL,
  `filesize` BIGINT UNSIGNED NOT NULL,
  `filetype` VARCHAR(100) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_filename` (`filename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. PHP 上傳存入 (PDO, Prepared Statement)

 PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['file'])) {
    $file = $_FILES['file'];
    if ($file['error'] !== UPLOAD_ERR_OK) {
        die("上傳錯誤: " . $file['error']);
    }
    
    // 安全檢查
    $allowed = ['image/jpeg', 'image/png', 'image/gif', 'application/pdf'];
    $finfo = finfo_open(FILEINFO_MIME_TYPE);
    $mime = finfo_file($finfo, $file['tmp_name']);
    if (!in_array($mime, $allowed)) {
        die("禁止的檔案類型: $mime");
    }
    
    $data = file_get_contents($file['tmp_name']);
    $stmt = $pdo->prepare(
        "INSERT INTO binary_data (description, bin_data, filename, filesize, filetype)
         VALUES (?, ?, ?, ?, ?)"
    );
    $stmt->execute([
        $_POST['description'] ?? '',
        $data,
        $file['name'],
        $file['size'],
        $mime
    ]);
    echo "儲存成功,ID: " . $pdo->lastInsertId();
    exit;
}
?>


說明:
檔案:

3. 讀取輸出 (支援 Range/快取)

prepare("SELECT bin_data, filename, filetype, filesize FROM binary_data WHERE id = ?");
$stmt->execute([$id]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$row) { http_response_code(404); exit("Not found"); }

header("Content-Type: {$row['filetype']}");
header("Content-Length: {$row['filesize']}");
header("Content-Disposition: inline; filename=\"{$row['filename']}\"");
header("Cache-Control: public, max-age=31536000");
header("ETag: \"{$id}-{$row['filesize']}\"");

// 輸出二進位資料
echo $row['bin_data'];

效能優化關鍵點

  • 分離 BLOB 欄位:核心表不含 BLOB,另建 blob_data 表 1:1 關聯,避免掃描主表時讀入大物件
  • 啟用壓縮:ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 (InnoDB 透明壓縮)
  • 串流讀寫:PDO PDO::MYSQL_ATTR_MAX_BUFFER_SIZE / mysqli_stmt_send_long_data 避免記憶體暴增
  • 限制大小:應用層 + DB max_allowed_packet 雙重限制

遷移至物件存儲 (S3 兼容) 範例

// 上傳至 MinIO/S3
$s3->putObject([
    'Bucket' => 'uploads',
    'Key' => "user/{$user_id}/{$uuid}.{$ext}",
    'Body' => fopen($file['tmp_name'], 'r'),
    'ContentType' => $mime,
    'Metadata' => ['original_name' => $file['name']],
]);

-- DB 僅存元資料
INSERT INTO files (user_id, s3_key, filename, size, mime, hash)
VALUES (?, ?, ?, ?, ?, ?);
copyright © 萬盛學電腦網 all rights reserved