LOAD_FILE()

Function Description

The LOAD_FILE() function is used to read the contents of the file pointed to by the datalink type.

Note

When load_file() is used to load large files, if the file data is too large, it may exceed the system memory limit, resulting in memory overflow. It is recommended to use it in combination with DATALINK.

Function Syntax

>LOAD_FILE(datalink_type_data);

Parameter definition

Parameters	Description
datalink_type_data	datalink type data, you can use the cast() function to convert

Example

There is a file under /Users/admin/case t1.csv

(base) admin@192 case % cat t1.csv
This is a test message

create table t1 (col1 int, col2 datalink);
create stage stage1 url='file:///Users/admin/case/';
insert into t1 values ​​(1, 'file:///Users/admin/case/t1.csv');
insert into t1 values ​​(2, 'stage://stage1//t1.csv');

mysql> select * from t1;
+-----------------------------------------------------------+
| col1 | col2 |
+-----------------------------------------------------------+
| 1 | file:///Users/admin/case/t1.csv |
| 2 | stage://stage1//t1.csv |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select col1, load_file(col2) from t1;
+-------------------------------------------+
| col1 | load_file(col2) |
+-------------------------------------------+
| 1 | this is a test message
 |
| 2 | this is a test message
 |
+-------------------------------------------+
2 rows in set (0.01 sec)


mysql> select load_file(cast('file:///Users/admin/case/t1.csv' as datalink));
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| load_file(cast(file:///Users/admin/case/t1.csv as datalink)) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| this is a test message
                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> select load_file(cast('stage://stage1//t1.csv' as datalink));
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| load_file(cast(stage://stage1//t1.csv as datalink)) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| this is a test message
                             |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)