MySQL按位与256位二进制值

Question

我打算在MySQL表列中存储一个256位长的二进制值。

我应该使用哪种列类型（blob？）才能对其进行位运算（最好提供AND的示例）。

- rich

---

256位只有32字节。不是真正的二进制大对象。您只需要在数据库上存储这些值吗？还是计划对它们执行一些操作（按值搜索，模式匹配，算术运算，位逻辑运算）... - Sylvain Leroux

我计划在它们上面使用位运算。 - rich

好的，只有位运算。由于这是一个有趣的问题，我做了一些实验。您能否确认如果“label”是一个“BLOB”列，下面来自您答案的表达式bin(label & b'01..00')是否按预期工作？对我来说，它不起作用？！？总是返回0... - Sylvain Leroux

虽然在选择中它可以工作，但我似乎无法使其在where子句中工作，并且我还没有检查过>64位。 - rich

我认为这个方案可能适用于64位。但是绝对不会超过64位。我在下面编辑了我的答案，以我认为是唯一可行的解决方案：将您的256位值拆分为4个64位BIGINT UNSIGNED。虽然不是非常优雅，但它可以正常工作，并且您将能够访问MySQL按位运算符的“全部功能”，以及各种转换函数（HEX（），BIN（））。 - Sylvain Leroux

回到“BLOB”问题，我确认这在MySQL5.1上不起作用：有一个带有“BLOB”列的表，“insert into t values (b'11110000');”，然后“select hex(b) from t;”会给出正确的“F0”。但是“select bin(b) from t;”会产生明显错误的“0”。原因是“BIN（）”仅适用于数字。而“HEX”接受数字或字符串...位运算符对BLOB也不起作用：“select hex(b & b'01111111') from t;”会产生“0”。 - Sylvain Leroux

Answer 1

我认为在SQL层面上无法对256位的值执行按位操作，因为文档明确说明：

MySQL使用BIGINT（64位）算术进行位运算，因此这些运算符的最大范围为64位。

http://dev.mysql.com/doc/refman/5.5/en/bit-functions.html#operator_bitwise-and

---

关于存储这些值，TINYBLOB 是可行的，但我个人更喜欢简单地使用 BINARY(32)（一个由 32 个字节组成的二进制字符串 -- 256 位）。

---

---

在写这篇文章时，我想到了一个技巧。如果我们只能使用64位值（BIGINT UNSIGNED），为什么不将256位存储为4个64位字。虽然不太优雅，但这样也可以工作。特别是因为您只需要进行按位操作：

ABCD₃₂ & WXYZ₃₂ == A₈ & W₈, B₈ & X₈, C₈ & Y₈, D₈ & Z₈

非常基本地说：

create table t (a bigint unsigned, 
                b bigint unsigned, 
                c bigint unsigned, 
                d bigint unsigned);

在插入时，256位的值必须被“拆分”成4个字。

-- Here I use hexadecimal notation for conciseness. you may use b'010....000' if you want 
insert into t values (0xFFFFFFFF,
                      0xFFFF0000,
                      0xFF00FF00,
                      0xF0F0F0F0);

您可以轻松查询256位值：

mysql> select CONCAT(LPAD(HEX(a),8,'0'),
                     LPAD(HEX(b),8,'0'),
                     LPAD(HEX(c),8,'0'),
                     LPAD(HEX(d),8,'0')) from t;
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT(LPAD(HEX(a),8,'0'),
                     LPAD(HEX(b),8,'0'),
                     LPAD(HEX(c),8,'0'),
                     LPAD(HEX(d),8,'0')) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| FFFFFFFFFFFF0000FF00FF00F0F0F0F0                                                                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+

我在这里再次使用了十六进制，但是你可以通过将 ̀HEX() 替换为 BIN() 来显示二进制。

最后但并非最不重要的是，您可以对它们执行二进制操作。再次提醒，您只需“拆分”操作数即可。假设我想对表中的所有值应用 256 位掩码 0xFFFFFFFFFFFFFFFF0000000000000000：

update t set a = a & 0xFFFFFFFF, 
             b = b & 0xFFFFFFFF, 
             c = c & 0x00000000, 
             d = d & 0x00000000;

- Sylvain Leroux

---

直到MySQL实现Oracle风格的“RAW”数据类型，或者实现256位无符号的“REALLYBIGINT UNSIGNED”数据类型，整数类型是最好的选择。+1 - spencer7593

Answer 2

看起来blob可以通过以下位运算查询：

select id,bin(label & b'01000000010000001000000000000000000') from projects;

- rich