MySQL / MariaDB bitmap operations on records

mysql_bitmap.sql

-- https://stackoverflow.com/questions/60645538/how-do-you-store-and-mutate-a-bitmap-bitset-using-mysql

create table test(id int, b blob);
insert into test(id, b) select 1, 0x000000;
insert into test(id, b) select 2, 0xffffff;

delimiter //
create function set_bit(b blob, pos int, val int) returns blob reads sql data
comment 'changes the bit at position <pos> (0: right most bit) to <val> in the blob <b>'
begin
    declare len int;      -- byte length of the blob
	declare byte_pos int; -- position of the affected byte (1: left most byte)
    declare bit_pos  int; -- position within the affected byte (0: right most bit)
    declare byte_val int; -- value of the affected byte
    
    set len = length(b);
    set byte_pos = len - (pos div 8);
    set bit_pos = pos mod 8;
    set byte_val = ord(substring(b, byte_pos, 1)); -- read the byte
	set byte_val = byte_val & (~(1 << bit_pos));   -- set the bit to 0
    set byte_val = byte_val | (val << bit_pos);    -- set the bit to <val>

    return insert(b, byte_pos, 1, char(byte_val)); -- replace the byte and return
end //
delimiter ;


delimiter //
create function to_base2(b blob) returns text deterministic
begin
  declare t text default '';
  declare i int default 1;
  while (i <= length(b)) do
    set t = concat(t, lpad(conv(ord(substring(b,i,1)),10,2),8,0), ' ');
    set i = i + 1;
  end while;
  return trim(t);
end //
delimiter ;


-- https://www.db-fiddle.com/f/a1HwEQU7afCcnvPFvcxt69/0
update test set b = set_bit(b, 10, 1);
update test set b = set_bit(b, 11, 0);

select id, hex(b), to_base2(b) from test;