create table employee(id int, age int, gender boolean, other varchar(1000) primary key (id))
. When there is frequent access to the smaller fields such as id, age, gender
, and less frequent access to the larger field other
, it is advisable to designate the other
column as a compressed column. Generally, only read and write operations on the other
column will trigger its compression and decompression, while access to other columns will not. This strategy further reduces the storage size of row data, enabling swifter access to frequently accessed smaller fields and significantly reducing the storage space required for less frequently accessed larger fields.BLOB
(including TINYBLOB
, MEDIUMBLOB
, and LONGBLOB
)TEXT
(including TINYTEXT
, MEDIUMTEXT
, and LONGTEXT
)VARCHAR
VARBINARY
COLUMN_FORMAT
in column_definition
is changed in TencentDB for MySQL. In addition, column compression is supported only for tables with the InnoDB storage engine.column_definition:data_type [NOT NULL | NULL] [DEFAULT default_value][AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY][COMMENT 'string'][COLLATE collation_name][COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}|COMPRESSED=[zlib]] # COMPRESSED is the compressed column keyword[STORAGE {DISK|MEMORY}][reference_definition]
CREATE TABLE t1(id INT PRIMARY KEY,b BLOB COMPRESSED);
CREATE TABLE t1(id INT PRIMARY KEY,b BLOB COMPRESSED=zlib);
DDL | Whether the Compression Attribute is Inherited |
CREATE TABLE t2 LIKE t1; | Yes |
CREATE TABLE t2 SELECT * FROM t1; | Yes |
CREATE TABLE t2(a BLOB) SELECT * FROM t1; | No |
DDL | Description |
ALTER TABLE t1 MODIFY COLUMN a BLOB; | Alters a compressed column into a non-compressed one. |
ALTER TABLE t1 MODIFY COLUMN a BLOB COMPRESSED; | Alters a non-compressed column into a compressed one. |
Parameter | Effective Immediately | Type | Default Value | Valid Values/Value Range | Description |
cdb_column_compression_enabled | Yes | bool | FALSE | TRUE/FALSE | The column compression feature can be toggled on or off. When disabled, the creation of tables with compression attributes is prohibited, although tables that already possess compression attributes remain unaffected. |
innodb_column_compression_zlib_wrap | Yes | bool | TRUE | TRUE/FALSE | If enabled, it will generate the zlib header and zlib footer for the data, and perform an adler32 checksum verification. |
innodb_column_compression_zlib_strategy | Yes | Integer | 0 | [0,4] | Column compression policy. Valid values: 0: Z_DEFAULT_STRATEGY; 1: Z_FILTERED; 2: Z_HUFFMAN_ONLY; 3: Z_RLE; 4: Z_FIXED. Generally, Z_DEFAULT_STRATEGY is the best choice for text data, while Z_RLE for image data. |
innodb_column_compression_zlib_level | Yes | Integer | 6 | [0,9] | Column compression level. Value range: 0–9. 0 indicates not to compress. The higher the value, the smaller the data size after compression, and the longer the compression duration. |
innodb_column_compression_threshold | Yes | Integer | 256 | [0, 0xffffffff] | Column compression threshold in bytes. Value range: 1–0xffffffff. Only data whose length is at or above this threshold will be compressed; otherwise, the original data will stay unchanged with only a compression header added. |
innodb_column_compression_pct | Yes | Integer | 100 | [1, 100] | Column compression ratio in percentages. Value range: 1–100. Data will be compressed only if the data size after compression/data size before compression is below this value; otherwise, the original data will stay unchanged with only a compression header added. |
Name | Type | Description |
Innodb_column_compressed | Integer | Number of column compressions, including compressions for non-compressed data and compressed data. |
Innodb_column_decompressed | Integer | Number of column decompressions, including decompressions for non-compressed data and compressed data. |
Name | Scope | Description |
Compressed column '%-.192s' can't be used in key specification | Name of the column specified for compression | The compression attribute cannot be specified for a column with an index. |
Unknown compression method: %s | Name of the compression algorithm specified in the DDL statement | An invalid compression algorithm other than zlib is specified in the CREATE TABLE or ALTER TABLE statement. |
Compressed column '%-.192s' can't be used in column format specification | Name of the column specified for compression | If the COLUMN_FORMAT attribute has been specified for a column, other attributes cannot be specified, and COLUMN_FORMAT can be used only in NDB. |
Alter table ... discard/import tablespace not support column compression | \\ | The ALTER TABLE ... DISCARD/IMPORT TABLESPACE statement cannot be executed for tables with column compression enabled. |
BLOB
(including TINYBLOB
, MEDIUMBLOB
, and LONGBLOB
)TEXT
(including TINYTEXT
, MEDIUMTEXT
, and LONGTEXT
)VARCHAR
VARBINARY
JSON
CREATE TABLE t1(id INT PRIMARY KEY,b BLOB COMPRESSED);
CREATE TABLE t1(id INT PRIMARY KEY,b BLOB COLUMN_FORMAT COMPRESSED);
innodb_min_column_compress_length
, which defaults to 256. Should the original size of a column exceed the value of this parameter, compression will be applied; otherwise, only a compression header is added without actual data compression being performed.DDL | Whether the Compression Attribute is Inherited |
CREATE TABLE t2 LIKE t1; | Yes |
CREATE TABLE t2 SELECT * FROM t1; | No |
CREATE TABLE t2(a BLOB) SELECT * FROM t1; | No |
DDL | Description |
ALTER TABLE t1 MODIFY COLUMN a BLOB; | Alters a compressed column into a non-compressed one. |
ALTER TABLE t1 MODIFY COLUMN a BLOB COMPRESSED; | Alters a non-compressed column into a compressed one. |
Parameter | Effective Immediately | Type | Default Value | Valid Values/Value Range | Description |
innodb_zlib_column_compression_level | Yes | UINT | 6 | [0-9] | Zlib compression, where 0 denotes no compression, 1 signifies the fastest compression, and 9 represents the highest level of compression achievable. As the scale progresses from 1 to 9, the compression speed decreases, yet the compression ratio increases. |
innodb_zstd_column_compression_level | Yes | UINT | 3 | [1-22] | Zstd compression employs a scale where 1 signifies the fastest compression speed, and 22 denotes the highest level of compression. As the scale progresses from 1 to 22, the compression speed decreases, yet the compression ratio increases. |
innodb_min_column_compress_length | Yes | UINT | 256 | [1,UINT_MAX32] | Adjust the compression threshold, measured in bytes. Should the original length of a column be greater than or equal to the value of this parameter, compression will be applied. Otherwise, only a compression header will be added, without actual data compression being performed. |
CREATE TABLE t1(id INT PRIMARY KEY,b BLOB COMPRESSED ALGORITHM = [ZLIB|LZ4|ZSTD]);
CREATE TABLE t1(id INT PRIMARY KEY,b BLOB COLUMN_FORMAT COMPRESSED ALGORITHM = [ZLIB|LZ4|ZSTD]);
innodb_zlib_column_compression_level
, ranging from 0 to 9. Here, 0 signifies no compression, 1 denotes the fastest compression, and 9 represents the highest degree of compression, with the default set to 6.CREATE TABLE t2 (a VARCHAR(100) COMPRESSED) ENGINE=InnoDB;SHOW CREATE TABLE t2;
Was this page helpful?