CODEWITHSUNDEEP
mysqlsqlxor
Dr Black
Dr Black

Reputation: 1

How do bit manipulators work in mySQL (with this example)

This is an example for bit manipulation. I do not understand how (id+1)^1-1 gives the shown output values. Please help.

Bit manipulation expression (id+1)^1-1 can calculate the new id after switch.

SELECT id, (id+1)^1-1, student FROM seat;


| id | (id+1)^1-1 | student |

|----|------------|---------|

| 1  | 2          | Abbot   |

| 2  | 1          | Doris   |

| 3  | 4          | Emerson |

| 4  | 3          | Green   |

| 5  | 6          | Jeames  |

Upvotes: 0

Views: 220

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37430

Let me present you how it is done:

first, ^ is XOR operation, so:

0^0 = 0
1^0 = 1
0^1 = 1
1^1 = 0

Note that 1 = 001 in binary form (I presented only relevant 3 bits to below explanation), so for example 2^1 = 010^001:

    010
XOR 001
-------
    011 = 3

id |  binary id | id + 1 | binary id + 1 | binary (id + 1) ^ 1 | decimal (id + 1) ^ 1 | (id + 1) ^ 1 - 1
1  |  001       | 2      | 010           | 011                 | 3                    | 2
2  |  010       | 3      | 011           | 010                 | 2                    | 1
3  |  011       | 4      | 100           | 101                 | 5                    | 4
4  |  100       | 5      | 101           | 100                 | 4                    | 3
5  |  101       | 6      | 110           | 111                 | 7                    | 6

Upvotes: 1

Solarflare
Solarflare

Reputation: 11106

^ is the bitwise XOR-operator. Bitwise x XOR 1 switches the last bit of x (1 xor 1 = 0, 0 xor 1 = 1), so it exchanges 0 and 1, 2 and 3 and so on.

So now you have a way to switch seats 0 and 1, 2 and 3, or, for this matter, seats 2 and 3, 3 and 4.

To use this to exchange 1 with 2, 3 and 4, you can first map 1, 2, 3, 4 to 2, 3, 4, 5, apply the XOR, then map the result back to 1, 2, 3, 4.

The function that maps 1, 2, 3, 4 to 2, 3, 4, 5 is f(x) = x+1. The reverse is g(x) = x-1.

So what you get is: h(x) = g( xor1( f(x) ) ). This is exactly your formula: apply ^1 to id+1, (id+1)^1, then undo the subsitution by applying x-1 to this result, and you get ((id+1)^1)-1.

Another substitution would have been to map 1, 2, 3, 4 to 0, 1, 2, 3 (with f(x) = x-1 and g(x) = x+1), giving you the final formula ((id-1)^1)+1.

Upvotes: 1

Related Questions