MySQL LOAD_FILE() function

In this context, we will learn how we can use the MySQL LOAD_FILE() function with proper syntax and good examples.

Introduction of MySQL LOAD_FILE() function

The LOAD_FILE() function in MySQL reads a file and gives back its contents as a string.

Syntax of the MySQL LOAD_FILE() function

The syntax of the MySQL LOAD_FILE() function is given as follows:

LOAD_FILE(file_name)

Parameters or arguments used in MySQL LOAD_FILE() function:

There is only one parameter accepted by the LOAD_FILE() function in MySQL, which is given as follows:

file_name- This is the file_name that will be used as the full path to the file.

Returns:

It will return the contents as a string.

Application used for LOAD_FILE() function:

The LOAD_FILE() function can be used in the given below MySQL versions.:

• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0
• MySQL 3.23

Examples of MySQL LOAD_FILE() function:

Now we will look into some MySQL LOAD_FILE() function examples and will explore how we can use the LOAD_FILE function in MySQL.

Example-1

Here's an example where I select the contents from a file:

SELECT LOAD_FILE('/data/test.txt') AS Result;

OUTPUT:

Result

This file contains the texts that we want.

Example 2:

Here's an example of what a query might look like when inserting the contents of the file into a database:

INSERT INTO MyTable (FileId, UserId, MyBlobColumn)
VALUES (1, 20, LOAD_FILE('/data/test.txt'));

In this case, the column MyBlobColumn has a data type of BLOB (which allows it to store binary data).

And now that it's in the database, we can select it:

SELECT MyBlobColumn
FROM MyTable
WHERE UserId = 20;

OUTPUT:

MyBlobColumn

This text is all that the file contains!

Example 3: If the File Doesn't Exist

If the file doesn't exist, NULL is returned:

SELECT LOAD_FILE('/data/oops.txt') AS Result;

OUTPUT:

Result

NULL

Some more Reasons We Might Get NULL:

We'll also get NULL if one of the following conditions isn't met:

• The file must be located on the server host.
• We must have the FILE privilege in order to read the file. A user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server.
• The file must be readable by all, and its size must be less than max_allowed_packet bytes. Here's how we can check that:

SHOW VARIABLES LIKE 'max_allowed_packet';

OUTPUT:

Variable_name	Value
max_allowed_packet	67108864

• If the secure_file_priv system variable is set to a non-empty directory name, then the file to be loaded must be located in that directory. Here's how we can check that:

SHOW VARIABLES LIKE 'secure_file_priv';

OUTPUT:

Variable_name	Value
secure_file_priv	/data/

In this example, I can only read files from the /data/ directory.

Application of MySQL LOAD_FILE() function:

This function is used to read a file and returns its contents as a string.

Summary:

In the above context, we have learned how we can use the LOAD_FILE() function in MySQL reads a file and gives back its contents as a string.