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.