Insert image into MYSQL Table using SQL Statement

Question

I need to insert images in MYSQL database table. The images are stored in the local disk. I am using LOAD_FILE() for inserting the images, but it only stores the path not the image.

If it is not possible to insert from local disk means from where can I get the images? Please give me a solution with what are the resources I need to store my images into the database.

Answer 1

I was able to get this done by moving the image(fileName.jpg) file first in to below folder(in my case) C:\ProgramData\MySQL\MySQL Server 5.7\Uploads and then I executed below command and it works for me,

UPDATE tbl_name SET logo=LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/fileName.jpg') where id='qweq12';

Hope this helps.

Answer 2

I'm not sure what your app does, but I strongly advise against storing an image directly to a MYSQL database, in a long run your database will be laggy.

To answer your question, you need to do the following:-

Step 1: Create MySQL Table

Do create a mysql table with a blob type field in it, to save your image.

In our table we have only two fields:

1) image_id of int type

2) image of blob type

Here is the query to create table:

CREATE TABLE pictures ( image_id int(10) NOT NULL auto_increment, image blob, PRIMARY KEY (image_id) );

Step 2: insert image into table

Now we can insert the image into table using the insert into sql. Following is the example of sql:

INSERT INTO pictures VALUES(1, LOAD_FILE('d:\flower.gif'));

We have used the LOAD_FILE() function of MySQL to insert the image data into database.

After inserting the data you can view it using the MySQL tool.

Hope that solves your problem :).

Answer 3

Instead of putting images in database. Just put the relative path in database and actual file on the system.

Take 1 base directory : /root/child/images

Images needs to be stored is image1.jpg, image2.jpg etc.

Just store the "image1.jpg" inside the database.

So while fetching data, get the file name , combine it with base directory path and get it.