MariaDB COMPRESS() Function

In MariaDB, COMPRESS() is a built-in function that compresses a string and returns the result as a binary string.

To uncompress the compressed contents of the COMPRESS() function, use the UNCOMPRESS() function.

check availability

The MariaDB COMPRESS() function requires MariaDB to be compiled with a compression library such as zlib. If MariaDB has not been compiled with such a compression library, COMPRESS() will not work, it will return NULL.

To determine whether the COMPRESS() function works correctly in the current MariaDB database server, use the have_compress system variable.

This statement returns whether the current MariaDB database server supports compression:

SELECT @@have_compress;

Output:

+-----------------+
| @@have_compress |
+-----------------+
| YES             |
+-----------------+

If the server has access to the zlib compression library, the result is YES, otherwise the result will be NO.

MariaDB COMPRESS() Syntax

Here is the syntax of the MariaDB COMPRESS() function:

COMPRESS(str)

Parameters

str

Required. A string to compress.

Return value

The MariaDB COMPRESS() function compresses the specified string str and returns the compressed result as a binary string.

If the argument is NULL, the MariaDB COMPRESS() function returns NULL.

MariaDB COMPRESS() Examples

Example 1

To compress the string hello, use the following statement like this:

SELECT COMPRESS('hello'), LENGTH(COMPRESS('hello'));

Output:

+--------------------------------------+---------------------------+
| COMPRESS('hello')                    | LENGTH(COMPRESS('hello')) |
+--------------------------------------+---------------------------+
| 0x05000000789CCB48CDC9C90700062C0215 |                        17 |
+--------------------------------------+---------------------------+

Here, the output shows the hexadecimal equivalent of the binary string. To disable displaying hexadecimal, use the --binary-as-hex=false parameter to disable displaying binary content as hexadecimal.

The LENGTH() function display the compressed data length.

To compress empty strings, use a statement like this:

SELECT COMPRESS(''), LENGTH(COMPRESS(''));

Output:

+----------------------------+----------------------+
| COMPRESS('')               | LENGTH(COMPRESS('')) |
+----------------------------+----------------------+
| 0x                         |                    0 |
+----------------------------+----------------------+

From the above we can see that the compressed length of very short string is longer than the original string. Let’s look at an example of compressing a very long string. In the following example, we use the REPEAT() function to construct a string with a length of 3000.

SELECT LENGTH(COMPRESS(REPEAT('abc', 1000)));

Output:

+---------------------------------------+
| LENGTH(COMPRESS(REPEAT('abc', 1000))) |
+---------------------------------------+
|                                    33 |
+---------------------------------------+

Here, the REPEAT('abc', 1000) function will repeat 'abc' 1000 times and return a string with a length of 3000. After being compressed by MariaDB COMPRESS(), the length is only 33.

To calculate the length of the original string of a compressed string, use the UNCOMPRESSED_LENGTH() function.

SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('abc', 1000)));

Output:

+----------------------------------------------------+
| UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('abc', 1000))) |
+----------------------------------------------------+
|                                               3000 |
+----------------------------------------------------+

Example 2

You can compare the length before and after compression in this example:

Here’s a basic example:

SELECT
    LENGTH(REPEAT('z', 50)) AS "Uncompressed",
    LENGTH(COMPRESS(REPEAT('z', 50))) AS "Compressed";

Output:

+--------------+------------+
| Uncompressed | Compressed |
+--------------+------------+
|           50 |         16 |
+--------------+------------+

Conclusion

In MariaDB, COMPRESS() is a built-in function that compresses a string and returns the result as a binary string.