當網站採用多台 Web 伺服器(負載均衡)且不使用獨立圖片伺服器時,將圖片二進制數據存入 MySQL BLOB 欄位,可實現跨伺服器自動共享,避免同步檔案系統的複雜性。
| 類型 | 最大容量 | 適用場景 |
|---|---|---|
| TinyBlob | 255 Bytes | 極小圖示 |
| Blob | 64 KB | 縮略圖、頭像 |
| MediumBlob | 16 MB | 一般網頁圖片(推薦) |
| LongBlob | 4 GB | 高解析度原圖 |
CREATE TABLE `photo` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`type` VARCHAR(100) NOT NULL COMMENT 'MIME 類型',
`binarydata` MEDIUMBLOB NOT NULL COMMENT '圖片二進制數據',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
upload_image_todb.php<?php
// 連接資料庫(建議改用 mysqli 或 PDO)
$conn = @mysqli_connect("localhost", "root", "", "demo") or die(mysqli_error($conn));
mysqli_set_charset($conn, 'utf8');
$action = isset($_REQUEST['action']) ? $_REQUEST['action'] : '';
// --- 上傳邏輯 ---
if ($action === 'add') {
$image = mysqli_real_escape_string($conn, file_get_contents($_FILES['photo']['tmp_name']));
$type = $_FILES['photo']['type']; // 例:image/jpeg
$sql = "INSERT INTO photo (type, binarydata) VALUES ('$type', '$image')";
mysqli_query($conn, $sql) or die(mysqli_error($conn));
header('Location: upload_image_todb.php');
exit;
}
// --- 圖片輸出 ---
if ($action === 'show') {
$id = isset($_GET['id']) ? intval($_GET['id']) : 0;
$sql = "SELECT * FROM photo WHERE id = $id";
$res = mysqli_query($conn, $sql) or die(mysqli_error($conn));
if ($row = mysqli_fetch_assoc($res)) {
header('Content-Type: ' . $row['type']);
echo $row['binarydata'];
exit;
}
echo "圖片不存在";
exit;
}
// --- 列表頁 ---
?>
<!DOCTYPE html>
<html>
<head><meta charset="utf-8"><title>圖片上傳到資料庫 Demo</title></head>
<body>
<form method="post" action="upload_image_todb.php" enctype="multipart/form-data">
<p>圖片:<input type="file" name="photo"></p>
<p><input type="hidden" name="action" value="add"><input type="submit" value="上傳"></p>
</form>
<?php
$sql = "SELECT id FROM photo ORDER BY id DESC";
$res = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_assoc($res)) {
// 加時間戳防快取
echo '<p><img src="upload_image_todb.php?action=show&id=' . $row['id'] . '&t=' . time() . '" width="150"></p>';
}
?>
</body></html>
SELECT * 會讀取大量二進制導致記憶體飆升binarydata 欄位,避免 SELECT *max_allowed_packet 足夠大(如 32M)mysqli_store_result vs mysqli_use_result 控制緩衝模式