How do you store and mutate a bitmap/bitset using MySQL?

I want to create a table column that stores a 500 byte bitmap (500 bytes * 8 bits per byte = 4000 bits) and do operations to mutate bits (set to 1 or 0) at certain indexes in the bitmap.

However, the documentation page on bitmaps is mostly empty leaving me with the raw bit functions as the only guide. How do you create, count, read, and mutate a bitmap as a column type in MySQL?

Using bin and lpad you can print a 64 bit number out as a binary string.

LPAD(BIN(34), 64, '0')
0000000000000000000000000000000000000000000000000000000000100010

However, how do you print out a binary/blob/varbinary string that might be 4000 bits long?

(Note: not talking about bitmap indexes)

Solution

Use BLOB as data type and write a function wich will:

extract the byte which needs to be updated
change the bit in the byte
insert the changed byte into the blob at the original position

Here is one implementation:

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 ;

A simple test:

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

We have two blob bitmasks (3 bytes each) - One full of zeros and one full of ones. In both we set the bit at position 10 (11th bit from right) to 1 and the bit at position 11 (12th bit from right) to 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;

Result:

| id  | hex(b) | to_base2(b)                |
| --- | ------ | -------------------------- |
| 1   | 000400 | 00000000 00000100 00000000 |
| 2   | FFF7FF | 11111111 11110111 11111111 |

View on DB Fiddle

Note: to_base2() is a custom function that returns a string with a bit representation of a BLOB and is only used for presentation purpose.

This works for MySQL 5.x as well as for 8.0.

It is possible to implement it inline in a single expression (without the need of a function) - But that is rather unreadable:

update test t
cross join (select 10 as pos, 1 as val) i -- input
set t.b = insert(
  t.b,
  length(t.b) - (i.pos div 8),
  1,
  char(ord(
    substring(t.b, length(t.b) - (i.pos div 8), 1))
    & ~(1 << (i.pos mod 8))
    | (i.val << (i.pos mod 8)
  ))
);

View on DB Fiddle

In MySQL 8.0 it's a bit simpler, since we don't need to extract the byte and can use bit operations on blobs. But we need to make sure, that the oprerands are of the same length:

update test t
cross join (select 10 as pos, 1 as val) i -- input
set t.b = t.b
  & (~(concat(repeat(0x00,length(t.b)-1),char(1)) << i.pos))
  | (concat(repeat(0x00,length(t.b)-1),char(i.val)) << i.pos)

View on DB Fiddle

Another way:

update test t
cross join (select 10 as pos, 1 as val) i -- input
set t.b = 
  case when i.val = 1
    then t.b | concat(repeat(0x00,length(t.b)-1),char(1)) << i.pos
    else t.b & ~(concat(repeat(0x00,length(t.b)-1),char(1)) << i.pos)
  end

View on DB Fiddle