BLOB and TEXT Data Types
Overview of large object types. This page compares BLOB (binary) and TEXT (character) types, explaining their storage and usage differences.
Last updated
Was this helpful?
Overview of large object types. This page compares BLOB (binary) and TEXT (character) types, explaining their storage and usage differences.
A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are
BLOB,
MEDIUMBLOB, and
These differ only in the maximum length of the values they can hold.
The TEXT types are
TEXT,
MEDIUMTEXT, and
JSON (alias for LONGTEXT)
These correspond to the four BLOB types and have the same maximum lengths and storage requirements.
BLOB and TEXT columns can have a DEFAULT value.
It is possible to set a unique index on columns that use the BLOB or TEXT data types.
When working with very large BLOB or TEXT values, the data may exceed the limit set by the max_allowed_packet system variable. To avoid this—and to reduce memory consumption on the client—most MariaDB connectors allow you to "stream" data in chunks.
Connector/C: Use mysql_stmt_send_long_data() to send parameter data in pieces before calling mysql_stmt_execute(). This bypasses max_allowed_packet limits and reduces the peak memory footprint (RSS) of the application.
Connector/J: Use PreparedStatement.setBinaryStream() (for BLOB) or PreparedStatement.setCharacterStream() (for TEXT).
Protocol: These APIs utilize the COM_STMT_SEND_LONG_DATA command, which appends data to a parameter on the server side.
0-Based Indexing: Parameter numbering starts at 0.
The is_null Flag: This must be 0; if set to nonzero, the server may discard the streamed data.
Resetting: Use mysql_stmt_reset() to clear all accumulated long data on the server if you need to abort or retry.
Chunk Size: A practical performance "sweet spot" is between 64 KB and 1 MB per chunk.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Last updated
Was this helpful?
Was this helpful?