How to insert image in mysql database(table)?

Question

I want to insert image into a table like

 CREATE TABLE XX_SAMPLE(ID INT
                       ,IMAGE BLOB);

So can you help out form how to insert image into the above table.

Solution 1

Please try below code

INSERT INTO xx_BLOB(ID,IMAGE) VALUES(1,LOAD_FILE('E:/Images/jack.jpg'));

Solution 2

You should use LOAD_FILE like so:

LOAD_FILE('/some/path/image.png')

Solution 3

Step 1: open your mysql workbench application select table. choose image cell right click select "Open value in Editor"

Step 2: click on the load button and choose image file

Step 3:then click apply button

Step 4: Then apply the query to save the image .Don't forgot image data type is "BLOB". Step 5: You can can check uploaded image

Solution 4

You can try something like this..

CREATE TABLE 'sample'.'picture' ( 
'idpicture' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
'caption' VARCHAR(45) NOT NULL, 
'img' LONGBLOB NOT NULL, 
 PRIMARY KEY('idpicture')) TYPE = InnoDB;

or refer to the following links for tutorials and sample, that might help you.

http://forums.mysql.com/read.php?20,17671,27914

http://www.hockinson.com/programmer-web-designer-denver-co-usa.php?s=47

View more solutions

Comments

[Samuel Owino]

Dont use loadFile, add the value as a VARCHAR data type, once retrieved in the application layer the application code can be used to fetch the image referenced by this URL.

[Amirhosein Al]

What if the query is being invoked from the server?? Let's say we have an Express server app and my image file is on my local machine. How is that done??

[j_allen_morris]

If the image is on your local machine, you'd need to upload it to the server first.

[Nico Haase]

Can you explain that further? According to the table definition, the second value should not be a string containing the file path, but the content of that file - that's why other answers already contain this

[Nico Haase]

Can you explain that further? How does using another table definition solve the given problem? Additionally, don't refer to external pages, put everything needed into the answer itself. Two of these links are dead

[DanimalReks]

This worked well for me. I had issues with png and jpg. However, gif worked fine.