How the LOAD_FILE() Function Works in MySQL

In MySQL, the LOAD_FILE() function reads a file and returns its contents as a string.

Syntax

The syntax goes like this:

LOAD_FILE(file_name)

Where file_name is the full path to the file.

Example

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

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

Result:

+------------------------------------------+
| Result                                   |
+------------------------------------------+
| This text is all that the file contains! |
+------------------------------------------+

A Database Example

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;

Result:

+------------------------------------------+
| MyBlobColumn                             |
+------------------------------------------+
| This text is all that the file contains! |
+------------------------------------------+

If the File Doesn’t Exist

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

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

Result:

+--------+
| Result |
+--------+
| NULL   |
+--------+

More Reasons You Might Get NULL

You’ll also get NULL one of the following conditions aren’t met:

• The file must be located on the server host.
• You must have the FILE privilege in order to read the file. A user who has the FILEprivilege 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 less than max_allowed_packet bytes. Here’s how you can check that:

  SHOW VARIABLES LIKE 'max_allowed_packet';

  My result:

  +--------------------+----------+
  | Variable_name      | Value    |
  +--------------------+----------+
  | max_allowed_packet | 67108864 |
  +--------------------+----------+

• If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.Here’s how you can check that:

  SHOW VARIABLES LIKE 'secure_file_priv';

  My result:

  +------------------+--------+
  | Variable_name    | Value  |
  +------------------+--------+
  | secure_file_priv | /data/ |
  +------------------+--------+

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