How to store Image along with data in MySQL database?

8 次查看（过去 30 天）
Dipojjal Chakrabarti
回答： Piyush Kumar 2024-10-25，7:43
I am creating a face recognition system which will recognize faces and return information after recognizing that user like: his name, email address, user ID, password, etc.
I was planning to use MySQL because it will be impemented on the web later.
How do I do so?

请先登录，再回答此问题。

回答（2 个）

Stalin Samuel
Stalin Samuel 2015-4-23
Piyush Kumar
Piyush Kumar 2024-10-25，7:43
Hi,
To store an image along with other data in a MySQL database, follow these steps:
1. Create the Table
CREATE TABLE images (
id INT AUTO_INCREMENT PRIMARY KEY,
image_name VARCHAR(255),
image_data LONGBLOB
);
2. Insert the image: Use the LOAD_FILE function to insert an image into the database. Ensure that the secure_file_priv setting in MySQL allows access to the directory where the image is stored:
INSERT INTO images (image_name, image_data)
VALUES ('100.png', LOAD_FILE('C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\100.png'));
You will see "BLOB" entry in the database in "image_data" column.
3. Extract the image: To retrieve and save the image from the database, you can use the following command. Make sure the output path is permitted by the secure_file_priv setting:
mysql -u root -p -e "SELECT image_data FROM test.images WHERE image_name = '201.png' INTO DUMPFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/extracted.img';"
4. Convert the Extracted file to PNG using powerShell: Convert the binary data back into a PNG file using PowerShell:
$inputFile = "C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\extracted.img"
$outputFile = "C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\extracted.png"
[System.IO.File]::WriteAllBytes($outputFile, [System.IO.File]::ReadAllBytes($inputFile))
5. Important Note: Ensure that the file paths used for storing and retrieving images are within the directories allowed by the secure_file_priv setting in MySQL. This setting restricts file operations to specified directories for security reasons.
This process ensures that the image data is correctly stored and retrieved from the database, and the PowerShell script helps convert the binary data back into a usable image file.

类别

在 Help Center 和 File Exchange 中查找有关 Database Toolbox 的更多信息

标签

产品

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by