LOB_WRITEAPPEND is used to perform append (streaming) writes to LONGTEXT or LONGBLOB columns in an UPDATE statement, reducing memory pressure during large LOB writes. The same LOB can be appended multiple times across one or more transactions; after a transaction commits, the complete content becomes visible to other sessions.
Note
This function is supported starting from V4.6.2.1.
Syntax
UPDATE table_name
SET lob_column = LOB_WRITEAPPEND(lob_column, amount, buffer)
[WHERE condition];
Parameters
Parameter |
Type |
Note |
|---|---|---|
lob_column |
LONGTEXT or LONGBLOB |
The target LOB column to which data is to be appended. It must be the same column as the left side of SET. |
amount |
integer | The length of the data to be appended this time must be greater than 0 and must be exactly the same as the actual length of buffer. For LONGTEXT, it is the character length; for LONGBLOB, it is the byte length. |
buffer |
Strings, binary strings, LONGTEXT or LONGBLOB |
The data to be appended this time. |
The usage limitations and notes are as follows:
- It must be used in
UPDATE ... SET .... - Only one assignment statement is allowed after
SET. - The target LOB column must be of the
LONGTEXTorLONGBLOBtype. - Tables with
MERGE_ENGINEset todelete_insertare not supported. - The maximum length for a single append operation is approximately 512 MB. Strictly speaking, since the data header itself occupies some space, the actual limit is
512 MB - 1 Byte - 48 Byte, which equals536,870,863bytes. - The maximum cumulative appended data length is approximately 4 TB. Strictly speaking, the actual limit is
4 TB - 1 Byte, which is4,398,046,511,103bytes. - Only one target row can be added for writing.
- The first parameter (
lob_column) ofLOB_WRITEAPPENDmust be the same as the LOB column on the left side of theSETstatement. - Cannot be nested within other functions.
- The target LOB column cannot be a dependent column, including for indexes,
GENERATEDcolumns, triggers, orCHECKconstraints. - Cannot be used on views with the
WITH CHECK OPTIONclause. LOB_WRITEAPPENDis not idempotent: executing the same statement once and multiple times yields different results. Repeated execution will append data repeatedly.- Retry mechanism: When the server explicitly returns a failure, the append operation is considered ineffective. If the client experiences a timeout, disconnection, or lost response, the outcome is uncertain. Do not directly retry the same statement, as this may result in duplicate appends. During a retry, you can first wait or read the final response of the original statement before deciding on the next action.
Examples
This example shows how to use LOB_WRITEAPPEND to append data in multiple chunks to a LONGTEXT column and use the SUBSTR function to read the data in segments.
CREATE TABLE stream_store (
id INT PRIMARY KEY,
data LONGTEXT
);
START TRANSACTION;
INSERT INTO stream_store VALUES (1, '');
UPDATE stream_store
SET data = LOB_WRITEAPPEND(data, 3, 'ABC')
WHERE id = 1;
UPDATE stream_store
SET data = LOB_WRITEAPPEND(data, 3, 'DEF')
WHERE id = 1;
UPDATE stream_store
SET data = LOB_WRITEAPPEND(data, 3, 'GHI')
WHERE id = 1;
COMMIT;
-- After all the operations above are successfully performed and committed, it is equivalent to:
-- INSERT INTO stream_store VALUES (1, 'ABCDEFGHI');
SELECT data,
CHAR_LENGTH(data) AS char_len
FROM stream_store
WHERE id = 1;
The return result is as follows:
+-----------+----------+
| data | char_len |
+-----------+----------+
| ABCDEFGHI | 9 |
+-----------+----------+
1 row in set
Use the SUBSTR function to read data in segments:
SELECT SUBSTR(data, 1, 3) AS first_part,
SUBSTR(data, 4, 3) AS second_part,
SUBSTR(data, 7, 3) AS third_part
FROM stream_store
WHERE id = 1;
The return result is as follows:
+------------+-------------+------------+
| first_part | second_part | third_part |
+------------+-------------+------------+
| ABC | DEF | GHI |
+------------+-------------+------------+
1 row in set