Created
April 8, 2020 18:26
-
-
Save xeoncross/5897b1abf1566ea16c73c050d07d85fa to your computer and use it in GitHub Desktop.
MySQL / MariaDB bitmap operations on records
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment