How the COMPRESS() function works in Mariadb?

The COMPRESS() function is a string function that compresses a string and returns the result as a binary string.

Posted on

The COMPRESS() function is a string 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. Otherwise, the return value is always NULL. The compressed string can be uncompressed with UNCOMPRESS(). The COMPRESS() function can be used to reduce the storage space of large strings, or to perform various operations based on the compressed strings.

Syntax

The syntax of the COMPRESS() function is as follows:

COMPRESS(string_to_compress)

Where:

  • string_to_compress is an expression that returns a string value to be compressed.

The return type of the function is a binary string value.

Examples

Example 1: Compressing and uncompressing a string

In this example, we use the COMPRESS() function to compress a string and then use the UNCOMPRESS() function to uncompress it. We use the SELECT statement to display the result.

SELECT UNCOMPRESS(COMPRESS('Hello World')) AS string;

The output is:

+-------------+
| string      |
+-------------+
| Hello World |
+-------------+

Example 2: Compressing and uncompressing a column value

In this example, we use the COMPRESS() function to compress a column value and then use the UNCOMPRESS() function to uncompress it. We use the products table as an example, which has the following structure and data:

DROP TABLE IF EXISTS products;
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  description VARCHAR(100)
);

INSERT INTO products VALUES
(1, 'Laptop', 999.99, 'A high-performance laptop'),
(2, 'Mouse', 19.99, 'A wireless mouse'),
(3, 'Keyboard', 49.99, NULL),
(4, 'Monitor', 199.99, 'A 24-inch monitor'),
(5, 'Speaker', 29.99, NULL);

We use the SELECT statement to display the product name and the uncompressed description.

SELECT name, UNCOMPRESS(COMPRESS(description)) AS description
FROM products;

The output is:

+----------+---------------------------+
| name     | description               |
+----------+---------------------------+
| Laptop   | A high-performance laptop |
| Mouse    | A wireless mouse          |
| Keyboard | NULL                      |
| Monitor  | A 24-inch monitor         |
| Speaker  | NULL                      |
+----------+---------------------------+

Example 3: Comparing the length of compressed and uncompressed strings

In this example, we use the COMPRESS() function to compress a string and then use the LENGTH() function to compare the length of the compressed and uncompressed strings. We use the SELECT statement to display the result.

SELECT LENGTH('Hello World') AS original_length,
       LENGTH(COMPRESS('Hello World')) AS compressed_length;

The output is:

+-----------------+-------------------+
| original_length | compressed_length |
+-----------------+-------------------+
|              11 |                23 |
+-----------------+-------------------+

This shows that the compressed string is longer than the original string, because the compression algorithm adds some overhead to the binary string. However, for longer and more repetitive strings, the compression can reduce the length significantly.

Some other functions that are related to the COMPRESS() function are:

  • UNCOMPRESS(): Returns a string value that is the uncompressed version of a compressed binary string. The syntax is UNCOMPRESS(compressed_string).
  • UNCOMPRESSED_LENGTH(): Returns the length of a string value before it was compressed. The syntax is UNCOMPRESSED_LENGTH(compressed_string).
  • LENGTH(): Returns the number of bytes in a given string. The syntax is LENGTH(string).
  • CHARACTER_LENGTH(): Returns the number of characters in a given string. The syntax is CHARACTER_LENGTH(string).

For example, you can use the UNCOMPRESSED_LENGTH() function to get the length of a string before it was compressed, which is the same as the LENGTH() function applied to the original string:

SELECT UNCOMPRESSED_LENGTH(COMPRESS('Hello World')) AS original_length;

The output is:

+-----------------+
| original_length |
+-----------------+
|              11 |
+-----------------+

Conclusion

The COMPRESS() function is a useful function to compress a string and return the result as a binary string. The COMPRESS() function requires MariaDB to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL. The compressed string can be uncompressed with UNCOMPRESS(). The COMPRESS() function can be used to reduce the storage space of large strings, or to perform various operations based on the compressed strings. The COMPRESS() function can be used in various contexts, such as in SELECT, UPDATE, DELETE, WHERE, ORDER BY, and GROUP BY clauses. The COMPRESS() function can be combined with other functions to perform various string operations and analyses.