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

First, upgrade to MySQL 8.0. This won't work on earlier versions of MySQL.

You should use BINARY, VARBINARY, or BLOB, depending on the length of the bitfields you want to store.

mysql> create table mytable ( bits binary(500) );
ERROR 1074 (42000): Column length too big for column 'bits' (max = 255); use BLOB or TEXT instead

mysql> create table mytable ( bits blob(500) );
Query OK, 0 rows affected (0.02 sec)

Use UNHEX() to form bitfields from hexadecimal strings. It's too difficult to work with binary bytes directly.

mysql> insert into mytable set bits = unhex(repeat('00', 500));
Query OK, 1 row affected (0.00 sec)

You can use bitwise operators like |, &, ^, and ~ with bitfields. But the strings must be the same length!

mysql> update mytable set bits = bits | b'01000';
ERROR 3513 (HY000): Binary operands of bitwise operators must be of equal length

It's inconvenient, but you must form strings that are the right length using CONCAT():

mysql> update mytable set bits = bits | unhex(concat(repeat('00', 498), 'ffff'));
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Reading a bit string (I have shortened it for this display):

mysql> select hex(bits) from mytable\G
*************************** 1. row ***************************
hex(bits): 000...000FFFF

The BIT_COUNT() function works:

mysql> select bit_count(bits) from mytable;
+-----------------+
| bit_count(bits) |
+-----------------+
|              16 |
+-----------------+

mysql> select bit_count(~bits) from mytable;
+------------------+
| bit_count(~bits) |
+------------------+
|             3984 |
+------------------+

By this time, you should sit back and think about whether SQL is the best tool for this task, even with the features of MySQL 8.0. You will find it more flexible and powerful to do bitwise work in most other programming languages.

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