Bitmasks for Fun and Profit: Practical Applications for Web Developers

Using Bitwise Operators in The Database

Part 6 of 11 of a serialized version of Bitmasks for Fun and Profit: Practical Applications for Web Developers. Links to SQL Fiddles.

Chris Adams

Oct 08, 2024

In SQL, you can use bitwise operators to query columns that store bitmasks.

Common databases support all of the Bitwise operators.

Example Scenario

Let's say you have a users table with a column named permissions, where each bit in the permissions column represents a different permission:

1 (0b0001) - Execute permission
2 (0b0010) - Write permission
4 (0b0100) - Read permission
8 (0b1000) - Admin permission

A user with a permissions value of 5 (0b0101) has Read and Execute permissions.

Using Bitwise Operators in a `SELECT` Statement

Checking for a Specific Permission

If you want to check which users have the Write permission, you can use the Bitwise AND operator:

# SQL
SELECT user_id, permissions
FROM users
WHERE (permissions & 2) = 2;

This query checks each user's permissions column to see if the bit corresponding to 2 (Write permission) is set.

Combining Permissions

If you want to check which users have both Read and Execute permissions, you can use:

# SQL
SELECT user_id, permissions
FROM users
WHERE (permissions & 5) = 5;

In Binary:
Some permission bitmask:   1000101
5:                             101
                           -------
(permissions & 5)              101

(permissions & 5) = 5 is TRUE

Here, 5 is the binary 0101, which checks for both Read (0b100) and Execute (0b001) permissions.

Filtering Based on Any Permission

If you want to find users with either Execute or Write permissions, you can use:

# SQL
SELECT user_id, permissions
FROM users
WHERE (permissions & 3) != 0;

Here, 3 is the binary 0011, which checks for either Execute (0b001) or Write (0b010) permissions. The != 0 ensures that at least one of these permissions is present.

In Binary:
Some permission bitmask:   1000101
3:                             011
                           -------
(permissions & 3)                1
only Execute permission is set, write is not.
(permissions & 3) = 3 is FALSE

(permissions & 3) != 0 is TRUE

Basic SQLite example:

https://sqlfiddle.com/sqlite/online-compiler?id=561e6604-20e0-476d-843b-5c548e301e2c

In this SQLite example, we need to use a Common Table Expression (CTE). These also work in PostgreSQL:

# SQL
-- Define CTE for bitmask constants
WITH BitmaskConstants AS (
    SELECT
        1 AS COLOR_BLACK,
        2 AS COLOR_RED,
        4 AS COLOR_LIME,
        8 AS COLOR_BLUE,
        16 AS COLOR_YELLOW,
        32 AS COLOR_CYAN,
        64 AS COLOR_MAGENTA,
        128 AS COLOR_SILVER,
        256 AS COLOR_GRAY,
        512 AS COLOR_MAROON,
        1024 AS COLOR_OLIVE,
        2048 AS COLOR_GREEN,
        4096 AS COLOR_PURPLE,
        8192 AS COLOR_TEAL,
        16384 AS COLOR_NAVY,
        32768 AS SIZE_XS,
        65536 AS SIZE_S,
        131072 AS SIZE_M,
        262144 AS SIZE_L,
        524288 AS SIZE_XL,
        1048576 AS SIZE_XXL,
        2097152 AS MATERIAL_COTTON,
        4194304 AS MATERIAL_POLYESTER,
        8388608 AS MATERIAL_LINEN,
        16777216 AS MATERIAL_DENIM
)

SQL Statements Using Bitmasks

Here are some SQL statements to query the store_orderitem table using bitmasks:

Select all items

# SQL
SELECT * FROM store_orderitem;

Select items with a specific bitmask value

# SQL
SELECT 
 * 
FROM 
 store_orderitem 
WHERE
 bitmask = '2129924';

Select items with a specific attribute using bitwise AND

Suppose we want to find items with the Medium size attribute (bit position 2):

# SQL
SELECT 
 * 
FROM 
 store_orderitem 
WHERE 
 CAST(bitmask AS INTEGER) & 2 <> 0;

Select items with multiple specific attributes

Suppose we want to find items with Medium size and Red color (bit positions 2 and 8):

# SQL
SELECT 
 * 
FROM 
 store_orderitem 
WHERE 
 CAST(bitmask AS INTEGER) & (2 | 8) = (2 | 8);

Select items without a specific attribute

Suppose we want to find items that do not have the Large size attribute (bit position 4):

# SQL
SELECT 
 * 
FROM 
 store_orderitem 
WHERE 
 CAST(bitmask AS INTEGER) & 4 = 0;

Select items with any of several attributes

Suppose we want to find items with either Medium size or Green color (bit positions 2 and 16):

# SQL
SELECT 
 * 
FROM 
 store_orderitem 
WHERE 
 CAST(bitmask AS INTEGER) & (2 | 16) <> 0;

Example Queries Using CTE

Select all items:

# SQL
SELECT * FROM store_orderitem;

Select items with a specific bitmask value:

# SQL
SELECT * FROM store_orderitem
WHERE bitmask = '2129924';

Select items with a specific attribute using bitwise AND:

Suppose we want to find items with the Medium size attribute (bit value 131072):

# SQL

SELECT * FROM store_orderitem
WHERE CAST(bitmask AS INTEGER) & (SELECT SIZE_M FROM BitmaskConstants) <> 0;

Select items with multiple specific attributes:

Suppose we want to find items with Medium size and Red color (bit values 131072 and 2):

# SQL
SELECT 
* 
FROM 
 store_orderitem
WHERE 
CAST
 (bitmask AS INTEGER) 
 & 
 (
  (SELECT 
    SIZE_M
   FROM 
    BitmaskConstants) 
    |
  (SELECT 
    COLOR_RED 
   FROM 
    BitmaskConstants)
  ) = (
  (SELECT 
    SIZE_M 
   FROM 
    BitmaskConstants) 
   | 
  (SELECT 
    COLOR_RED
   FROM 
    BitmaskConstants)
);

Select items without a specific attribute\

Suppose we want to find items that do not have the Large size attribute (bit value 262144):

# SQL
SELECT * FROM store_orderitem
WHERE 
 CAST(bitmask AS INTEGER) 
  & 
 (SELECT SIZE_L FROM BitmaskConstants) = 0;

Advanced Example in SQLite: using Named Constants and Strings

This is a more advanced SQLite that can also return strings.
It uses a more complex way to represent the constants, but it makes queries easier to understand.
https://sqlfiddle.com/sqlite/online-compiler?id=82835532-f939-4bfa-a6b1-a6536e184270

# SQL
-- Define CTE for bitmask constants
WITH BitmaskConstants AS (
SELECT 1 AS bit_value, 'COLOR_BLACK' AS attribute UNION ALL
SELECT 2, 'COLOR_RED' UNION ALL
SELECT 4, 'COLOR_LIME' UNION ALL
SELECT 8, 'COLOR_BLUE' UNION ALL
SELECT 16, 'COLOR_YELLOW' UNION ALL
SELECT 32, 'COLOR_CYAN' UNION ALL
SELECT 64, 'COLOR_MAGENTA' UNION ALL
SELECT 128, 'COLOR_SILVER' UNION ALL
SELECT 256, 'COLOR_GRAY' UNION ALL
SELECT 512, 'COLOR_MAROON' UNION ALL
SELECT 1024, 'COLOR_OLIVE' UNION ALL
SELECT 2048, 'COLOR_GREEN' UNION ALL
SELECT 4096, 'COLOR_PURPLE' UNION ALL
SELECT 8192, 'COLOR_TEAL' UNION ALL
SELECT 16384, 'COLOR_NAVY' UNION ALL
SELECT 32768, 'SIZE_XS' UNION ALL
SELECT 65536, 'SIZE_S' UNION ALL
SELECT 131072, 'SIZE_M' UNION ALL
SELECT 262144, 'SIZE_L' UNION ALL
SELECT 524288, 'SIZE_XL' UNION ALL
SELECT 1048576, 'SIZE_XXL' UNION ALL
SELECT 2097152, 'MATERIAL_COTTON' UNION ALL
SELECT 4194304, 'MATERIAL_POLYESTER' UNION ALL
SELECT 8388608, 'MATERIAL_LINEN' UNION ALL
SELECT 16777216, 'MATERIAL_DENIM'
),

-- CTE to find matched attributes
MatchedAttributes AS (
    SELECT
        so.id,
        so.order_name,
        so.product,
        so.quantity,
        so.bitmask,
        bc.attribute
    FROM
        store_orderitem so
    CROSS JOIN
        BitmaskConstants bc
    WHERE
        CAST(so.bitmask AS INTEGER) & bc.bit_value <> 0
)

# SQL

-- Select and aggregate the matched attributes
SELECT
    id,
    order_name,
    product,
    quantity,
    bitmask,
    GROUP_CONCAT(attribute, ', ') AS matched_attributes
FROM
    MatchedAttributes
GROUP BY
    id, order_name, product, quantity, bitmask
ORDER BY
    id;

Result (showing limited columns for space):

id   bitmask   matched_attributes
11   2129924   COLOR_LIME, SIZE_XS, MATERIAL_COTTON
12   2129924   COLOR_LIME, SIZE_XS, MATERIAL_COTTON
13   2359360   COLOR_MAGENTA, SIZE_L, MATERIAL_COTTON
14   2359360   COLOR_MAGENTA, SIZE_L, MATERIAL_COTTON

Advanced Example in MySQL

MySQL Fiddle:
https://sqlfiddle.com/mysql/online-compiler?id=3771f7ab-5fb7-4ea8-9ba3-31ff5be55cf0

For MySQL you can do this to set constants:

# SQL
SET @COLOR_BLACK = 1;
SET @COLOR_RED = 2;
SET @COLOR_LIME = 4;
SET @COLOR_BLUE = 8;
SET @COLOR_YELLOW = 16;
SET @COLOR_CYAN = 32;
SET @COLOR_MAGENTA = 64;
SET @COLOR_SILVER = 128;
SET @COLOR_GRAY = 256;
SET @COLOR_MAROON = 512;
SET @COLOR_OLIVE = 1024;
SET @COLOR_GREEN = 2048;
SET @COLOR_PURPLE = 4096;
SET @COLOR_TEAL = 8192;
SET @COLOR_NAVY = 16384;
SET @SIZE_XS = 32768;
SET @SIZE_S = 65536;
SET @SIZE_M = 131072;
SET @SIZE_L = 262144;
SET @SIZE_XL = 524288;
SET @SIZE_XXL = 1048576;
SET @MATERIAL_COTTON = 2097152;
SET @MATERIAL_POLYESTER = 4194304;
SET @MATERIAL_LINEN = 8388608;
SET @MATERIAL_DENIM = 16777216;

You can also use a CTE:

# SQL

-- Define CTE for bitmask constants
WITH RECURSIVE BitmaskConstants AS (
    SELECT 1 AS bit_value, 'COLOR_BLACK' AS attribute UNION ALL
    SELECT 2, 'COLOR_RED' UNION ALL
    SELECT 4, 'COLOR_LIME' UNION ALL
    SELECT 8, 'COLOR_BLUE' UNION ALL
    SELECT 16, 'COLOR_YELLOW' UNION ALL
    SELECT 32, 'COLOR_CYAN' UNION ALL
    SELECT 64, 'COLOR_MAGENTA' UNION ALL
    SELECT 128, 'COLOR_SILVER' UNION ALL
    SELECT 256, 'COLOR_GRAY' UNION ALL
    SELECT 512, 'COLOR_MAROON' UNION ALL
    SELECT 1024, 'COLOR_OLIVE' UNION ALL
    SELECT 2048, 'COLOR_GREEN' UNION ALL
    SELECT 4096, 'COLOR_PURPLE' UNION ALL
    SELECT 8192, 'COLOR_TEAL' UNION ALL
    SELECT 16384, 'COLOR_NAVY' UNION ALL
    SELECT 32768, 'SIZE_XS' UNION ALL
    SELECT 65536, 'SIZE_S' UNION ALL
    SELECT 131072, 'SIZE_M' UNION ALL
    SELECT 262144, 'SIZE_L' UNION ALL
    SELECT 524288, 'SIZE_XL' UNION ALL
    SELECT 1048576, 'SIZE_XXL' UNION ALL
    SELECT 2097152, 'MATERIAL_COTTON' UNION ALL
    SELECT 4194304, 'MATERIAL_POLYESTER' UNION ALL
    SELECT 8388608, 'MATERIAL_LINEN' UNION ALL
    SELECT 16777216, 'MATERIAL_DENIM'
),

-- CTE to find matched attributes
MatchedAttributes AS (
    SELECT
        so.id,
        so.order_name,
        so.product,
        so.quantity,
        so.bitmask,
        bc.attribute
    FROM
        store_orderitem so
    JOIN
        BitmaskConstants bc
    ON
        CAST(so.bitmask AS UNSIGNED) & bc.bit_value <> 0
)

Getting values from the CTE can be a little bit convoluted, but once you understand how it works it is not too bad:

# SQL
# BitmaskConstants has two columns, bit_value
# and attribute.  attribute is a string
# the result 
# of this statement 4
SELECT bit_value FROM BitmaskConstants WHERE attribute = 'COLOR_LIME'

Here are some representative MySQL queries using bitwise operations on the store_orderitem table using various bitwise operators (&, |, ^, ~, <<, >>).

# SQL
-- Find all items that have the COLOR_RED attribute (bitmask 2)
-- Using bitwise AND (&) to check if the COLOR_RED bit is set
SELECT * 
FROM store_orderitem 
WHERE CAST(bitmask AS UNSIGNED) & 2 <> 0;

-- Find all items that have either COLOR_RED or COLOR_BLUE
-- Using bitwise OR (|) to check for either attribute
SELECT * 
FROM store_orderitem 
WHERE CAST(bitmask AS UNSIGNED) & (2 | 8) <> 0;

-- Find items that have COLOR_RED but not COLOR_BLUE
-- Using bitwise XOR (^) to exclude COLOR_BLUE
SELECT * 
FROM store_orderitem 
WHERE (CAST(bitmask AS UNSIGNED) & 2) 
AND (CAST(bitmask AS UNSIGNED) ^ 8) = CAST(bitmask AS UNSIGNED);

-- Negate COLOR_BLUE using bitwise NOT (~)
-- Finding items that do not have COLOR_BLUE
SELECT * 
FROM store_orderitem 
WHERE CAST(bitmask AS UNSIGNED) & ~8 = CAST(bitmask AS UNSIGNED);

-- Shift left (<<) and shift right (>>) operations
-- Checking attributes after shifting bits
SELECT * 
FROM store_orderitem 
WHERE (CAST(bitmask AS UNSIGNED) << 1) & 16 <> 0;

SELECT * 
FROM store_orderitem 
WHERE (CAST(bitmask AS UNSIGNED) >> 1) & 4 <> 0;

-- Combine attributes and match items
-- Using multiple bitwise operations
SELECT * 
FROM store_orderitem 
WHERE (CAST(bitmask AS UNSIGNED) & (2 | 4 | 16)) <> 0;

Here are some examples using named constants from BitmaskConstants:

# SQL
-- Query to check if a specific attribute (e.g., COLOR_BLACK) is set
SELECT 
    id, order_name, product, quantity, bitmask 
FROM 
    store_orderitem 
WHERE 
    CAST(bitmask AS UNSIGNED) & 
    (SELECT bit_value FROM BitmaskConstants WHERE attribute = 'COLOR_BLACK') <> 0;

-- Query to check if none of the attributes (e.g., COLOR_BLACK, SIZE_XL) are set
SELECT 
    id, order_name, product, quantity, bitmask 
FROM 
    store_orderitem 
WHERE 
    CAST(bitmask AS UNSIGNED) & 
    (SELECT SUM(bit_value) FROM BitmaskConstants 
     WHERE attribute IN ('COLOR_BLACK', 'SIZE_XL')) = 0;

-- Query to toggle a specific attribute (e.g., MATERIAL_COTTON) using XOR
SELECT 
    id, order_name, product, quantity, 
    CAST(bitmask AS UNSIGNED) ^ 
    (SELECT bit_value FROM BitmaskConstants WHERE attribute = 'MATERIAL_COTTON') 
    AS new_bitmask 
FROM 
    store_orderitem;

-- Query to check if a specific attribute (e.g., MATERIAL_DENIM) is not set
SELECT 
    id, order_name, product, quantity, bitmask 
FROM 
    store_orderitem 
WHERE 
    NOT CAST(bitmask AS UNSIGNED) & 
    (SELECT bit_value FROM BitmaskConstants WHERE attribute = 'MATERIAL_DENIM') <> 0;

-- Query to check if either of two attributes (e.g., COLOR_BLUE, SIZE_M) is set
SELECT 
    id, order_name, product, quantity, bitmask 
FROM 
    store_orderitem 
WHERE 
    (CAST(bitmask AS UNSIGNED) & 
    (SELECT bit_value FROM BitmaskConstants WHERE attribute = 'COLOR_BLUE')) 
    | 
    (CAST(bitmask AS UNSIGNED) & 
    (SELECT bit_value FROM BitmaskConstants WHERE attribute = 'SIZE_M')) <> 0;

-- Query to check if both of two attributes (e.g., MATERIAL_LINEN, SIZE_L) are set
SELECT 
    id, order_name, product, quantity, bitmask 
FROM 
    store_orderitem 
WHERE 
    (CAST(bitmask AS UNSIGNED) & 
    (SELECT bit_value FROM BitmaskConstants WHERE attribute = 'MATERIAL_LINEN')) 
    & 
    (CAST(bitmask AS UNSIGNED) & 
    (SELECT bit_value FROM BitmaskConstants WHERE attribute = 'SIZE_L')) <> 0;

This is part 6 of 11 of a serialized version of my book:

https://amzn.to/4f3UA15

Bitmasks for Fun and Profit: Practical Applications for Web Developers

Use bitmasks as powerful filters that decouple rules from logic, simplify data relationships and reduce code complexity.

All code in the book is syntax highlighted and printed in full color.
I publish several books about Guitar, Music and Programming on my Author Page on Amazon:
https://amzn.to/3QlvX5K
Follow me at Torus Head Studios!
https://torusheadstudios.com/

Torus Head Studios Substack

Discussion about this post

Torus Head Studios Substack

Using Bitwise Operators in The Database

Part 6 of 11 of a serialized version of Bitmasks for Fun and Profit: Practical Applications for Web Developers. Links to SQL Fiddles.

Example Scenario

Using Bitwise Operators in a SELECT Statement

Checking for a Specific Permission

Combining Permissions

Filtering Based on Any Permission

Basic SQLite example:

SQL Statements Using Bitmasks

Select all items

Select items with a specific bitmask value

Select items with a specific attribute using bitwise AND

Select items with multiple specific attributes

Select items without a specific attribute

Select items with any of several attributes

Example Queries Using CTE

Select items without a specific attribute\

Advanced Example in SQLite: using Named Constants and Strings

Advanced Example in MySQL

This is part 6 of 11 of a serialized version of my book:

Bitmasks for Fun and Profit: Practical Applications for Web Developers

Discussion about this post

Using Bitwise Operators in a `SELECT` Statement