The COMPRESS SQL Function in MySQL and MariaDB - Compress string
COMPRESS
Syntax:
COMPRESS(String)
Return value:
BINARY
Function type:
Encryption & Compression
Function Description
The COMPRESS() function compresses "String" and returns the result as a binary string.
This function requires MySQL or MariaDB to be compiled with a compression library like zlib, otherwise the return value is always NULL.
Numeric values are interpreted as a character string.
If "String" contains no characters, the function returns an empty string.
If the value of "String" is NULL, the COMPRESS() function returns NULL.
The compressed string can be uncompressed with UNCOMPRESS().
SQL Examples for the COMPRESS function
1 2 3 4 5 6 7 8 910
SELECThex(compress('Test Test Test Test Test'));SELECTbit_length('Test Test Test Test Test');SELECTbit_length(compress('Test Test Test Test Test'));SELECThex(compress(123123123));SELECTcompress('');SELECTcompress(null);
hex(compress('Test Test Test Test Test'))
varchar(80) BINARY
18000000789C0B492D2E5108C124006AD508A1
bit_length('Test Test Test Test Test')
bigint(11)
192
bit_length(compress('Test Test Test Test Test'))
bigint(11)
152
hex(compress(123123123))
varchar(44) BINARY
09000000789C3334323604230008CD01C3
compress('')
varchar(12) BINARY
compress(null)
varchar(12) BINARY
NULL
The examples were created with the MyWAY SQL manager: Download
How to use the COMPRESS() function in MySQL and MariaDB databases
MySQL and MariaDB provide the COMPRESS() function used to compress data using the zlib compression algorithm, where the function takes a string as input and returns the compressed data as a binary string.
To decompress the compressed data, the UNCOMPRESS() function can be used.
The COMPRESS() function can be used to reduce the disk space required to store large text or binary data, when transferring data over low-bandwidth networks to store in cache systems, or to improve query performance by compressing data. The effectiveness of the data compression depends on the properties of the data to be compressed.
