Page 1 of 1

Most simple MySQL data type

Posted: Wed Jul 16, 2008 8:07 pm
by JellyFish
What is the most simple 0 or 1, true or false, on or off data type for mysql? What I mean by simple is space efficiency.

Re: Most simple MySQL data type

Posted: Wed Jul 16, 2008 10:34 pm
by s.dot
I'd say INT (1)
Perhaps TINYINT, though I just use INT.

Re: Most simple MySQL data type

Posted: Wed Jul 16, 2008 10:59 pm
by Benjamin
Technically there isn't a way to have an actual bool field in MySQL. The smallest is TINYINT which has a range of –128 to 127 (signed) and 0 to 255 (unsigned). If you've got quite a few boolean values you need to store it's very efficient to place them into a bitmap.

Code: Select all

 
TYPE         STORAGE      SUPPORTED BOOLS
TINYINT      1 byte       8
SMALLINT     2 bytes      16
MEDIUMINT    3 bytes      24
INT,INTEGER  4 bytes      32
BIGINT       8 bytes      64
 

Re: Most simple MySQL data type

Posted: Thu Jul 17, 2008 3:47 pm
by JellyFish
Thanks guys. I'm using TINYINT(1) at the moment, but does having a limit of 1 use less space on my database?

Oddly even though I have a limit to 1 I can place a 2 in the field, even a 20! Strange...

Re: Most simple MySQL data type

Posted: Thu Jul 17, 2008 3:48 pm
by Benjamin
JellyFish wrote:Thanks guys. I'm using TINYINT(1) at the moment, but does having a limit of 1 use less space on my database?

Oddly even though I have a limit to 1 I can place a 2 in the field, even a 20! Strange...
It doesn't have any effect on what the field can actually store.