Jump to content


Photo

Storing a boolean


  • Please log in to reply
5 replies to this topic

#1 Crashthatch

Crashthatch
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 03 June 2006 - 05:05 PM

I need to store a true/false 1/0 value in a mysql database.
Originally, I always used a tinyint field type (1 or 0).
Next I discovered Enum and, thinking it would only take 1 bit rather than 8, switched to using that. (smaller files, faster search time).
I recently read that Enum takes a minimum of 1 byte per record, so there's basically no advantage to either method.

Is there a preferred way of storing boolean values in mysql tables? What is it, and why is that method better?

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 June 2006 - 06:11 PM

Well, there's a BIT field, too -- the question is whether or not this flag is really necessary when a ENUM might be better; you'll need to explain what it's used for.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Crashthatch

Crashthatch
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 03 June 2006 - 08:23 PM

One example is in a game I'm writing. Vehicles can either be in the air (eg. a plane) or on the ground (eg. a tank). I use a field "inair" (1 or 0) to determine whether the vehicle is currently airborne. This is then read any time anyone looks at the map.

There are other places too, (building under construction or fully built? weapon has bombard ability?) but I don't see how any of them are any different.

I'll have a look at the bit field.

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 June 2006 - 10:44 PM

My point was that storing "inair" true/false means you'll need more fields if you need another piece of information as well (e.g. inair vs onground vs something else). Storing a ENUM of air/ground/etc. would be more useful. Same goes for other fields. In my experience, BIT fields are never as useful as they sound, because they're not storing raw information, but rather a very simplistic semantic representation -- which is what the application layer is for.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 Crashthatch

Crashthatch
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 05 June 2006 - 12:19 AM

I see what you mean. In this case it is just 2 values, but I'll bear it in mind for the future. Thanks for the help.

#6 Wildbug

Wildbug
  • Members
  • PipPipPip
  • Advanced Member
  • 1,149 posts

Posted 07 June 2006 - 07:55 PM

[!--quoteo(post=379660:date=Jun 3 2006, 01:05 PM:name=Crashthatch)--][div class=\'quotetop\']QUOTE(Crashthatch @ Jun 3 2006, 01:05 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I need to store a true/false 1/0 value in a mysql database....
[/quote]

There's also the CHAR(0). It stores either the NULL value or the empty string (''). You can easily check the value in PHP with something like is_string($row[0]) or whatever.
Twice a day my clock works PERFECTLY!  I can't figure out what's wrong with it.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users