MySQL8 LOAD_FILE() Functions – String Functions

This function helps in reading a file and returns its contents as a string.

Some most important part of the function

• 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 the server and its size less than max_allowed_packet bytes. In my system the value of ‘max_allowed_packet‘ is 16777216.
  

Lets see the below code to get  the value of variable.

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.08 sec)

• 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. In my system the value of ‘secure_file_priv‘ is /var/lib/mysql-files/ . 

This is the most important part of the function. Otherwise you will waste your time and get the NULL error.

Lets see the below code to get  the value of variable.

mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

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

• The character_set_filesystem system variable controls interpretation of file names that are given as literal strings.

Lets see the below code to get  the value of variable.

mysql> SHOW VARIABLES LIKE 'character_set_filesystem';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_filesystem | binary |
+--------------------------+--------+
1 row in set (0.76 sec)

LOAD_FILE() : Syntax

LOAD_FILE() : Parameter

[table caption=”” width=”100%” colwidth=”15%|15%|15%|55%” colalign=”left|lef|lef|left”]
Name, Required /Optional, Value Type, Description
string , Required, String , The filename.
[/table]

LOAD_FILE() : Output

[table caption=”” width=”100%” colwidth=”20%|80%” colalign=”left|left”]
Returns,
string, returns file contents as a string.
[/table]

LOAD_FILE() : Available from

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 5.7
[/table]

LOAD_FILE() Example 1 : Basic usage.

Lets see the below example, where I select the contents from a file:

mysql> SELECT LOAD_FILE('/var/lib/mysql-files/test.txt') AS Output;
+---------------------+
| Output              |
+---------------------+
this is test file.
+---------------------+
1 row in set (0.09 sec)

LOAD_FILE() Example 2 : A Database Example

Lets see the below example of a query might look like when inserting the contents of the file into a table:

mysql> INSERT INTO tbl_test (id, user_name, cv_text) 
VALUES (1, 'emp', LOAD_FILE('/var/lib/mysql-files/test.txt'));

And now we can fetch the row of a table:

mysql> SELECT cv_text FROM tbl_test WHERE id = 1;
+---------------------+
| cv_text             |
+---------------------+
this is test file.
+---------------------+
1 row in set (0.04 sec)

In this case, the column cv_text has a data type of BLOB (which allows it to store binary data).

LOAD_FILE()  Example 3 : If the File Doesn’t Exist

NULL is returned, if the file doesn’t exist. Lets see the below example.

mysql> SELECT LOAD_FILE('/var/lib/mysql-files/test1.txt') AS Output;
+--------+
| Output |
+--------+
| NULL   |
+--------+
1 row in set (1.41 sec)

See all MySQL String functions MySQL 8 String Functions.

Related articles : BIT_LENGTH(), CHAR() , CHARACTER_LENGTH(), CHAR_LENGTH(), TRIM(), RTRIM(), RIGHT().

PHP Related articles : STRLEN(), SUBSTR_COUNT(), COUNT_CHARS() ,STRCSPN() , STR_GETCSV(), ECHO(),  PHP STRING FUNCTIONS().