MySQL 儲存二進位檔案（圖片/HTML）完整實作：BLOB 欄位設計、PHP 上傳與輸出範例

資料表設計

CREATE DATABASE binary_data;
USE binary_data;

CREATE TABLE binary_data (
    id          INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    description CHAR(50),
    bin_data    LONGBLOB,
    filename    CHAR(50),
    filesize    CHAR(20),
    filetype    CHAR(50),
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• LONGBLOB：最大 4GB，適用圖片、PDF、HTML 等二進位資料。
• 建議另存 filename、filesize、filetype 便於下載/瀏覽。

PHP 上傳儲存 (store.php)

<?php
$host='localhost'; $user='root'; $pass='pass'; $db='binary_data';
$conn = mysqli_connect($host, $user, $pass, $db) or die('連線失敗');

if (isset($_POST['submit'])) {
    $desc = mysqli_real_escape_string($conn, $_POST['description']);
    $file = $_FILES['form_data'];

    // 讀取二進位內容
    $data = file_get_contents($file['tmp_name']);
    $data = mysqli_real_escape_string($conn, $data);

    $sql = "INSERT INTO binary_data (description, bin_data, filename, filesize, filetype)
            VALUES ('$desc', '$data', '{$file['name']}', '{$file['size']}', '{$file['type']}')";
    mysqli_query($conn, $sql) or die('寫入失敗: '.mysqli_error($conn));
    $id = mysqli_insert_id($conn);
    echo "儲存成功，資料庫 ID: $id";
    mysqli_close($conn);
} else {
?>

  說明: 

  檔案: 

  
  

<?php
$host='localhost'; $user='root'; $pass='pass'; $db='binary_data';
$conn = mysqli_connect($host, $user, $pass, $db) or die('連線失敗');

$id = (int)$_GET['id'];
$sql = "SELECT bin_data, filetype FROM binary_data WHERE id=$id";
$res = mysqli_query($conn, $sql) or die('查詢失敗');
$row = mysqli_fetch_assoc($res);

if ($row) {
    header("Content-Type: {$row['filetype']}");
    header("Content-Length: " . strlen($row['bin_data']));
    echo $row['bin_data'];
} else {
    header("HTTP/1.0 404 Not Found");
    echo "File not found";
}
mysqli_close($conn);
?>

<!-- 直接當圖片來源 -->
<img src="getdata.php?id=3" alt="資料庫圖片">

<!-- 下載連結 -->
<a href="getdata.php?id=5" download>下載檔案</a>