DATALINK Type¶
The DATALINK type is used to store special data types that point to a document (such as satge) or file links. Its main purpose is to store the link address of the document in the database, rather than storing the document itself. This type can be applied to a variety of scenarios, especially when dealing with large-scale document management, providing quick access to documents without actually storing the documents in the database.
Using the DATALINK data type can:
Save storage space: Documents are actually stored in external storage (such as object storage systems), while the database only saves links.
Convenient document access: With storage links, the system can quickly access documents without additional storage and processing.
Improve data operation efficiency: avoids processing large files directly in the database, and improves the speed and efficiency of data operation.
Insert DATALINK type data¶
Grammar Structure
INSERT INTO TABLE_NAME VALUES ('<file://<path>/<filename>>|<stage://<stage_name>/<path>/<file_name>>?<offset=xx>&<size=xxx>')
Parameter definition
Parameters |
Description |
|---|---|
file |
Point to the local file system file location. |
stage |
Point to stage Point to file location. |
offset |
non-required. Offset, indicates the starting point of the read content. |
size |
Non-required. Specifies the size of the read content, in subsections. |
Read DATALINK type data¶
If you want to read the DATALINK data pointing to the file link, you can use the load_file function.
Note
The load_file() function reads files in binary mode. For non-text files (such as binary format files such as images, audio, video, etc.), the read content will be returned in the form of a stream of original bytes and will not be converted into character encoding. In addition, since in UTF-8 encoding, Chinese characters usually take up 3 bytes, while English characters only take up 1 byte. Therefore, when specifying the offset and read size of the file, if the byte alignment of the characters is not considered, Chinese characters may be truncated or cannot be read correctly, resulting in garbled code. To avoid this, it is necessary to correctly convert the values of offset and size according to the character encoding to ensure that the number of bytes of the read content is aligned with the boundaries of the characters.
Example¶
There is a file under /Users/admin/case t1.csv
(base) admin@192 case % cat t1.csv
This is a test message
drop table test01;
create table test01 (col1 int, col2 datalink);
create stage stage01 url='file:///Users/admin/case/';
insert into test01 values (1, 'file:///Users/admin/case/t1.csv');
insert into test01 values (2, 'file:///Users/admin/case/t1.csv?size=2');
insert into test01 values (3, 'file:///Users/admin/case/t1.csv?offset=4');
insert into test01 values (4, 'file:///Users/admin/case/t1.csv?offset=4&size=2');
insert into test01 values (5, 'stage://stage01/t1.csv');
insert into test01 values (6, 'stage://stage01/t1.csv?size=2');
insert into test01 values (7, 'stage://stage01/t1.csv?offset=4');
insert into test01 values (8, 'stage://stage01/t1.csv?offset=4&size=2');
mysql> select * from test01;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| col1 | col2 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | file:///Users/admin/case/t1.csv |
| 2 | file:///Users/admin/case/t1.csv?size=2 |
| 3 | file:///Users/admin/case/t1.csv?offset=4 |
| 4 | file:///Users/admin/case/t1.csv?offset=4&size=2 |
| 5 | stage://stage01/t1.csv |
| 6 | stage://stage01/t1.csv?size=2 |
| 7 | stage://stage01/t1.csv?offset=4 |
| 8 | stage://stage01/t1.csv?offset=4&size=2 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8 rows in set (0.01 sec)
mysql> select col1, load_file(col2) from test01;
+-------------------------------------------+
| col1 | load_file(col2) |
+-------------------------------------------+
| 1 | this is a test message
|
| 2 | th |
| 3 | is a test message
|
| 4 | i |
| 5 | this is a test message
|
| 6 | th |
| 7 | is a test message
|
| 8 | i |
+-------------------------------------------+
8 rows in set (0.01 sec)