Search code examples
mysqlcomparisonbit-manipulation

MySQL bitwise comparison

I have mysql with a user table with answers from a poll saved as a bitwise. How do I find the user with most or least common answers with the reference bitwise?

+------+---------+--+
| User | Answers |  |
+------+---------+--+
| A    |       1 |  |
| B    |       5 |  |
| C    |      10 |  |
+------+---------+--+
Solution

  • Assuming by 'reference bitwise' you mean that you have another value that is a bitmask that you are trying to match against the Answers column, something like this should do it for you. In this case, I'm using '4' as the reference bitmask and myTable as the name of your table..

    SELECT User, BIT_COUNT(Answers & 4) AS MatchedBits FROM myTable ORDER BY MatchedBits DESC

    This returns:

    +------+-------------+
| User | MatchedBits |
+------+-------------+
| B    |           1 |
| A    |           0 |
| C    |           0 |
+------+-------------+

    You can also add a LIMIT 1 clause to get just the top result, but of course that won't tell you if there is more than one top result with the same number of bits matched.