MySQL - BLOB

MySQL BLOB：在数据库中存储二进制数据

A BLOB（二进制大对象）是 MySQL 中的一种数据类型，用于存储二进制数据，例如图像、音频文件、PDF 或任何其他类型的文件。这允许你将应用程序的数据及其关联文件统一存储在一个地方。

大问题：你是否应该使用 BLOB？

虽然你可以在数据库中存储文件，但它并非总是最佳的架构选择。理解权衡至关重要。

何时考虑使用 BLOB

事务完整性：当文件及其元数据必须在一个单个原子事务中创建、更新或删除时。
简单性：对于小型应用程序，在数据库中管理所有内容可能比使用独立的文件系统更简单。
安全性：数据库访问控制可用于管理对文件的访问。
小文件：存储非常小的文件，如用户头像或配置图标，可以提高效率。

何时避免使用 BLOB（替代方案：文件路径存储）

性能：数据库针对结构化数据进行了优化，而不是大型二进制流。存储大型文件会显著减慢查询和备份速度。
数据库大小：BLOB 会迅速膨胀数据库，使其变得缓慢、昂贵且难以管理。
应用程序复杂性：你的应用程序代码需要处理二进制数据流的进出数据库，这可能比从文件系统提供文件更复杂。
可扩展性：最常见和可扩展的方法是将文件存储在专用文件系统或云对象存储服务（如 Amazon S3 或 Google Cloud Storage）上，并在数据库中仅存储文件路径或 URL。

最佳实践：对于大多数应用程序，特别是处理几兆字节以上文件的应用程序，在数据库中存储文件的引用（URL 或路径），并将文件本身存储在文件系统或对象存储中。

BLOB 的类型

MySQL 提供了四种 BLOB 类型，区别仅在于它们能存储的最大数据量。

类型	最大大小
TINYBLOB	255 字节
BLOB	65 千字节 (65,535 字节)
MEDIUMBLOB	16 兆字节 (16,777,215 字节)
LONGBLOB	4 千兆字节 (4,294,967,295 字节)

在应用程序中使用 BLOB

最常见的使用 BLOB 的方式是通过应用程序。让我们创建一个表来存储用户头像，并演示如何使用 Node.js 脚本插入和检索它们。

CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    profile_picture MEDIUMBLOB, -- MEDIUMBLOB 对于头像来说是合理的大小
    mime_type VARCHAR(50) -- 存储 MIME 类型以便正确地提供图片服务
);

从 Node.js 插入 BLOB

此脚本将图像文件从磁盘读取到 Buffer 中，并使用预处理语句将其插入数据库。

// 设置：npm install mysql2
// 假设你在同一目录下有一个文件 'avatar.jpg'。
const mysql = require('mysql2/promise');
const fs = require('fs').promises;

async function uploadProfilePicture(username, filePath, mimeType) {
    let connection;
    try {
        connection = await mysql.createConnection({ /* 你的数据库配置 */ });

        // 将文件读取到 Buffer 中
        const imageBuffer = await fs.readFile(filePath);

        const sql = 'INSERT INTO user_profiles (username, profile_picture, mime_type) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE profile_picture = VALUES(profile_picture), mime_type = VALUES(mime_type)';

        const [result] = await connection.execute(sql, [username, imageBuffer, mimeType]);
        console.log(`${username} 的头像已更新。受影响行数：${result.affectedRows}`);

    } catch (error) {
        console.error('上传图片失败：', error);
    } finally {
        if (connection) await connection.end();
    }
}

// 用法
uploadProfilePicture('aisha_khan', './avatar.jpg', 'image/jpeg');

在 Node.js 中检索 BLOB

此脚本检索二进制数据并将其保存到新文件。在 Web 应用程序中，你会将此数据以正确的 Content-Type 头流式传输给客户端。

async function downloadProfilePicture(username, outputFilePath) {
    let connection;
    try {
        connection = await mysql.createConnection({ /* 你的数据库配置 */ });

        const sql = 'SELECT profile_picture, mime_type FROM user_profiles WHERE username = ?';
        const [[user]] = await connection.execute(sql, [username]);

        if (user && user.profile_picture) {
            await fs.writeFile(outputFilePath, user.profile_picture);
            console.log(`${username} 的图片已保存到 ${outputFilePath}。MIME 类型：${user.mime_type}`);
        } else {
            console.log(`未找到用户 ${username} 的图片：`);
        }

    } catch (error) {
        console.error('下载图片失败：', error);
    } finally {
        if (connection) await connection.end();
    }
}

// 用法
downloadProfilePicture('aisha_khan', './retrieved_avatar.jpg');

常见陷阱

max_allowed_packet：MySQL 有一个服务器变量，限制任何单个数据包的大小。如果你的 BLOB 大小超过此值，查询将失败。你可能需要在 MySQL 配置文件（my.cnf 或 my.ini）中增加它。
内存使用：将大型 BLOB 加载到应用程序中会消耗大量内存。请务必注意你正在处理的数据大小。
不必要的数据检索：如果不需要二进制数据，请避免在包含 BLOB 列的表上使用 SELECT *。明确只选择你需要的列，以防止获取大量不必要的数据。