將用戶上傳文件直接存入資料庫BLOB字段,雖簡化文件系統管理、便於事務一致性與備份,但需嚴格把控安全與性能。本文提供從表設計、上傳處理、存儲到讀取的完整最佳實踐。
CREATE TABLE `uploaded_files` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`original_name` VARCHAR(255) NOT NULL COMMENT '原始文件名',
`mime_type` VARCHAR(100) NOT NULL COMMENT 'MIME類型',
`size` INT UNSIGNED NOT NULL COMMENT '文件大小(字節)',
`content` LONGBLOB NOT NULL COMMENT '文件二進制內容',
`uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`uploaded_by` INT UNSIGNED NOT NULL COMMENT '上傳用戶ID',
INDEX `idx_user_time` (`uploaded_by`, `uploaded_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
<?php
// 配置
const MAX_SIZE = 10 * 1024 * 1024; // 10MB
const ALLOWED_MIMES = ['image/jpeg', 'image/png', 'application/pdf'];
const UPLOAD_DIR = __DIR__ . '/tmp_uploads/'; // 臨時目錄,定期清理
// 1. 驗證
if (!isset($_FILES['file']) || $_FILES['file']['error'] !== UPLOAD_ERR_OK) {
throw new RuntimeException('上傳失敗: ' . ($_FILES['file']['error'] ?? '未知錯誤'));
}
$file = $_FILES['file'];
if ($file['size'] > MAX_SIZE) throw new RuntimeException('文件過大');
$finfo = new finfo(FILEINFO_MIME_TYPE);
$mime = $finfo->file($file['tmp_name']);
if (!in_array($mime, ALLOWED_MIMES, true)) throw new RuntimeException('文件類型不允許');
// 2. 讀取內容(流式讀取大文件)
$content = '';
$fp = fopen($file['tmp_name'], 'rb');
while (!feof($fp)) {
$content .= fread($fp, 8192);
}
fclose($fp);
// 3. 存入資料庫(預處理防注入)
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
]);
$stmt = $pdo->prepare(
'INSERT INTO uploaded_files (original_name, mime_type, size, content, uploaded_by) VALUES (?, ?, ?, ?, ?)'
);
$stmt->execute([
$file['name'],
$mime,
$file['size'],
$content,
$_SESSION['user_id'] ?? 0,
]);
echo '文件已存入,ID: ' . $pdo->lastInsertId();
$stmt = $pdo->prepare('SELECT original_name, mime_type, content FROM uploaded_files WHERE id = ?');
$stmt->execute([$_GET['id'] ?? 0]);
$file = $stmt->fetch(PDO::FETCH_ASSOC);
if ($file) {
header('Content-Type: ' . $file['mime_type']);
header('Content-Disposition: attachment; filename="' . rawurlencode($file['original_name']) . '"');
header('Content-Length: ' . strlen($file['content']));
echo $file['content'];
} else {
http_response_code(404);
echo '文件不存在';
}
finfo而非依賴$_FILES['type'](可偽造)。upload_max_filesize、post_max_size、Nginxclient_max_body_size三層限制。unlink($tmp_name),防止殘留佔用磁碟。合理權衡資料庫存儲與對象存儲,構建安全高效的文件上傳服務。