How do I compare (AND) two strings which represent a binary mask in mysql?

Question

I have a table in mysql which stores (among other columns) a bitmask as a string, for example:

000100

I'd like to perform a query which will AND these fields together to provide a result that shows when any two mask positions are both true.

As an example, consider these three sample records:

id  name  mask
==  ====  ====
11  a     000100
12  a     000110
13  a     010000

This query is a self-join. I am looking for records where, for a given name, the same bitmask occurs twice or more.

In the above example, the only records that match this condition are 11 and 12 (the fourth bit is 1 in both cases).

The problem I am having is performing the AND on the mask. Since it is stored as a string, I am unsure how to get mysql to treat it as a binary value.

Answer 1

You can use conv, eg.

select conv('1100', 2, 10) & conv('0110', 2, 10);

Re comment, it seems to work for me:

mysql> select conv('1001', 2, 10) & conv('0110', 2, 10) = 0;
+-----------------------------------------------+
| conv('1001', 2, 10) & conv('0110', 2, 10) = 0 |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select conv('1001', 2, 10) & conv('0111', 2, 10) = 0;
+-----------------------------------------------+
| conv('1001', 2, 10) & conv('0111', 2, 10) = 0 |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+
1 row in set (0.00 sec)