MySQL bitwise AND 256-bit binary values

bit-manipulation, blob, mysql

Solution

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:

ABCD32 & WXYZ32 == A8 & W8, B8 & X8, C8 & Y8, D8 & Z8

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;

Problem

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

Original source