[mysql] How to insert image in mysql database(table)?

I want to insert image into a table like

                       ,IMAGE BLOB);

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

This question is related to mysql database blob mysql-loadfile

The answer is

If I use the following query,


Error: no such function: LOAD_FILE

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.

You can try something like this..

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

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




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.

You should use LOAD_FILE like so:


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

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

Step 3:then click apply buttonenter image description here

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 enter image description here

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');

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to blob

Angular: How to download a file from HttpClient? How do we download a blob url video How to convert Blob to File in JavaScript Saving binary data as file using JavaScript from a browser Inserting Image Into BLOB Oracle 10g How to Display blob (.pdf) in an AngularJS app Difference between CLOB and BLOB from DB2 and Oracle Perspective? JavaScript blob filename without link How to convert Blob to String and String to Blob in java How to go from Blob to ArrayBuffer

Examples related to mysql-loadfile

Using PHP to upload file and add the path to MySQL database How to insert image in mysql database(table)? MYSQL import data from csv using LOAD DATA INFILE MySQL: Enable LOAD DATA LOCAL INFILE How to import XML file into MySQL database table using XML_LOAD(); function How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE