blob - MySQL bitwise AND 256-bit binary values -

i'm intending on storing 256-bit long binary value in mysql table column.

which column type should using (blob?) such can run bitwise operations against (example of , ideal).

i don't think find way perform bit-wise operation on 256-bit values @ sql level doc state that:

mysql uses bigint (64-bit) arithmetic bit operations, these operators have maximum range of 64 bits.

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

---

as storing values, tinyblob possible, personal preference go binary(32) (a binary string of 32 bytes -- 256-bits).

---

---

while writing this, 1 trick came mind. if limited 64-bit values (bigint unsigned), why not store 256-bit 4 words of 64-bits. not elegant work. here since 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 "split" on 4 words:

-- here use hexadecimal notation conciseness. may use b'010....000' if want  insert t values (0xffffffff,                       0xffff0000,                       0xff00ff00,                       0xf0f0f0f0); 

you query 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')) 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, display binary replacing ̀hex() bin()

and last not least perform binary operation on them. once again, have "split" operand. assuming want apply 256 bits mask 0xffffffffffffffff0000000000000000 values in table:

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