MySQL LOAD_FILE() function

LOAD_FILE() function

MySQL LOAD_FILE() reads the file and returns the file contents as a string.

This function is useful in -

• File content retrieval: It allows you to retrieve the contents of a file as a string.
• Data import: LOAD_FILE() can be used to import data from files into a database.
• Data filtering: LIKE operators are commonly used in the WHERE clause to filter data.

Syntax:

LOAD_FILE (file_name)

Where file_name is a file name with path.

Syntax Diagram:

MySQL Version: 8.0

Example: MySQL LOAD_FILE() function

For this example, first we will take a backup of the data of author table using the following command -

Code:

SELECT * LOAD_FILE('/home/username/myfile.txt')

Explanation:

Above MySQL command will read file myfile.txt

MySQL LOAD_FILE fucntion is several conditions to be met to be executed successfully.

• File which you are trying to load must be present in the same host where MySQL server is running. For example, if your MySQL server is installed on example.com, file must be present on example.com only.
• Full path name of the file must be specified. So, if your file is located within a user's home directory, assuming username w3r, you must specify '/home/w3r/somefile.txt'
• User who is executing the command must have FILE privilege. You may grant FILE privilege to a user with following "GRANT FILE on dbname.* TO user@localhost".
• File in question must be readable by all. If you are trying to load a file which is not present on the users home directory hieararchy, make sure you have read permission on the file.
• MySQL Server has a max_allowed_packet variable. File in question must not exceed value specified in that variable. You may check value of max_allowed_packet with 'show variables like '%max_allowed_packet%';'; you must have MySQL root privilege for executing this command. You change the value of max_allowed_packet in your MySQL configuration file. Open your my.ini or my.cnf file, find the line max_allowed_packet=some_value and change the value to your desired one, for example if you want to set the valle to say 50MB, write 50MB.
• MySQL has a secure_file_priv variable. If value of that variable is set to a nonempty directory name, the file to be loaded must be located in that directory. You may find the secure_file_priv variable and its value and may chnage it in your MySQL configuration file.

All String Functions (Slides presentation)

Previous: LIKE
Next: LOCATE