How the BIT_AND() function works in Mariadb?

MariaDB’s BIT_AND() function is used to perform a bitwise AND operation on all bits in a given expression.

Posted on

The MariaDB BIT_AND() function is used to perform a bitwise AND operation on all bits in a given expression. This function is particularly useful in scenarios where you need to combine bits from multiple rows to find commonalities, such as permissions settings or feature flags.

Syntax

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

BIT_AND(expr) [over_clause]

This function takes an expression expr as an argument and performs the bitwise AND operation. It operates with 64-bit (BIGINT) precision, making it suitable for large datasets. The optional over_clause allows BIT_AND() to be used as a window function.

Examples

Example 1: Basic Usage

This example demonstrates the basic usage of BIT_AND() without any tables:

SELECT BIT_AND(3);

+------------+
| BIT_AND(3) |
+------------+
|          3 |
+------------+

The output is 3 because the bitwise AND of 3 (which is 011 in binary) with itself is 011.

Example 2: With Multiple Numbers

Here we’ll see how BIT_AND() works with multiple numbers:

SELECT BIT_AND(3 & 1);

+----------------+
| BIT_AND(3 & 1) |
+----------------+
|              1 |
+----------------+

The output is 1 because the bitwise AND of 3 (011 in binary) and 1 (001 in binary) is 001.

Example 3: No Rows Match

This example shows what happens when no rows match:

SELECT BIT_AND(NULL);

+----------------------+
| BIT_AND(NULL)        |
+----------------------+
| 18446744073709551615 |
+----------------------+

When no rows match, BIT_AND() returns a value with all bits set to 1, which is 18446744073709551615 in a 64-bit system.

Example 4: Using with Tables

To demonstrate BIT_AND() with tables, we’ll create a simple table:

DROP TABLE IF EXISTS example;
CREATE TABLE example (a INT);
INSERT INTO example VALUES (3), (1), (7);

Now, let’s use BIT_AND():

SELECT BIT_AND(a) FROM example;

+------------+
| BIT_AND(a) |
+------------+
|          1 |
+------------+

The output is 1 because the bitwise AND of 3 (011), 1 (001), and 7 (111) is 001.

Example 5: Window Function

Here’s how to use BIT_AND() as a window function:

DROP TABLE IF EXISTS example;
CREATE TABLE example (a INT, b INT);
INSERT INTO example VALUES (3, 1), (1, 1), (7, 2);

SELECT a, BIT_AND(a) OVER (PARTITION BY b) FROM example;

+------+----------------------------------+
| a    | BIT_AND(a) OVER (PARTITION BY b) |
+------+----------------------------------+
|    3 |                                1 |
|    1 |                                1 |
|    7 |                                7 |
+------+----------------------------------+

The BIT_AND() function is applied to each partition of column b, resulting in 1 for the first partition and 7 for the second.

Here are a few functions related to MariaDB’s BIT_AND():

  • MariaDB’s BIT_OR() function is used to perform a bitwise OR operation on all bits in an expression.
  • MariaDB’s BIT_XOR() function is used to perform a bitwise XOR operation on all bits in an expression.
  • MariaDB’s BIT_COUNT() function is used to count the number of bits that are set in an expression.

Conclusion

The BIT_AND() function in MariaDB is a powerful tool for performing bitwise operations across multiple rows. Its ability to be used as an aggregate or window function adds to its versatility in handling various data processing tasks. Understanding how to use BIT_AND() and related functions can greatly enhance your data manipulation capabilities in MariaDB.