您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

PHP 實作檔案存入 MySQL 長整型欄位：上傳、儲存與讀取完整範例

編輯：MySQL 資料庫相關知識

資料庫儲存二進位檔案（圖片、文件）的典型做法是使用 LONGBLOB 欄位。本文提供完整 PHP 範例，涵蓋資料表建立、HTML 上傳表單、檔案寫入資料庫、讀取輸出全流程。

1. 資料表結構

CREATE TABLE files (
    id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(64),
    filesize INT(16),
    filetype VARCHAR(64),
    filecontent LONGBLOB,
    remark TEXT
);

2. 系統參數設定

$DB_SERVER = "127.0.0.1";
$DB_USER = "root";
$DB_PASS = "";
$DB_NAME = "article";
$TABLE_NAME = "files";
$HANDLER_SCRIPT = "view_file.php";  // 讀取顯示腳本

3. 上傳表單 (HTML)

<form enctype="multipart/form-data" method="post">
  <input type="hidden" name="MAX_FILE_SIZE" value="10485760"> <!-- 10MB -->
  <label>上傳檔案: <input type="file" name="userfile"></label><br>
  <label>檔案說明: <textarea name="description" rows="4" cols="40"></textarea></label><br>
  <input type="submit" name="submit" value="上傳">
</form>

4. 處理上傳並寫入資料庫

if (isset($_POST['submit'])) {
    $conn = mysqli_connect($DB_SERVER, $DB_USER, $DB_PASS, $DB_NAME);
    if (!$conn) die("連線失敗: " . mysqli_connect_error());

    $file = $_FILES['userfile'];
    $fp = fopen($file['tmp_name'], 'rb');
    $content = fread($fp, $file['size']);
    fclose($fp);

    // 必須轉義二進位資料
    $content = mysqli_real_escape_string($conn, $content);
    $filename = mysqli_real_escape_string($conn, $file['name']);
    $description = mysqli_real_escape_string($conn, $_POST['description']);

    $sql = "INSERT INTO $TABLE_NAME (filename, filesize, filetype, filecontent, remark)
            VALUES ('$filename', {$file['size']}, '{$file['type']}', '$content', '$description')";

    if (mysqli_query($conn, $sql)) {
        $id = mysqli_insert_id($conn);
        echo "檔案已儲存，資料庫 ID: $id";
    } else {
        echo "錯誤: " . mysqli_error($conn);
    }
    mysqli_close($conn);
}

5. 讀取並輸出檔案


// view_file.php?id=123
$id = (int)($_GET['id'] ?? 0);
if ($id > 0) {
    $conn = mysqli_connect($DB_SERVER, $DB_USER, $DB_PASS, $DB_NAME);
    $sql = "SELECT filename, filetype, filecontent FROM $TABLE_NAME WHERE id = $id";
    $result = mysqli_query($conn, $sql);
    if ($row = mysqli_fetch_assoc($result)) {
        header("Content-Type: " . $row['filetype']);
        header("Content-Disposition: inline; filename=\"" . $row['filename'] . "\"");
        echo $row['filecontent'];
    } else {
        header("HTTP/1.0 404 Not Found");
        echo "檔案不存在";
    }
    mysqli_close($conn);
}