w3resource
MYSQL Tutorial

MySQL LOAD_FILE() function

LOAD_FILE() function

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

Syntax:

LOAD_FILE (file_name)

Where file_name is a file name with path.

Syntax Diagram:

MySQL LOAD_FILE() Function - Syntax Diagram

MySQL Version: 5.6

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 [email protected]".
  • 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

MySQL String Functions, slide presentation

Many of our valued users post comments along with piece of code. Disqus may, false positively, consider those piece of code as Bad or Starnge syntax and send those comments to spam automatically. Even if that happens, we will make sure those useful comments are taken out of spam and approved. It may take a couple of days for that though, but any useful comment will be brought to public view for sure. We regret if that happend to any user. You may write us directly regarding this to - w3resource[at]yahoo[dot]com.