Menu
I often have tables where I need to store a flag which can either be 1 or 0 (true or false, etc).
I've previously used TINYINT.
Should I instead use BIT(1)? Why or why not?
610
TINYINT is a very small integer. The minimum and maximum SIGNED values are -128 and 127 respectively, while for UNSIGNED values TINYINT range is from 0 to 255. TINYINT uses 1 byte per row. It is the best option when you want to save space on your disk and enhance performance.
A BIT data type is used to store the value of 1 bit that can be 0 or 1. It stores the value in the range of 1 to 64. If we try to insert other values (for example, 2) inside the BIT column, MySQL issues an error.
A TINYINT is an 8-bit integer value, a BIT field can store between 1 bit, BIT(1), and 64 bits, BIT(64). For a boolean values, BIT(1) is pretty common. what's the difference between a TINYINT and a BIT(8) ? TINYINT can be signed or unsigned and relate to negative numbers.
The TINYINT takes 1 byte that means it has range -128 to +127 while int takes 4 bytes; it has range -2147483648 to +2147483647.
if you use a mysql version greater then 5.0.3 Bit isn't anymore an alias for Tinyint but if you create a bit column it gets anyway 1 Byte.
so use Bit(1) or Tinyint(1) is equal and you get no benefits if your table had only 1 Bit column.
but if you had more true/false columns i suggest you to use Bit as each value of the bit columns are placed in the same 1 Byte until it is filled.
if u use mysql lower then 5.0.3 then use tinyint or bit is totally fine. if you look at the mysql documentation on bool types you see that it is a alias for tinyint
http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html
BOOL, BOOLEAN
These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:
BIT is a synonym for TINYINT(1).
183
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
