如何使用MySQL存储和更改位图/位集？

Question

我希望创建一个表列，该列存储500字节位图(500 bytes * 8 bits per byte = 4000 bits)，并在位图中的某些索引上执行修改位(设置为1或0)的操作。

然而，位图上的文档页大多为空。给我留下了原始位函数作为唯一的指南。如何在MySQL中将位图作为列类型创建、计数、读取和变异？

使用bin和lpad，您可以将64位数字作为二进制字符串输出。

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

但是，如何打印可能有4000位长的二进制/blob/var二进制字符串？

(注:不谈位图索引)

Answer 1

使用BLOB作为数据类型，并编写函数will：

• 提取需要更新的字节
• 更改字节中的位
• 将更改的字节插入原始位置的blob中。

以下是一个实现：

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 ;

一个简单的测试：

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

我们有两个blob位掩码(每个3个字节)--一个满是零，一个满是1。在这两种情况下，我们都将位置10 (从右到右的第11位)的位设置为1，而在位置11 (从右到右的第12位)的位设置为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;

结果：

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

关于DB Fiddle的看法

注意：to_base2()是一个自定义函数，它返回带有BLOB位表示的字符串，仅用于表示目的。

这适用于MySQL 5.x和8.0。

可以在一个表达式中内联地实现它(不需要一个函数)--但这是相当不可读的：

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

关于DB Fiddle的看法

在MySQL 8.0中，这有点简单，因为我们不需要提取字节，可以在blobs上使用位操作。但我们需要确保，操纵台的长度是相同的：

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) 

关于DB Fiddle的看法

另一种方式是：

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

关于DB Fiddle的看法

Answer 2

首先，升级到MySQL 8.0。这在早期版本的MySQL上是行不通的。

您应该使用二进制、VARBINARY或BLOB，这取决于要存储的位字段的长度。

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)

使用UNHEX()从十六进制字符串生成位字段。直接处理二进制字节太困难了。

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

您可以在位字段中使用按位运算符，如|、&、^和~。但是字符串必须是相同的长度！

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

这是不方便的，但是您必须使用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

读取一个位字符串(为了这个显示，我缩短了它)：

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

BIT_COUNT()函数可以工作：

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

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

此时，您应该考虑一下，即使使用MySQL 8.0的特性，SQL是否是完成此任务的最佳工具。您会发现在大多数其他编程语言中进行按位工作更加灵活和强大。