Binary string search on one field.

Question

I have 300 boolean fields in one table, and im trying to do somithing like that:

One string field:

10000010000100100100100100010001

Ha a simple way to do a simple search os this field like:

select * from table where field  xor "10000010000100100100000000010001"

Im tring this but is to long:

select * from teste where mid(info,2,1) and mid(info,3,1)

:) Help!!

Answer 1

A citation from the book High Performance MySQL:

If you used an integer, you could write that example as follows:

mysql> SET @CAN_READ   := 1 << 0,
    -> @CAN_WRITE  := 1 << 1,
    -> @CAN_DELETE := 1 << 2;
mysql> CREATE TABLE acl (
    -> perms TINYINT UNSIGNED NOT NULL DEFAULT 0
    -> );
mysql> INSERT INTO acl(perms) VALUES(@CAN_READ + @CAN_DELETE);
mysql> SELECT perms FROM acl WHERE perms & @CAN_READ;
+-------+
| perms |
+-------+
|   5   |
+-------+

UPD:

A possible solution in your case if all the strings are of the same length (I'll be surprised if they are not):

select * from teste where info like '_______00001001001001001___1___1';

Answer 2

Applying bitmasking to other types than ints, like strings in your case, is possible if you write your own external function and do the comparison in there, but this is somewhat hardcore stuff if you are not familiar programming with C. MySQL itself only provides bitmask operators for the int type it uses internally.

Edit: Or use the 'LIKE' solution provided by newtower

See other similar topics: http://stackoverflow.com/questions/177054/is-there-a-practical-limit-to-the-size-of-bit-masks

While efficient in space and possibly speed, this approach to solve the problem has same disadvantages as having a table with 300 columns. A table with so many columns is very inflexible and adding and removing values requires altering the structure of the table, rather than the data.

While compressing it into one field might seem to solve the problem, it actually makes the data contain the same structure in even more inflexible format, because you are removing the column:data semantics and solely relying on positional data. Making changes to this kind of data storage format will eventually be very time intensive and error prone to code.

It seems to me that you would do better if you inverted your problem and made a table that contains your booleans as rows, instead of having lots of columns. This is often the case. :)

Go with the table structure presented by JYelton instead, or something similar, if possible.

Answer 3

The best way to handle this, if you can, would be to create another table that would link to the existing one as a many-to-one and you could then use a select statement to find all records in the subtable matching the ID of your parent table. In this example, the new table would be named info (after the column) and the prior table is named parent:

SELECT parent.*
FROM info
INNER JOIN parent
ON parent.id = info.parent_id
WHERE info.data IN ( 2, 3 ) // see note 1
GROUP BY parent.id
HAVING COUNT(*) = 2 // see note 2

Note 1: The positions within the string are now ints stored in the new table, in the column 'data'.

Note 2: You will need to specify the number of values specified above.

Thoughts: This query does not eliminate parent records where additional values match in the data table.