How to insert images in blob in mysql table using only sql syntax (without PHP)?
Solution 1
I think that command is a MSSQL syntax. Try this command:
INSERT INTO `abc`
(`img`)
VALUES
(LOAD_FILE('C:/Users/adity/Desktop/New folder/a.png'))
This command stores image as a BLOB
Solution 2
Through Mysql workbench, its very easy to load images into database using the following steps.
- Right click on the value of the (blob)column in the table and select "Load value from file".
- Then we can provide the image path in the system.
- Then it will converted into byte array and stored it automatically.
- finally save the changes of the table.
xxx
Updated on December 13, 2020Comments
-
xxx over 3 years
- Hi,I am new to SQL and I wanted to store images in the database.I already created a column with blob data type and tried to execute the following statement as given here
INSERT INTO `abc` (`img`) SELECT BulkColumn FROM OPENROWSET( Bulk 'C:\Users\adity\Desktop\New folder\a.png', SINGLE_BLOB) AS BLOB
which gives error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( Bulk
C:\Users\name\Desktop\New folder\a.png
, SINGLE_BLOB) AS BLOB' at line 4I also tried following code as given here
insert into table `abc`(`img`) values('C:\Users\name\Desktop\New folder\an.jpg') where id=1;
which gives the error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table
abc
(img
) values('C:\Users\adity\Desktop\New folder\an.jpg') where id=1' at line 1So please suggest me how to store images in a blob without using php,etc and simply using simple sql insert statement.I am using wamp server for my database.
- I know that I should use file system for images instead of using database.But what does a file system actually mean.Does it mean a file or image hosting site whose address will be stored in database.
-
xxx over 8 yearsThe statement executed successfully and now table looks something like this.Is it correct and what format is it?
-
aceraven777 over 8 yearsYes that's the correct format of the BLOB. Are you expecting an image will show up there? That's not how it works. You can use this BLOB to display the image. You can refer here how to display BLOB using PHP. stackoverflow.com/questions/20556773/…
-
RiggsFolly over 4 years
WHERE id=1
?? -
tk_ over 4 yearsIm was answering the question. :D
-
RiggsFolly over 4 yearsAn INSERT query does NOT have a
WHERE
clause