MySQL LOAD_FILE() String Function

MySQL LOAD_FILE() String Function

The MySQL LOAD_FILE() string function reads the content of a file and returns it as a string. This function is essential for importing data from files into SQL queries.

Syntax

SELECT LOAD_FILE(file_path) AS result;

The LOAD_FILE() function has the following components:

  • file_path: The path to the file to be loaded. This must be an absolute path.
  • result: An alias for the resulting string content of the file.

Example MySQL LOAD_FILE() String Function

Let's look at some examples of the MySQL LOAD_FILE() string function:

Step 1: Setting Up the File

Ensure the file you want to load is accessible by the MySQL server and located in a directory where the server has read permissions. For example, create a text file named example.txt with the following content:

Hello, this is a sample file content.

Save the file in the directory /tmp/.

Step 2: Using the Database

Set the context to the desired database:

USE mydatabase;

This query sets the context to the database named mydatabase.

Step 3: Using LOAD_FILE()

Use the LOAD_FILE() function to read the content of the file:

SELECT LOAD_FILE('/tmp/example.txt') AS file_content;

This query reads the content of the file example.txt located at /tmp/ and returns it as a string.

Step 4: Using LOAD_FILE() with WHERE Clause

You can use the LOAD_FILE() function in conjunction with other SQL statements. For example, to load a file conditionally:

SELECT id, LOAD_FILE('/tmp/example.txt') AS file_content
FROM sample_table
WHERE id = 1;

This query reads the content of the file example.txt and returns it as a string for rows where the id is 1.

Step 5: Handling Errors

Ensure proper error handling when using LOAD_FILE(). For example, you can check if the file content was successfully loaded:

SELECT IFNULL(LOAD_FILE('/tmp/nonexistent.txt'), 'File not found') AS file_content;

This query attempts to read the content of a non-existent file and returns 'File not found' if the file cannot be read.

Conclusion

The MySQL LOAD_FILE() function is a powerful tool for importing data from files into SQL queries. Understanding how to use the LOAD_FILE() function is essential for effective data management and manipulation in MySQL.