How to use LOAD_FILE() function in MySQL? (code example)

In MySQL, the LOAD_FILE() function reads a file and returns its contents as a string.

Syntax

LOAD_FILE(file_name)

Where file_name is the full path to the file.

The following is an example of me selecting content from a file:

SELECT LOAD_FILE('/data/test.txt') AS Result;

Result:

+------------------------------------------+
| Result                                   |
+------------------------------------------+
| This text is all that the file contains! |
+------------------------------------------+

An example of a database

The following is an example of selecting the content of a file Example of query when inserting into database:

INSERT INTO MyTable (FileId, UserId, MyBlobColumn) 
VALUES (1, 20, LOAD_FILE('/data/test.txt'));

In this example, the column MyBlobColumn has a BLOB data type (allowing it to store binary data).

Now that it is in the database we can select it:

SELECT MyBlobColumn
  FROM MyTable
  WHERE UserId = 20;

Result:

+------------------------------------------+
| MyBlobColumn                             |
+------------------------------------------+
| This text is all that the file contains! |
+------------------------------------------+

If the file does not exist, return NULL:

SELECT LOAD_FILE('/data/oops.txt') AS Result;

Result:

+--------+
| Result |
+--------+
| NULL   |
+--------+

If one of the following conditions is not met, NUll may be returned:

1. The file must be located on the server host.

2. You must have the FILE permission to read the file. Users with this FILE permission can read any file on the server host that is world-readable or readable by the MySQL server.

3. The file must be readable by everyone and its size is less than max_allowed_packet bytes.

You can check like this:

SHOW VARIABLES LIKE 'max_allowed_packet';

Result:

+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+

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.

You can check like this:

SHOW VARIABLES LIKE 'secure_file_priv';

Result:

+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| secure_file_priv | /data/ |
+------------------+--------+

In this example, I can only read files from the /data/ directory.

Related recommendations: "MySQL Tutorial"

This article is about the use of the LOAD_FILE() function in MySQL. I hope it will be helpful to friends in need!

The above is the detailed content of How to use LOAD_FILE() function in MySQL? (code example). For more information, please follow other related articles on the PHP Chinese website!