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

The COMPRESS() function requires MariaDB to have been compiled with a compression library such as zlib. If MariaDB hasn’t been compiled with such a compression library, COMPRESS() returns null.

Syntax

The syntax goes like this:

COMPRESS(string_to_compress)

Where string_to_compress is the string you want to compress.

Example

Here’s a basic example:

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

Result:

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

Here, I used the LENGTH() function to get the length in bytes of the uncompressed and compressed strings respectively. I used the REPEAT() function to repeat the same letter 50 times before compressing it.

Here’s another example that uses the copyright symbol (©):

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

Result:

+--------------+------------+
| Uncompressed | Compressed |
+--------------+------------+
|          100 |         17 |
+--------------+------------+

The copyright symbol is a two-byte character, and its uncompressed length is twice the size of a single-byte character. However, compressing this string of repeated two-byte characters brings it down to a similar size to the compressed string of repeated single-byte characters.

Here’s one that uses a three-byte character:

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

Result:

+--------------+------------+
| Uncompressed | Compressed |
+--------------+------------+
|          150 |         18 |
+--------------+------------+

In this case, the effect is even greater. The repeated three-byte character string is reduced to a similar size to the repeated one and two-byte character strings in the previous examples.

Null Arguments

Passing null returns null:

SELECT COMPRESS(null);

Result:

+----------------+
| COMPRESS(null) |
+----------------+
| NULL           |
+----------------+

Missing Argument

Calling COMPRESS() with the wrong number of arguments, or without passing any arguments results in an error:

SELECT COMPRESS();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'COMPRESS'

Check for Compression

As mentioned, the COMPRESS() function requires MariaDB to have been compiled with a compression library such as zlib. If MariaDB hasn’t been compiled with such a compression library, COMPRESS() returns null.

You can check the have_compress system variable to see whether or not the zlib compression library is accessible to the server:

SELECT @@have_compress;

Result:

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

In this case, the zlib compression library is accessible to the server, and the result is YES.

If the zlib compression library is not accessible to the server, the result would be NO.