Java + MySQL 圖片存儲與讀取完整實戰：BLOB 類型最佳實踐

資料庫表設計

CREATE TABLE photo (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) COMMENT '名稱',
    photo LONGBLOB COMMENT '照片'  -- TINYBLOB(255B) BLOB(64KB) MEDIUMBLOB(16MB) LONGBLOB(4GB)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

選型建議：一般業務用 MEDIUMBLOB (16MB) 足夠；超大文件建議存對象存儲 (OSS/S3)，庫中存 URL。

工具類：圖片 ↔ 二進制流

public final class ImageUtil {
    private ImageUtil() {}

    /** 文件 → FileInputStream */
    public static FileInputStream toInputStream(String filePath) {
        try {
            return new FileInputStream(filePath);
        } catch (FileNotFoundException e) {
            throw new RuntimeException("文件不存在: " + filePath, e);
        }
    }

    /** InputStream → 文件 (流式寫入，防 OOM) */
    public static void toFile(InputStream in, String destPath) {
        try (FileOutputStream out = new FileOutputStream(destPath)) {
            byte[] buffer = new byte[8192];
            int len;
            while ((len = in.read(buffer)) != -1) {
                out.write(buffer, 0, len);
            }
        } catch (IOException e) {
            throw new RuntimeException("寫入文件失敗: " + destPath, e);
        }
    }
}

存入數據庫 (PreparedStatement + 二進制流)

public class ImageInsert {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
        String user = "root", pwd = "root";

        String sql = "INSERT INTO photo (name, photo) VALUES (?, ?)";

        try (Connection conn = DriverManager.getConnection(url, user, pwd);
             PreparedStatement ps = conn.prepareStatement(sql);
             InputStream in = ImageUtil.toInputStream("D:/temp/photo1.png")) {

            ps.setString(1, "產品圖片_001");
            ps.setBinaryStream(2, in, in.available()); // 關鍵：二進制流 + 長度
            int rows = ps.executeUpdate();
            System.out.println("插入成功: " + rows + " 行");
        } catch (SQLException | IOException e) {
            throw new RuntimeException("存圖失敗", e);
        }
    }
}

從數據庫讀取並還原文件

public class ImageRead {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
        String user = "root", pwd = "root";

        String sql = "SELECT photo FROM photo WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(url, user, pwd);
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, 1);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    try (InputStream in = rs.getBinaryStream("photo")) {
                        ImageUtil.toFile(in, "D:/temp/photo2_restored.png");
                        System.out.println("圖片還原成功");
                    }
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("讀圖失敗", e);
        }
    }
}

Spring Boot / MyBatis 整合最佳實踐

實體類

@Data
public class Photo {
    private Integer id;
    private String name;
    private byte[] photo; // MyBatis 自動映射 BLOB ↔ byte[]
}

Mapper

@Mapper
public interface PhotoMapper {
    @Insert("INSERT INTO photo (name, photo) VALUES (#{name}, #{photo})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Photo photo);

    @Select("SELECT id, name, photo FROM photo WHERE id = #{id}")
    Photo selectById(@Param("id") Integer id);
}

Service (大文件分流建議)

@Service
@Transactional
public class PhotoService {
    @Autowired PhotoMapper mapper;
    @Value("${storage.type:db}") String storageType; // db / oss

    public Photo save(MultipartFile file) {
        if ("oss".equals(storageType)) {
            String url = ossClient.upload(file);
            Photo p = new Photo();
            p.setName(file.getOriginalFilename());
            p.setPhoto(url.getBytes()); // 存 URL 而非二進制
            mapper.insert(p);
            return p;
        }
        // DB 存儲
        Photo p = new Photo();
        p.setName(file.getOriginalFilename());
        p.setPhoto(file.getBytes()); // 注意：大文件會 OOM，建議流式寫入
        mapper.insert(p);
        return p;
    }

    public void export(Integer id, HttpServletResponse resp) {
        Photo p = mapper.selectById(id);
        resp.setContentType("image/png");
        resp.setHeader("Content-Disposition", "inline; filename=\"" + p.getName() + "\"");
        try (OutputStream out = resp.getOutputStream()) {
            out.write(p.getPhoto());
        } catch (IOException e) {
            throw new RuntimeException("導出失敗", e);
        }
    }
}

關鍵避坑指南

架構建議：大文件走對象存儲

┌─────────────┐     小文件 (<1MB)      ┌─────────┐
│  業務服務   │ ─────────────────────→ │ MySQL   │
│             │     BLOB 直存          │  photo  │
└─────────────┘                        └─────────┘
       │
       │ 大文件 (≥1MB)
       ↓
┌─────────────┐     上傳返回 URL        ┌─────────┐
│  業務服務   │ ─────────────────────→ │ OSS/S3  │
│             │     存 URL 到 photo    │  Bucket │
└─────────────┘                        └─────────┘

閾值 1MB 可根據業務調整；混合存儲時 photo 字段存二進制或 URL，name 字段區分類型或新增 storage_type 字段。