MySQL - LOAD_FILE() Function

---

---

The MySQL LOAD_FILE() function accepts a string value representing the path of a file and reads its contents and returns them. The file path should be an absolute path, and the user running the query should have the necessary file system permissions to read the file. If we provide an image, it will return the output as blob.

Syntax

Following is the syntax of MySQL LOAD_FILE function −

LOAD_FILE(file_name)

Parameters

This function takes a file path as a parameter.

Return Value

This function returns the file content as a string.

Example

In the following example, we are using the LOAD_FILE() function to read the contents of a text file 'test.txt' from an absolute file path and return it as a hexadecimal string −

SELECT LOAD_FILE('MySQL_data_directory/test.txt') as Result;

Following is the output of the above code −

Result
0x5468697320697320612066696C6520776974682073616D706C652064617461

If you disable the --binary-as-hex setting, the result of the above query will return the actual text content of the file −

SELECT LOAD_FILE('MySQL_data_directory/test.txt') as Result;

The output obtained is as follows −

Result
This is a file with sample data

Example

If the file path passed to the LOAD_FILE() function does not exist, it returns NULL −

SELECT LOAD_FILE('UnknownPath/java.jpg');

We get the output as follows −

LOAD_FILE('UnknownPath/java.jpg')
NULL

Example

You can also pass a column name of a table as a parameter of this function and load the contents of a file as values of the specified column.

Assume we have created a table "Tutorials_table" using the CREATE statement and inserted records into it except the "Contents" column −

CREATE TABLE Tutorials_table (
   ID INT, 
   Title VARCHAR(20), 
   Contents LONGTEXT
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO Tutorials_table (ID, Title) VALUES 
(1, 'Java'),
(2, 'JavaFX'),
(3, 'Coffee Script'),
(4, 'OpenCV');

Suppose we have 4 text files in the MySQL data directory i.e. C:\ProgramData\MySQL\MySQL Server 8.0\Uploads −

java.txt:

Java is a high-level programming language originally developed by Sun Microsystems and released in 1995. Java runs on a variety of platforms, such as Windows, Mac OS, and the various versions of UNIX.

Javafx.txt:

JavaFX is a Java library used to build Rich Internet Applications. The applications written using this library can run consistently across multiple platforms. The applications developed using JavaFX can run on various devices such as Desktop Computers, Mobile Phones, TVs, Tablets, etc.

coffee.txt:

CoffeeScript is a light weight language which transcompiles into JavaScript. It provides better syntax avoiding the quirky parts of JavaScript, still retaining the flexibility and beauty of the language.

opencv.txt:

OpenCV is a cross-platform library using which we can develop real-time computer vision applications. It mainly focuses on image processing, video capture and analysis including features like face detection and object detection. 

Now, let's load the contents of above text file as values for the "Contents" column in the Tutorials_table −

UPDATE Tutorials_table SET Contents = LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/java.txt') WHERE ID=1;
UPDATE Tutorials_table SET Contents = LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/javafx.txt') WHERE ID=2;
UPDATE Tutorials_table SET Contents = LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/coffee.txt') WHERE ID=3;
UPDATE Tutorials_table SET Contents = LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/opencv.txt') WHERE ID=4;

You can verify the contents of the table using the following SELECT query −

SELECT * FROM Tutorials_table;

You can observe the inserted data as shown in the table obtained below −

ID	Title	Contents
1	Java	Java is a high-level programming language originally developed by Sun Microsystems and released in 1995. Java runs on a variety of platforms, such as Windows, Mac OS, and the various versions of UNIX.
2	JavaFX	JavaFX is a Java library used to build Rich Internet Applications. The applications written using this library can run consistently across multiple platforms. The applications developed using JavaFX can run on various devices such as Desktop Computers, Mobile Phones, TVs, Tablets, etc.
3	Coffee Script	CoffeeScript is a light weight language which transcompiles into JavaScript. It provides better syntax avoiding the quirky parts of JavaScript, still retaining the flexibility and beauty of the language.
4	OpenCV	OpenCV is a cross-platform library using which we can develop real-time computer vision applications. It mainly focuses on image processing, video capture and analysis including features like face detection and object detection.