Skip to content

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.

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.

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)