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.

Russellrusset

Well, you can put it into a database (with modern versions of mySql), but it's better to upload the file to either your server, or someone elses (like imgur) and store the URL in the database. – Mosul

What have you tried so far? Where are you stuck? Do you have any specific question about this problem? – Bicentennial

Answer 1

Please try below code

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

Maddi

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?? – Radiotherapy

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

Answer 2

You should use LOAD_FILE like so:

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

Majoriemajority

Answer 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

Makeup

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

Answer 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

Marjoriemarjory

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 – Bicentennial

Answer 5

I have three answers to this question:

1. It is against user experience UX best practice to use BLOB and CLOB data types in string and retrieving binary data from an SQL database thus it is advised that you use the technique that involves storing the URL for the image( or any Binary file in the database). This URL will help the user application to retrieve and use this binary file.

2. Second the BLOB and CLOB data types are only available to a number of SQL versions thus functions such as LOAD_FILE or the datatypes themselves could miss in some versions.

3. Third DON'T USE BLOB OR CLOB. Store the URL; let the user application access the binary file from a folder in the project directory.

Gristle

Answer 6

I tried all above solution and fail, it just added a null file to the DB.

However, I was able to get it 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,

INSERT INTO xx_BLOB(ID,IMAGE) VALUES(1,LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/fileName.jpg'));

Hope this helps.

Tindal

Answer 7

The accepted answer is correct, but I came across so many cases where LOAD_FILE() returns NULL (like discussed in 24459149) that I normally have been better off, creating an import file with a hex representation:

docker run -d -p 3306:3306 -e MARIADB_ROOT_PASSWORD=test -e MARIADB_USER=test -e MARIADB_PASSWORD=test -e MARIADB_DATABASE=testdb mariadb:latest
echo "CREATE TABLE XX_SAMPLE(ID INT, IMAGE LONGBLOB)" |mysql -h 127.0.0.1 -u test --password=test testdb

echo -n "INSERT INTO XX_SAMPLE(ID,IMAGE) VALUES(1, 0x" >img_import.sql
cat /some/path/image.png |perl -ne "print unpack 'H*'" >> img_import.sql
echo ");" >> img_import.sql

mysql -h 127.0.0.1 -u test --password=test testdb <img_import.sql

Also Perl's unpack has been easier to use, compared to xxd or hexdump that are not always installed and need special flags to get that long string of hex values without breaks, that the mysql client expects.

UPDATE:

• Changed the quotes as @Terai proposed. (Seems to be a quoting issue that doesn't occur in my ZSH.)
• Added other DDL for the example table, as the original DDL above uses a BLOB. With normal size images you will need MEDIUMBLOB or even LONGBLOB.
• Added a sample docker container to complete the example.

Venavenable

the second line produce an error : syntax error at -e line 1, at EOF Execution of -e aborted due to compilation errors. – Haversine

You need to change 'print unpack "H*"' by "print unpack 'H*'" – Haversine

Answer 8

This is on mysql workbench -- give the image file path:

INSERT INTO XX_SAMPLE(id,image) VALUES(3,'/home/ganesan-pc/Documents/aios_database/confe.jpg');

Makeup

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 – Bicentennial