Most simple MySQL data type

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Most simple MySQL data type

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Most simple MySQL data type

Post by s.dot »

I'd say INT (1)
Perhaps TINYINT, though I just use INT.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Most simple MySQL data type

Post 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
 
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: Most simple MySQL data type

Post 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...
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Most simple MySQL data type

Post 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.
Post Reply