How to bitwise OR into a binary(100)?

I have a binary(100), and I want to bitwise OR just one of its bytes with a constant.

Any idea how this would be done开发者_开发知识库?

Alternatively, how can I store a value into a byte of a binary(100)?

Firstly, consider whether BINARY is actually the appropriate field type. When compared to BLOB it has a potentially nasty "feature" of stripping trailing spaces. BINARY is really designed to be just a case-insenstive binary text string, and not a blob of arbitrary binary data.

If you do use a blob, you'd need to use the SUBSTRING() operator combined with ASCII() to extract just the byte you want, then use the | bitwise operator.

To set something in the second byte you'd need to use something like:

UPDATE TABLE SET col = CONCAT(
   SUBSTR(col, 1, 1),
   CHAR(ASCII(SUBSTR(col, 2, 1) | 0x80)),
   SUBSTR(col, 3)
)

A possibly simpler solution might be to treat your 100 bytes as 12.5 lots of 64 bits (i.e. BIGINT), and then use direct bitwise operations on individual words.