Can I save 7 booleans in 1 column?

Right now I have 7 columns in my MySQL table:

monday
tuesday
wednesday
..

They are all booleans. Is there a way to combine these booleans in a single days column, by using a bitset/bitmap or something?

I also want to be able to query the rows that have a spec开发者_如何学Cific day enabled. The query I'm using now is:

SELECT id FROM rows WHERE thursday = 1

Multiple days can be true, but I only need to query for 1 day at a time.

Is there any reason you want to store them all in one column?

There are ways, but there are going to be issues down the road for you if you go this route. For instance, if you want to see which rows are active during "thursday", you'll have to look at every single row, run some function to "de-muck-up" your data, and then parse your results.

If it's only 7 booleans, why not just make a column that can store from 0-2^8 and assign each day a bit?

Yes, this is possible, but you loose normalization and certain queries will be very difficult to write.

For example - if you wanted to know how many occurrences of each day appear in the table (say how many Tuesdays), this design would make it quite difficult and inefficient.

You are better off with 7 bit fields in the table.

Let me give you the benefit of my experience. I had a similar need to store a bitmask of application rights.

It is pretty simple to implement, you just make the field a standard integer type and AND with the relevant bitmask to check if a particular item is set, and use OR to set a particular item.

That said, I regret doing it now. It actually makes your code more complex, and I learned the hard way that you won't really get much meaningful use out of indexes on that column resulting in poor query performance. Take my advice and just do it as 7 separate bit/boolean fields, especially given that they aren't inventing new weekdays any time soon so the flexibility of using a bitmask is a waste anyway. The clever bitmask solution doesn't pay off.

you could use 1 byte to store your 7 bits and have 1 bit left over, however it is highly advisable to just use different column for each bit

Set datatype of MySQL will be good. Storage used for it is bitmap.

I think @Mike M is right on here. so, for example, you have a table that might look like this:

monday | tuesday | wednesday | thursday | friday | saturday | sunday

true   | false   | false     | true     |  false |  false   | true

this could be respresented in one column as:

 | days |
 --------
  1001001

I'm not sure how much this really gets you, you'll just have to parse the days column to see which days are active.

I think that you can you serialization here. Take a look at that please :

http://www.devshed.com/c/a/PHP/Working-with-MySQL-and-Sessions-to-Serialize-Objects-in-PHP/1/