如何往mysql中添加图片

在MySQL中存储图片通常有两种方式：一种是将图片转换为二进制数据（BLOB类型），直接存储在数据库中；另一种是将图片存储在文件系统中，然后在数据库中存储图片的路径。

一、将图片转换为二进制数据存储

步骤：

创建表：

CREATE TABLE images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    image BLOB
);

插入图片：

首先，你需要读取图片文件并将其转换为二进制数据。以下是一个使用Python的示例：

import mysql.connector
from mysql.connector import Error

def insert_image(name, file_path):
    try:
        connection = mysql.connector.connect(host='localhost',
                                             database='your_database',
                                             user='your_username',
                                             password='your_password')

        cursor = connection.cursor()

        with open(file_path, 'rb') as file:
            binary_data = file.read()

        insert_query = "INSERT INTO images (name, image) VALUES (%s, %s)"
        cursor.execute(insert_query, (name, binary_data))
        connection.commit()
        print("Image inserted successfully")

    except Error as e:
        print("Error while inserting image", e)

    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

# 使用示例
insert_image('example_image', '/path/to/your/image.jpg')

从数据库中检索图片：

同样，你可以使用Python来检索并显示图片：

def retrieve_image(image_id):
    try:
        connection = mysql.connector.connect(host='localhost',
                                             database='your_database',
                                             user='your_username',
                                             password='your_password')

        cursor = connection.cursor()

        select_query = "SELECT name, image FROM images WHERE id = %s"
        cursor.execute(select_query, (image_id,))
        record = cursor.fetchone()

        if record:
            name, image = record
            with open(f'{name}.jpg', 'wb') as file:
                file.write(image)
            print("Image retrieved successfully")
        else:
            print("No image found with the given ID")

    except Error as e:
        print("Error while retrieving image", e)

    finally:
        if connection.is.makedirs():
            cursor.close()
            connection.close()

# 使用示例
retrieve_image(1)

二、将图片路径存储在数据库中

步骤：

创建表：

CREATE TABLE images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    path VARCHAR(255)
);

插入图片路径：

你可以直接将图片上传到服务器，并将图片的路径存储在数据库中。以下是一个使用Python的示例：

import mysql.connector
from mysql.connector import Error
import os

def insert_image_path(name, file_path):
    try:
        connection = mysql.connector.connect(host='localhost',
                                             database='your_database',
                                             user='your_username',
                                             password='your_password')

        cursor = connection.cursor()

        # 确保文件存在
        if os.path.exists(file_path):
            # 获取文件的相对路径或绝对路径
            file_path = os.path.abspath(file_path)

            insert_query = "INSERT INTO images (name, path) VALUES (%s, %s)"
            cursor.execute(insert_query, (name, file_path))
            connection.commit()
            print("Image path inserted successfully")
        else:
            print("File does not exist")

    except Error as e:
        print("Error while inserting image path", e)

    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

# 使用示例
insert_image_path('example_image', '/path/to/your/image.jpg')

从数据库中检索图片路径：

你可以使用Python来检索图片路径，并显示图片：

def retrieve_image_path(image_id):
    try:
        connection = mysql.connector.connect(host='localhost',
                                             database='your_database',
                                             user='your_name',
                                             password='your_password')

        cursor = connection.cursor()

        select_query = "SELECT name, path FROM images WHERE id = %s"
        cursor.execute(select_query, (image_id,))
        record = cursor.fetchone()

        if record:
            name, path = record
            print(f"Image path: {path}")
            # 你可以在这里添加代码来显示图片，例如使用PIL库
        else:
            print("No image found with the given ID")

    except Error as e:
        print("Error while retrieving image path", e)

    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

# 使用示例
retrieve_image_path(1)