How the UNCOMPRESSED_LENGTH() function works in Mariadb?

The UNCOMPRESSED_LENGTH() function in MariaDB is used to determine the length of a compressed string before it is uncompressed.

Posted on

The UNCOMPRESSED_LENGTH() function in MariaDB is used to determine the length of a compressed string before it is uncompressed. It takes a compressed string as input and returns the length of the uncompressed string in bytes.

Syntax

The syntax for the MariaDB UNCOMPRESSED_LENGTH() function is as follows:

UNCOMPRESSED_LENGTH(compressed_string)
  • compressed_string: The input string that has been compressed using the COMPRESS() function.

The function returns an integer value representing the length of the uncompressed string in bytes. If the input is not a compressed string, it returns NULL.

Examples

Example 1: Determining the Uncompressed Length of a Simple String

This example demonstrates how to use the UNCOMPRESSED_LENGTH() function to determine the length of a simple uncompressed string.

SELECT UNCOMPRESSED_LENGTH(COMPRESS('Hello, World!')) AS uncompressed_length;

Output:

+---------------------+
| uncompressed_length |
+---------------------+
|                  13 |
+---------------------+

The UNCOMPRESSED_LENGTH() function correctly returns the length of the uncompressed string ‘Hello, World!’, which is 13 bytes.

Example 2: Determining the Uncompressed Length of a String from a Table Column

This example shows how to use the UNCOMPRESSED_LENGTH() function to determine the length of an uncompressed string stored as a compressed column in a table.

DROP TABLE IF EXISTS example;
CREATE TABLE example (data BLOB);
INSERT INTO example VALUES (COMPRESS('This is a compressed string.'));

SELECT UNCOMPRESSED_LENGTH(data) AS uncompressed_length FROM example;

Output:

+---------------------+
| uncompressed_length |
+---------------------+
|                  28 |
+---------------------+

The UNCOMPRESSED_LENGTH() function returns the length of the uncompressed data stored in the data column of the example table, which is 29 bytes.

Example 3: Determining the Uncompressed Length of a Longer String

This example demonstrates how the UNCOMPRESSED_LENGTH() function handles determining the length of a longer uncompressed string.

SET @original_string = REPEAT('Lorem ipsum dolor sit amet, ', 100);
SET @compressed_string = COMPRESS(@original_string);

SELECT UNCOMPRESSED_LENGTH(@compressed_string) AS uncompressed_length;

Output:

+---------------------+
| uncompressed_length |
+---------------------+
|                2800 |
+---------------------+

In this example, a long string is compressed using the COMPRESS() function, and then the UNCOMPRESSED_LENGTH() function is used to determine the length of the uncompressed string, which is 2800 bytes.

Example 4: Handling an Uncompressed String

This example shows the behavior of the UNCOMPRESSED_LENGTH() function when provided with an uncompressed string.

SELECT UNCOMPRESSED_LENGTH('Hello, World!') AS uncompressed_length;

Output:

+---------------------+
| uncompressed_length |
+---------------------+
|           745301320 |
+---------------------+

When an uncompressed string is provided as input to the UNCOMPRESSED_LENGTH() function, it returns NULL because the input is not a valid compressed string.

Example 5: Comparing Compressed and Uncompressed Lengths

This example demonstrates how to compare the lengths of a compressed string and its uncompressed counterpart using the UNCOMPRESSED_LENGTH() function.

SET @original_string = 'This is a compressed string.';
SET @compressed_string = COMPRESS(@original_string);

SELECT LENGTH(@original_string) AS original_length,
       LENGTH(@compressed_string) AS compressed_length,
       UNCOMPRESSED_LENGTH(@compressed_string) AS uncompressed_length;

Output:

+-----------------+-------------------+---------------------+
| original_length | compressed_length | uncompressed_length |
+-----------------+-------------------+---------------------+
|              28 |                38 |                  28 |
+-----------------+-------------------+---------------------+

In this example, the output shows the length of the original uncompressed string, the length of the compressed string, and the length of the uncompressed string determined by the UNCOMPRESSED_LENGTH() function. This demonstrates how the UNCOMPRESSED_LENGTH() function can be used to compare the compressed and uncompressed lengths of a string.

The following are a few functions related to the MariaDB UNCOMPRESSED_LENGTH() function:

  • MariaDB COMPRESS() function is used to compress a string into a binary string.
  • MariaDB UNCOMPRESS() function is used to uncompress a string that has been compressed using the COMPRESS() function.
  • MariaDB LENGTH() function is used to determine the length of a string in bytes.

Conclusion

The UNCOMPRESSED_LENGTH() function in MariaDB is a valuable tool for determining the length of an uncompressed string before it is uncompressed. By understanding the syntax and usage examples, you can effectively incorporate this function into your SQL queries and data manipulation tasks. Whether you need to determine the size of compressed data or compare compressed and uncompressed lengths, the UNCOMPRESSED_LENGTH() function provides a convenient way to obtain this information without actually uncompressing the data.