MySQL Tidbits: The XOR Toggle

Jul11
Missing Image
By Joe Izenman

A web app full of data is often going to be full of tables (at least on the administration end). A listing of users here, all the recently created events there. Often for readability we will alternate rows between two slightly different background colors.

This can be handled on the PHP end with a bit of math and a counter, like so:

$count = 0;
while( $row = mysql_fetch_assoc($res) ) {
    if( $count % 2 == 0 )
        $background_color = 'white';
    else
        $background_color = 'gray';

    /* OUTPUT TABLE CONTENTS WITH GIVEN BACKGROUND */

    $count++;
}


However, it can also be done entirely in-database, via creative use of the XOR operator:

mysql> SET @toggle = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id,
    ->        IF( (@t := @t XOR 1),
    ->            'gray',
    ->            'white'
    ->        ) AS background
    -> FROM pg;
+----+------------+
| id | background |
+----+------------+
|  1 | gray       |
|  2 | white      |
|  3 | gray       |
|  4 | white      |
|  5 | gray       |
|  6 | white      |
|  7 | gray       |
|  8 | white      |
| 11 | gray       |
+----+------------+
9 rows in set (0.00 sec)


Since XOR (the boolean exclusive or operator) means that an expression evaluates as true when one and only one side of the operation is true, 0 XOR 1 will evaluate to 1 and 1 XOR 1 will evaluate to 0. Repeatedly XORing the same value (the @t variable) creates a toggle, flipping back and forth between zero and 1. The IF statement then translates those numbers into the appropriate colors, and presto! The color is now ready and waiting as you retrieve each row.

Other Uses

This is a pretty trivial difference, at least on such small listing tables. However, the XOR toggle construct can also be useful for updates. Say you have a field that you need toggled between true and false with an update statement.



mysql> SELECT * FROM toggler;
+----+--------+
| id | toggle |
+----+--------+
|  1 |      0 |
|  2 |      1 |
+----+--------+
2 rows in set (0.00 sec)

mysql> UPDATE toggler SET toggle = toggle XOR 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM toggler;
+----+--------+
| id | toggle |
+----+--------+
|  1 |      1 |
|  2 |      0 |
+----+--------+
2 rows in set (0.00 sec)


By updating the toggle field to an XOR 1'ed version of itself, all 1's become 0's and 0's become 1's.


Dev

Back To Feed