MySQL in a Nutshell, 2nd Edition

Name

LOAD_FILE()

Synopsis

LOAD_FILE(filename)

This function reads the contents of a file and returns it as a string that may be used in MySQL statements and functions. The user must have FILE privileges in MySQL, and the file must be readable by all users on the filesystem. It returns NULL if the file doesn’t exist, if the user doesn’t have proper permissions, or if the file is otherwise unreadable. The file size in bytes must be less than the amount specified in the system variable max_allowed_packet. Starting with version 5.1.6 of MySQL, the system variable character_set_filesystem is used to provide filenames in the character set recognized by the underlying filesystem. Here is an example:

UPDATE applications
SET essay = LOAD_FILE('/tmp/applicant_7382.txt'),
student_photo = LOAD_FILE('/tmp/applicant_7382.jpeg')
WHERE applicant_id = '7382';

In this example, an essay written by someone who is applying for admission to the college is loaded into the essay column (which is a TEXT data type) of the row for the applicant in the applications table. The entire contents of the file, including any binary data (e.g., hard returns and tabs), are loaded from the file into the table. Additionally, an image file containing the student’s photograph is loaded into another column of the same table, but in a BLOB column.