MySQL bitwise AND 256-bit binary values

Question

I'm intending on storing a 256-bit long binary value in a MySQL table column.

Which column type should I be using (blob?) such that I can run bitwise operations against it (example of an AND would be ideal).

Answer 1

I don't think you could find some way to perform bit-wise operation on 256-bit values at SQL level as the doc clearly state that:

MySQL uses BIGINT (64-bit) arithmetic for bit operations, so these operators have a maximum range of 64 bits.

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

---

As for storing those values, TINYBLOB is possible, but my personal preference would go to simply BINARY(32) (a binary string of 32 bytes -- 256-bits).

---

---

While writing this, one trick came to my mind. If we are limited to 64-bit values (BIGINT UNSIGNED), why not store your 256-bit as 4 words of 64-bits. Not very elegant but that would work. Especially here since you only need bitwise operations:

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

Very basically:

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

While inserting, 256-bit values has to be "split" on 4 words:

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

You could easily query the 256-bit value:

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                                                                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+

I used hexadecimal here again, but you could display as binary by replacing ̀HEX() by BIN()

And last but not least you could perform binary operation on them. Once again, you just have to "split" the operand. Assuming I want to apply the 256 bits mask 0xFFFFFFFFFFFFFFFF0000000000000000 to all values in the table:

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