How to use LOAD_FILE to load a file into a MySQL blob?

The manual states the following:


Reads the file and returns the file contents as a string. To use this
function, the file must be located on the server host, you must
specify the full path name to the file, and you must have the FILE
privilege. The file must be readable by all and its size less than
max_allowed_packet bytes. If the secure_file_priv system variable is
set to a nonempty directory name, the file to be loaded must be
located in that directory.

If the file does not exist or cannot be read because one of the
preceding conditions is not satisfied, the function returns NULL.

As of MySQL 5.0.19, the character_set_filesystem system variable
controls interpretation of file names that are given as literal

mysql> UPDATE t
            SET blob_col=LOAD_FILE('/tmp/picture')
            WHERE id=1;

From this, I see more than one thing that could be wrong in your case…

  • are you passing the full path?
  • are privileges set correctly?
  • what does the function return? NULL?
  • have you tried it with the query given in the manual?

Leave a Comment