w3resource logo

MySQL load file function

MySQL LOAD_FILE() function



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

To use this function, the file must be located on the host server, user must specify the full path name of the file, and user must have the FILE privilege. The file must be readable and size must be less than max_allowed_packet (set in the my.ini file) bytes. It returns NULL if the file does not exist or can’t be read.


LOAD_FILE (file_name)

Where file_name is a file name with path.

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 -


SELECT * INTO OUTFILE 'C:\\aut_backup.sql'
FROM author;


The above MySQL command will create a file aut_backup.sql in the C drive. aut_backup.sql file will contain all the records of author table.

Contents of aut_backup.sql

aut backup

In the next step, we will create a table called temp_aut, whose structure is same as author, with the following command -


create table temp_aut like author;


The above MySQL command will create an empty table temp_aut.

In the next step, we will insert data into the temp_aut table from aut_backup.sql file with the following command -


load data infile 'C:\\aut_backup.sql' into table temp_aut fields terminated by ',' enclosed by '"';

Records of the temp_aut table after inserting data

temp aut

The above data is same as author -


All String Functions

MySQL String Functions, slide presentation

We have compiled a number of MySQL Questions and Answers. Check and assess you MySQL skill by visiting those questions and answers.