How to store Image along with data in MySQL database?

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?

Answer 1

[Stalin Samuel]

Connect to database

Answer 2

[Piyush Kumar]

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.