How can I store an array of boolean values in a MySql database?

Question

In my case, every "item" either has a property , or not. The properties can be some hundreds, so I will need , say, max 1000 true/false bits per item.

Is there a way to store those bits in one field of the item ?

Answer 1

If you're looking for a way to do this in a way that's searchable, then no.

A couple searchable methods (involving more than 1 column and/or table):

• Use a bunch of SET columns. You're limited to 64 items (on/offs) in a set, but you cna probably figure out a way to group them.
• Use 3 tables: Items (id, ...), FlagNames(id, name), and a pivot table ItemFlags(item_id, flag_id). You can then query for items with joins.

If you don't need it to be searchable, then all you need is a method to serialize your data before you put it in the database, and a unserialize it when you pull it out, then use a char, or varchar column.

• Use facilities built in to your language (PHP's serialize/unserialize).
• Concatenate a series of "y" and "n" characters together.
• Bit-pack your values into a string (8 bits per character) in the client before making a call to the MySQL database, and unpack them when retrieving data out of the database. This is the most efficient storage mechanism (if all rows are the same, use char[x], not varchar[x]) at the expense of the data not being searchable and slightly more complicated code.

Answer 2

I would rather go with something like:

Properties
ID, Property
1, FirsProperty
2, SecondProperty

ItemProperties
ID, Property, Item
1021, 1, 10
1022, 2, 10

Then it would be easy to retrieve which properties are set or not with a query for any particular item.