Storing a binary array in MySQL

Question

I have an array of values called A, B... X, Y, Z. Fun though it would be to have 26 columns in the table I can't help but feel there is a better way. I have considered creating a second table with the id value of row from the first table, the id of the item in the array and then the boolean value but it seems clunky and confusing.

Is there a better way?

Answer 1

Short answer, no. Long answer, it depends.

You can store binary data in a bunch of ways - abusing a number, using a BINARY OR VARBINARY, using a BLOB or TINYBLOB, etc. BINARY types will generally be faster than BLOB types, provided your data is a known size.

However, relational databases aren't designed for doing anything intelligent with binary data. On a project I used to work on, there was a table where each record had as specific binary pattern - stored as some sort of integer - and searching required a lot of ANDs, ORs, XORs and NOTs. It never really worked very well, performance sucked, and it held the whole project down. Looking back, I would have taken a completely different approach.

So if you just want to drop the data in and pull it out again, great. If you want to use it for anything intelligent, tough.

The situation may be different on other database vendors. In fact, have you considered using something else in place of the database? Some sort of object persistence?

Answer 2

Are your possible array values static?

If so, try using MySQL's SET data type.