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

Question

我想创建一个表列，用于存储一个500字节的位图（500字节*每字节8位=4000位），并对位图中特定索引处的位进行变异操作（设为1或0）。

然而，有关位图的文档页面大部分为空白，只留下原始位函数作为唯一指南。在MySQL中如何创建、计数、读取和变异位图作为列类型呢？

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

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

然而，如何打印长度可能为4000位的二进制/ blob / varbinary字符串？

（备注：不是在谈论位图索引）

- Xeoncross

---

https://dev.mysql.com/doc/refman/8.0/en/bit-functions.html - Barmar

我认为你需要使用varbinary或blob数据类型，因为没有公开的位图数据类型。 - Barmar

@nbk 这个问题与图像（或PHP）无关。 - Xeoncross

我知道，但关系型数据库管理系统并不是图像处理软件。虽然在 MySQL 8 中可以操作位，但一次更改那么多位是不可能的，因此需要一个专门的脚本来完成。另外这只是一条评论，而不是答案。你可以将位图转换为十六进制字符串，操纵其中的十六进制值，然后再将其转回二进制大对象，但只要你的位操作还像现在这样含糊不清，这种方法就行不通。 - nbk

你需要哪个版本的MySQL？如果你需要5.7或之前的版本，请说明；我有你需要的一些代码。 - Rick James

Answer 1

首先，升级到MySQL 8.0。之前的MySQL版本不支持此操作。

根据想要存储的位域长度，应使用BINARY、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 |
+------------------+

现在，你应该考虑一下SQL是否是最适合这项任务的工具，即使使用了MySQL 8.0的功能。在大多数其他编程语言中，执行位运算更加灵活和强大。

- Bill Karwin

---

我在系统的其余部分中使用RDBMS，所以让MySQL在位图/位集/位数组上工作可以使我避免为了设置/取消设置单个位而拉下500字节。显然，这对于MariaDB/MySQL来说比我最初想象的要困难得多。不确定我是否愿意仅针对这个字段使用键值存储。 - Xeoncross

我理解你不想让你的堆栈更加复杂，但这并不能改变MySQL没有适合你所需功能的事实。 - Bill Karwin

@Xeoncross，你认为update mytable set bits = bits | b'01000';有什么问题吗？使用这种方法不需要下载500字节的数据，我已经在成千上万条记录中尝试过并且效果很好，可以与SELECT和UPDATE一起使用。我甚至已经在SQLite中使用了BLOB数据类型。 - Christos Lytras

错误信息已经解释了。两个操作数必须具有相同的长度，但在这种情况下，“bits”长度为500字节，而右操作数长度为1字节。 - Bill Karwin

Answer 2

使用BLOB作为数据类型，并编写一个函数，该函数将：

• 提取需要更新的字节
• 更改字节中的位
• 将更改后的字节插入到原始位置的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;

我们有两个二进制掩码（每个3字节）- 一个全是零，一个全是一。在两个掩码中，我们将位于位置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中，它变得更简单了，因为我们不需要提取字节，可以在blob上使用位运算。但是我们需要确保操作数的长度相同：

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上查看

- Paul Spiegel

---

感谢您详细的回答。最近我有点忙，错过了它。我将开始第二个赏金以奖励您。 - Xeoncross