In MariaDB, the LOAD_FILE()
function reads a file and returns the file contents as a string.
Syntax
The syntax goes like this:
LOAD_FILE(file_name)
Where file_name
is the full path name to the file.
Example
Suppose we have a file called pets.csv
that contains the following text:
1,"Wag","Dog" 2,"Bark","Dog" 3,"Meow","Cat"
We can use the LOAD_FILE()
function to read and return those contents:
SELECT LOAD_FILE('/Users/barney/data/pets.csv');
Result:
+---------------------------------------------+ | LOAD_FILE('/Users/barney/data/pets.csv') | +---------------------------------------------+ | 1,"Wag","Dog" 2,"Bark","Dog" 3,"Meow","Cat" | +---------------------------------------------+ 1 row in set (0.186 sec)
In this case, the full path to the file is /Users/barney/data/pets.csv
.
Wrong File Path or Privileges
Providing a file path that doesn’t satisfy the following criteria results in null
:
- 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 it must be less than the size, in bytes, of the
max_allowed_packet
system variable. - If the
secure_file_priv
system variable is set to a non-empty directory name, the file to be loaded must be located in that directory.
Here’s how to check those two system variables:
SELECT
@@GLOBAL.secure_file_priv,
@@GLOBAL.max_allowed_packet;
Result:
+---------------------------+-----------------------------+ | @@GLOBAL.secure_file_priv | @@GLOBAL.max_allowed_packet | +---------------------------+-----------------------------+ | NULL | 16777216 | +---------------------------+-----------------------------+
In my case, I don’t have any directories specified in the secure_file_priv
system variable, and my max_allowed_packet
variable is set at its default (which is more than enough for the file I uploaded in the previous example).
However, here’s what happens when I try to upload a file that doesn’t exist:
SELECT LOAD_FILE('pets.csv');
Result:
+-----------------------+ | LOAD_FILE('pets.csv') | +-----------------------+ | NULL | +-----------------------+ 1 row in set (0.000 sec)
In this case, I forgot to specify the full path to the file.
Multi-Byte File Names
The character_set_filesystem
system variable is used to control interpretation of file names that are given as literal strings. It’s used for converting file names specified as a string literal from character_set_client
to character_set_filesystem
before opening the file.
By default, character_set_filesystem
is set to binary
, so no conversion takes place.
You can check this value using the following code:
SELECT @@GLOBAL.character_set_filesystem;
Result:
+-----------------------------------+ | @@GLOBAL.character_set_filesystem | +-----------------------------------+ | binary | +-----------------------------------+
Null File Path
Providing a null
file path results in null
:
SELECT LOAD_FILE(null);
Result:
+-----------------+ | LOAD_FILE(null) | +-----------------+ | NULL | +-----------------+ 1 row in set (0.000 sec)
Missing File Path
Failing to provide a file path results in an error:
SELECT LOAD_FILE();
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'LOAD_FILE'