mysql blob存图片

基础概念

MySQL中的BLOB（Binary Large Object）是一种数据类型，用于存储大量的二进制数据，如图像、音频、视频等。BLOB类型有四种：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，它们的区别在于能存储的最大数据长度不同。

优势

灵活性：BLOB类型可以存储任意大小的二进制数据，适用于多种类型的数据存储。
集成性：直接在数据库中存储二进制数据，便于数据的管理和检索。
安全性：数据存储在数据库中，可以通过数据库的安全机制进行保护。

类型

TINYBLOB：最大长度为255字节。
BLOB：最大长度为65,535字节（64KB）。
MEDIUMBLOB：最大长度为16,777,215字节（16MB）。
LONGBLOB：最大长度为4,294,967,295字节（4GB）。

应用场景

图像存储：将图片直接存储在数据库中，便于管理和检索。
文件存储：存储各种类型的二进制文件，如PDF、Word文档等。
多媒体数据：存储音频、视频等多媒体数据。

遇到的问题及解决方法

问题1：存储图片时出现乱码

原因：通常是由于字符编码问题导致的。

解决方法：确保数据库和表的字符集设置为utf8mb4，并且连接数据库时也使用相同的字符集。

CREATE TABLE images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    image BLOB
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

问题2：查询图片时速度慢

原因：BLOB类型的数据通常较大，查询时会导致性能问题。

解决方法：

分页查询：避免一次性查询大量数据。
索引优化：对于非BLOB字段建立索引，提高查询效率。
存储优化：将BLOB数据存储在文件系统中，数据库中只存储文件路径。

问题3：存储大文件时超出最大长度限制

原因：选择的BLOB类型最大长度不足以存储大文件。

解决方法：选择合适的BLOB类型，如MEDIUMBLOB或LONGBLOB，确保能存储所需大小的文件。

示例代码

以下是一个简单的示例，展示如何在MySQL中存储和检索图片：

存储图片

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='testdb',
                                         user='root',
                                         password='password')

    cursor = connection.cursor()

    with open('image.jpg', 'rb') as file:
        image_data = file.read()

    sql_insert_blob_query = """ INSERT INTO images
                                  (name, image) VALUES (%s,%s)"""

    insert_blob_tuple = ('image1.jpg', image_data)
    result = cursor.execute(sql_insert_blob_query, insert_blob_tuple)
    connection.commit()
    print("Image and file inserted successfully as a BLOB into MySQL database", result)

except Error as e:
    print("Error while connecting to MySQL", e)

finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

检索图片

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='testdb',
                                         user='root',
                                         password='password')

    cursor = connection.cursor()

    sql_select_blob_query = """SELECT name, image FROM images WHERE id = %s"""

    cursor.execute(sql_select_blob_query, (1,))
    record = cursor.fetchall()

    for row in record:
        print("Image Name: ", row[0])
        with open(row[0], 'wb') as file:
            file.write(row[1])

except Error as e:
    print("Error while connecting to MySQL", e)

finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")