1

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.

tk_
  • 16,415
  • 8
  • 80
  • 90
chandru rajendran
  • 11
  • 1
  • 1
  • 2

3 Answers3

1

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 :).

Godlove Damian
  • 137
  • 1
  • 4
  • 1
    Laggy? It depends entirely on the size of the file – Strawberry Jul 20 '18 at 05:26
  • Exactly, if you intend to store a few images won't be a problem, if you reach that point you have around 500MB of images in your database is when your users will be experiencing the slowness on loading, but that depends entirely on how you have implemented the rest of your app. Perhaps storing the image reference in the database as a string would be a much better alternative. – Godlove Damian Jul 21 '18 at 17:23
  • 1
    It's less about the size of the database as a whole, and more about the size of the individual files. If they're typically below 100k, say, then storing them in the db seems to work well. – Strawberry Jul 21 '18 at 17:53
0

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.

Tarun Khurana
  • 887
  • 8
  • 9
0

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.

tk_
  • 16,415
  • 8
  • 80
  • 90