12

Given that bit fields are just binary representations of data and need to be queried in slightly "strange" way.

Does it actually provide any benefit using a bit field for a boolean value? From what I can see it seems to suggest that space is the only real benefit.

13

Personally, I would use the BOOLEAN for a boolean value. But keep in mind the caveat that in MySQL, BOOLEAN is just a synonym of TINYINT(1) [src].

This, of course, means you can have values other than 0 or 1 if you are not careful. To avoid this, you can use the aliases TRUE and FALSE when inserting and updating data, as those map to 1 and 0 respectively.

For the sake of being complete, prior to 5.0.3, BIT was also a synonym of TINYINT(1).

1

If I understand it correctly, BOOLEAN always uses 1 byte per column but BIT(n) will use as few bytes that are needed to hold the given number of bits.

So BIT may save some space, but BOOLEAN is easier to work with if you need to query them with SQL.

If I had a set of flags that were understood by an application, and stored in a single field in the application, then I would be looking to use BIT(n). However if I had columns that needed to be queried from SQL, I would be using BOOLEAN.

An example would be an application that used flags to record what weeks an event should run on, with a bit per week, being 1 if the event runs on the given week. If the data was going to be made easy to query in a database, it would be “normalized” across two tables, but if the data only needed to be stored in the database, then it is best kept in the form the application uses it in.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.