Bitwise operators in select statements for using Bitmasks: MySQL basic

Using bitwise operators and defined constants.

You must execute the code again to save those values in the database.

-- Create the table in MySQL
CREATE TABLE store_orderitem (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    order_name VARCHAR(100) NOT NULL,
    product VARCHAR(50) NOT NULL,
    quantity INT NOT NULL,
    bitmask TEXT NOT NULL,
    CHECK (JSON_VALID(bitmask) OR bitmask IS NULL)
);

-- Insert data into the table
INSERT INTO store_orderitem (id, order_name, product, quantity, bitmask) VALUES (11, 'MyOrder', 'pants', 1, '2129924');
INSERT INTO store_orderitem (id, order_name, product, quantity, bitmask) VALUES (12, 'MyOrder', 'pants', 1, '2129924');
INSERT INTO store_orderitem (id, order_name, product, quantity, bitmask) VALUES (13, 'MyOrder', 'pants', 1, '2359360');
INSERT INTO store_orderitem (id, order_name, product, quantity, bitmask) VALUES (14, 'MyOrder', 'pants', 1, '2359360');

-- 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
)

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

id	order_name	product	quantity	bitmask	matched_attributes
11	MyOrder	pants	1	2129924	COLOR_LIME, MATERIAL_COTTON, SIZE_XS
12	MyOrder	pants	1	2129924	COLOR_LIME, MATERIAL_COTTON, SIZE_XS
13	MyOrder	pants	1	2359360	COLOR_MAGENTA, MATERIAL_COTTON, SIZE_L
14	MyOrder	pants	1	2359360	COLOR_MAGENTA, MATERIAL_COTTON, SIZE_L