Using LOAD_FILE() function in practical demonstration MySQL

This article is mainly about the use of the LOAD_FILE () function in the actual combat demonstration MySQL. If you are interested, let's take a look at this article. I believe that the use of the LOAD_FILE () function in the actual combat demonstration MySQL is of some reference value to everyone.

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

Grammar

LOAD_FILE (file_name)

Where file_name is the full path to the file.

Here is an example of what I selected from a file:

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

Results:

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

An example of a database

The following is an example of a query when the contents of a file are inserted into a 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 that allows it to store binary data.

Now that it's in the database, we can select it:

SELECT MyBlobColumn FROM MyTable WHERE UserId = 20

Results:

+-- + | 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

Results:

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

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

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

two。 You must have the FILE permission to read the file. Users with this FILE permission can read any file on the CVM, which is readable by world-readable or MySQL CVM.

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

You can check like this:

SHOW VARIABLES LIKE 'max_allowed_packet'

Results:

+-+ | 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'

Results:

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

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

The above details about the use of the LOAD_FILE () function in the actual combat demonstration MySQL, are they helpful to you? If you want to know more about it, you can continue to follow our industry information section.