CODEWITHSUNDEEP
mysqlsql
user1032531
user1032531

Reputation: 26281

BIT(1) or TINYINT for flags in MySQL

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?

Upvotes: 10

Views: 9210

Answers (1)

ins0
ins0

Reputation: 3928

If you use a MySQL version greater than 5.0.3 BIT isn't anymore an alias for TINYINT, but if you create a BIT column it gets anyway 1 Byte.

So using 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 you use MySQL lower than 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(1).

BOOL, BOOLEAN

These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true.

BIT[ (M) ]

This data type was added in MySQL 5.0.3 for MyISAM, and extended in 5.0.5 to MEMORY, InnoDB, BDB, and NDBCLUSTER. Before 5.0.3, BIT is a synonym for TINYINT(1).

Upvotes: 13

Related Questions