w3resource logo


MySQL load file function

MySQL LOAD_FILE() function

rating MySQL load_file() function has average rating 8 out of 10. Total 13 users rated.

<<PreviousNext>>

Description

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.

Syntax

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 -

Code

SELECT * INTO OUTFILE 'C:\\aut_backup.sql'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
FROM author;

Explanation

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 -

Code

create table temp_aut like author;

Explanation

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 -

Code

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 -

author



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

<<PreviousNext>>