How to Insert Image in MySQL Table using BLOB Data Type | Session #5
How to Insert Image in MySQL Table using BLOB Data Type – Detailed Description
MySQL me image store karne ke liye hum BLOB (Binary Large Object) data type use karte hain. Image ko MySQL table ke andar as a binary data store kiya jata hai. MySQL koi image ko display nahi karta, sirf uska raw data store karta hai. Display ka kaam application (PHP, .NET, Python) karti hai.
1️⃣ Create Table for Image Storage
MySQL me image store karne ke liye pehle ek table banana hota hai jisme image ko BLOB data type me rakha jayega.
CREATE TABLE employee_photo (
id INT AUTO_INCREMENT PRIMARY KEY,
employee_name VARCHAR(100),
photo LONGBLOB
);
Why LONGBLOB?
TINYBLOB → 255 bytes
BLOB → 65 KB
MEDIUMBLOB → 16 MB
LONGBLOB → 4 GB
Image usually बड़ा होता है, isliye LONGBLOB best hai.
2️⃣ Insert Image Using SQL (via File Path)
MySQL direct SQL query ke through image insert nahi kar sakta jab tak load_file() allowed na ho.
Example:
INSERT INTO employee_photo (employee_name, photo)
VALUES ('Naveen', LOAD_FILE('C:/images/naveen.jpg'));
Important: LOAD_FILE() Not Working?
Agar LOAD_FILE null return kare to ye settings check karein:
✔ secure_file_priv
MySQL me ek folder fix hota hai jisme se file load ki ja sakti hai.
Check this:
SHOW VARIABLES LIKE 'secure_file_priv';
Agar koi folder show ho:
C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/
To image ko ** उसी folder में copy करें**, फिर query chalाएँ:
INSERT INTO employee_photo (employee_name, photo)
VALUES ('Naveen', LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/naveen.jpg'));
3️⃣ Insert Image Using Programming Language (Recommended)
Direct SQL se image insert karna mushkil ho sakta hai, isliye log ye 3 languages use karte hain:
✔ PHP
✔ Python
✔ C# / .NET
✔ Java
Example: Insert Image Using Python
import mysql.connector
conn = mysql.connector.connect(
host="localhost",
user="root",
password="",
database="testdb"
)
cursor = conn.cursor()
with open("naveen.jpg", "rb") as file:
binary_data = file.read()
query = "INSERT INTO employee_photo (employee_name, photo) VALUES (%s, %s)"
cursor.execute(query, ("Naveen", binary_data))
conn.commit()
conn.close()
4️⃣ Retrieve Image from MySQL
Retrieve karne ke liye BLOB ko file ke form me save karna padta hai.
query = "SELECT photo FROM employee_photo WHERE id=1"
cursor.execute(query)
image = cursor.fetchone()[0]
with open("output.jpg", "wb") as file:
file.write(image)
5️⃣ Should You Store Images Inside MySQL?
Many developers image MySQL me store nahi karte.
Better approach:
✔ Store image in folder
✔ Store image path/URL in MySQL
Example:
/uploads/naveen.jpg
Why?
Database heavy nahi hota
Backup fast hota
Performance better hoti
Final Summary (Easy Version)
Step Description
1 Table create with LONGBLOB
2 LOAD_FILE se insert kar sakte hain (agar secure_file_priv allowed ho)
3 Best way = Python/PHP/.NET se insert karna
4 Retrieve karke file me save karna padta hai
5 Recommended: Image ko folder me rakho, MySQL me sirf path rakho