PDA

View Full Version : Bit math

Keleth
08-14-2011, 11:12 PM
I've tried googling this with insufficient results, so I'm hoping I can get some advice on it.

What I want to store, logically, is data representing if a series of items is visible or not. So I thought, I can store a bit string, where 0 is not visible, 1 is visible. So if I had 4 items, the default would be '0000'. if Item 2 became visible, then its '0010'. To store this, the best column type would be bit, correct?

And though my interface, I'm getting the signal to flip a bit. So, if my binary math is correct, the best way would be to NOR against a mask where the position I want to flip is 1, eg, to flip the third position, NOR against 0100.

I'm having trouble with that particular step. First, I can't find a NOR function, so I tried to invert the final value of an or as such: ~(0100 | 1010), gave me a near max 64 bit int value. I'm assuming its the dec value of the binary result, but given I'm only interested (in this case), the final 4 characters, it seems too much to work with.

So, is there something I'm missing? Is there a simpler technique I should look into? Should I be storing the data differently?

Old Pedant
08-15-2011, 09:06 PM
No, XOR, not NOR.

But...

But MySQL will *NOT* do bit-wise operations on single bits using the XOR logical operator. That operator is ONLY for use with boolean values.

Instead, you must use the ^ operator, the same operator used for XOR in JavaScript (and, I think, in PHP).

Examples:

mysql> select id, flags from zap;
+------+-------+
| id | flags |
+------+-------+
| 1 | 3 | 0011
| 2 | 7 | 0111
| 3 | 8 | 1000
+------+-------+
3 rows in set (0.00 sec)

mysql> select id, flags ^ 1 from zap;
+------+-----------+
| id | flags ^ 1 |
+------+-----------+
| 1 | 2 | 0010
| 2 | 6 | 0110
| 3 | 9 | 1001
+------+-----------+
3 rows in set (0.00 sec)

mysql> update zap set flags = flags ^ 8 where id in (2,3);
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select id, flags from zap;
+------+-------+
| id | flags |
+------+-------+
| 1 | 3 | 0011 [unchanged, because not IN(2,3) ]
| 2 | 15 | 1111 [ was 0111, 8-bit flipped ]
| 3 | 0 | 0000 [ was 1000, 8-bit flipped ]
+------+-------+
3 rows in set (0.00 sec)

Okay?

http://dev.mysql.com/doc/refman/5.5/en/non-typed-operators.html

See operators & | ^ ~

Keleth
08-16-2011, 02:50 AM
Ah, ok, so its using the dec representation of the binary number. I was trying to see if there was a way to work directly with the binary (101 instead of 5), but ok, yah, if its through the dec, that's doable too.

Old Pedant
08-16-2011, 03:00 AM
Oh, you can use binary!

I just don't bother for numbers up to 255 decimal. I can convert in my head faster than I can write the bits.

Look here:
http://dev.mysql.com/doc/refman/5.5/en/bit-field-values.html

mysql> select id, BIN(flags+0) from zap;
+------+--------------+
| id | BIN(flags+0) |
+------+--------------+
| 1 | 11 |
| 2 | 1111 |
| 3 | 0 |
+------+--------------+
3 rows in set (0.08 sec)

mysql> update zap set flags = flags ^ 0b1001;
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> select id, BIN(flags+0) from zap;
+------+--------------+
| id | BIN(flags+0) |
+------+--------------+
| 1 | 1010 |
| 2 | 110 |
| 3 | 1001 |
+------+--------------+
3 rows in set (0.00 sec)
(I'm not sure why they tell you to add 0 to the value in the BIN() function call. It seems to work just fine without it. But...)