Tuesday 30 July 2019

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.

0 comments:

Post a Comment