How the LOAD_FILE() function works in Mariadb?

The LOAD_FILE() function is a string function that reads the content of a file and returns it as a string. It can be used to load the content of a text file, a binary file, or an image file into a string variable or a column. The LOAD_FILE() function is a native function in Mariadb and does not require any external libraries or plugins.

Syntax

The syntax of the LOAD_FILE() function is as follows:

LOAD_FILE(file_name)

The file_name is the name of the file whose content is to be loaded. It can be any valid file name expression in Mariadb, such as column values, literal values, or variables. The file_name must be an absolute path to the file, such as '/home/user/data.txt'. Relative paths, such as 'data.txt', are not supported.

The LOAD_FILE() function returns a string value that contains the content of the file. If the file does not exist, is not readable, is empty, or is larger than the max_allowed_packet system variable, the function returns NULL.

Examples

In this section, we will show some examples of how to use the LOAD_FILE() function in Mariadb.

Example 1: Loading the content of a text file

The following example shows how to use the LOAD_FILE() function to load the content of a text file into a string variable. Suppose we have a text file called hello.txt that contains the following text:

Hello, world!
This is a text file.

The file is located in the /home/user directory. We can use the LOAD_FILE() function to load the content of the file into a string variable, as shown below:

SET @text = LOAD_FILE('/home/user/hello.txt');
SELECT @text;

The output is:

@text
---------------------
Hello, world!
This is a text file.

As you can see, the LOAD_FILE() function returns the content of the text file as a string value.

Example 2: Loading the content of a binary file

The following example shows how to use the LOAD_FILE() function to load the content of a binary file into a string variable. Suppose we have a binary file called data.bin that contains some binary data. The file is located in the /home/user directory. We can use the LOAD_FILE() function to load the content of the file into a string variable, as shown below:

SET @binary = LOAD_FILE('/home/user/data.bin');
SELECT HEX(@binary);

The output is:

HEX(@binary)
------------
48656C6C6F2C20776F726C64210A5468697320697320612062696E6172792066696C652E

As you can see, the LOAD_FILE() function returns the content of the binary file as a string value. To display the string value in a hexadecimal format, we use the HEX() function.

Example 3: Loading the content of an image file

The following example shows how to use the LOAD_FILE() function to load the content of an image file into a string variable. Suppose we have an image file called logo.png that contains the logo of Mariadb. The file is located in the /home/user directory. We can use the LOAD_FILE() function to load the content of the file into a string variable, as shown below:

SET @image = LOAD_FILE('/home/user/logo.png');
SELECT LENGTH(@image);

The output is:

LENGTH(@image)
--------------
12345

As you can see, the LOAD_FILE() function returns the content of the image file as a string value. To display the length of the string value in bytes, we use the LENGTH() function.

Conclusion

In this article, we have learned how the LOAD_FILE() function works in Mariadb. We have seen the syntax of the function, and some examples of how to use it with different types of files. We have also learned about some related functions that can be used with the LOAD_FILE() function. The LOAD_FILE() function is a useful function that can help us load the content of a file into a string value in Mariadb.