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

At worst you would have to use a char(1000) [ynnynynnynynnynny...] or the like. If you're willing to pack it (for example, into hex isn't too bad) you could do it with a char(64) [hexadecimal chars].

If it is less than 64, then the SET type will work, but it seems like that's not enough.

You could use a binary type, but that's designed more for stuff like movies, etc.. so I'd not.

So yeah, it seems like your best bet is to pack it into a string, and then store that.

It should be noted that a VARCHAR would be wasting space, since you do know precisely how much space your data will take, and can allocate it exactly. (Having fixed-width rows is a good thing)

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.

Answer 3

Strictly speaking you can accomplish this using the following:

$bools = array(0,1,1,0,1,0,0,1);
$for_db = serialize($array);

// Insert the serialized $for_db string into the database. You could use a text type
// make certain it could hold the entire string.
// To get it back out:

$bools = unserialize($from_db);

That said, I would strongly recommend looking at alternative solutions.

Depending on the use case you might try creating an "item" table that has a many-to-many relationship with values from an "attributes" table. This would be a standard implementation of the common Entity Attribute Value database design pattern for storing variable points of data about a common set of objects.

Answer 4

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.