Storing images in MySQL

Question

Please give me the query for inserting images in a MySQL database. I am new to stackoverflow, so please ignore me if my question is not up to mark.

I found [this answer](https://stackoverflow.com/a/6472268/7678788) as the best solution... — Chirag Jain, Jul 14 '18 at 12:00

score 18 · Answer 1 · answered Jun 10 '10 at 13:16

18

If the image is located on your MySQL host, you could use the LOAD_FILE() function to store the image in a BLOB field:

CREATE TABLE MyTable (id INT, image BLOB);

INSERT INTO MyTable (id, image) VALUES(1, LOAD_FILE('/tmp/your_image.png'));

You have to make sure that the image file is readable by MySQL, and that the MySQL user has the FILE privilege. To grant the FILE privilege, log-in as root and execute:

GRANT FILE ON *.* TO 'mysql_user'@'localhost';

answered Jun 10 '10 at 13:16

Daniel Vassallo

326,724
71
495
436

2

i have given path in load_file as(c:\dir\a.jpg) but after performing `select` operation I see NULL(not my img) being stored, why? – Gautam Kumar Jun 10 '10 at 13:55
1

@gautam: It looks like a permissions problem. You have to make sure that the file you are reading can be read by the MySQL service. – Daniel Vassallo Jun 10 '10 at 14:04
1

@gautam: In addition, I think in Windows you need to specify your path like this: `'c:\\dir\\a.jpg'` – Daniel Vassallo Jun 10 '10 at 14:06
It's not working and be null, too... – ChihHao Jun 16 '16 at 06:20

score 9 · Accepted Answer · edited Sep 25 '13 at 01:59

9

Is there a particular reason why you can't store a reference to the image, rather than the actual image?

Images are big—they make databases big. SQL can be painful (as it's not object-oriented) and doesn't have an image type.

You could store them as a BLOB... if you want to write all the coding, encoding, checking, etc.

edited Sep 25 '13 at 01:59

Gabriel Ryan Nahmias

2,075
1
26
39

answered Jun 10 '10 at 13:13

Selenia

286
2
3

3

Particular reason? uhmm, let us think. 1- It's not a web appllication and no web server is installed on the machine (to serve the images to the clients)? 2- Plus no file sharing preferred? 3- An application that has a feature to connect remote database & configuration? I can count lots more... – Roni Tovi Mar 16 '16 at 14:56

score 4 · Answer 3 · edited May 23 '17 at 12:19

4

You can use a BLOB field to do this, but I would generally not recommend that. It's almost always better to simply store the image on the filesystem and store the path to the image in the database.

ETA:

See this question for more discussion

edited May 23 '17 at 12:19

Community

1
1

answered Jun 10 '10 at 13:14

Eric Petroelje

58,601
9
123
175

score 0 · Answer 4 · answered Aug 05 '13 at 14:31

It is always a better idea to store images in external folders and store the refrence in the database.

Here is the step by step guide to upload images in the database. http://www.phpriot.com/articles/images-in-mysql/7

http://forum.codecall.net/topic/40286-tutorial-storing-images-in-mysql-with-php/

Here is the link for step by step guide for retreival of images from the database http://forum.codecall.net/topic/40849-tutorial-storing-images-in-mysql-with-php-part-ii-display-your-images/

Some more information on BLOB http://forums.mysql.com/read.php?20,17671,27914

Storing images in MySQL

4 Answers4

Linked

Related