Since the theme of my week appears to be database interactions (having looked at index structure query-performance on “membership” tables and prepared statements in the JDBC driver), I figured I would end the week on more database-related “hot take”: I no longer use BIT columns to store “Boolean” data in MySQL. Instead, I use a TINYINT column.

CAUTION:_ There is no “right” answer on this topic - this is just my opinion based on my experience and the choices that have come back to haunt me / make my database interactions more challenging._

Semantically speaking, a BIT field is no more clear or meaningful than a TINYINT field when it comes to storing Boolean data. Because, at least in MySQL, a **BIT** field isn’t a true-false data type - it’s a binary data type. A BIT field contains “bits”: N-number of bits, between 1 and 64. The only reason that it can represent a true-false value within an application is because the developers working on that application have all agreed to treat it as such.

A **TINYINT** field is also not a true-false data type - it’s a numeric data type. And, like the BIT field, the only reason that it can represent a true-false value within an application is because the developers working on that application have all agreed to treat it as such.

So, semantically speaking, neither BIT nor TINYINT are “Booleans”; but, they can both be overloaded to represent a Boolean value if, and only if, the developers working on that application all agree on said social contract.

Now, storage-wise, there’s also no difference between a BIT(1) and a TINYINT because a [BIT](https://dev.mysql.com/doc/refman/5.6/en/bit-type.html) field is actually stored in bytes. Meaning, in order to store a single bit, you have to store 8-bits. Which is also the amount of storage required by a [TINYINT](https://dev.mysql.com/doc/refman/5.6/en/integer-types.html).

So, from a physical stand-point, you’re not “saving space” by using a BIT instead of a TINYINT when attempting to represent a Boolean value.

To recap:

• Neither BIT nor TINYINT is a “Boolean” value. We can only treat them as Boolean values when our developers agree to treat them as Boolean values.

• Both BIT(1) and TINYINT require a byte of storage.

Since there’s no semantically meaningful difference between the two data-types when it comes to representing Boolean values, neither data-type is “more correct”. But, there are downsides to using a binary field in order to represent a true-false value:

• The MySQL driver for Node.js returns [BIT](https://www.bennadel.com/blog/3188-casting-bit-fields-to-booleans-using-the-node-js-mysql-driver.htm) data as a [Buffer](https://www.bennadel.com/blog/3188-casting-bit-fields-to-booleans-using-the-node-js-mysql-driver.htm); because, of course it would. After all, a BIT column represents binary data, which is what the Buffer represents in Node.js. As such, in order to translate a BIT(1) result into a true / false data-type, you have add special type-casing logic to your database client configuration.

• The MySQL driver for Java returns binary data when [BIT](https://www.bennadel.com/blog/2871-using-bit-values-in-coalesce-in-mysql-results-in-binary-values.htm) is used in a [COALESCE()](https://www.bennadel.com/blog/2871-using-bit-values-in-coalesce-in-mysql-results-in-binary-values.htm) call. As such, if you are performing a LEFT OUTER JOIN on a BIT field and attempt to provide a default value with COALESCE(), you have to CAST() the resultant value back to an UNSIGNED type in order to use the value as a Truthy within your application code.

The reason I have articles written on the above two issues is because I’ve personally run into these two issues and wasted hours trying to understand the issue and come up with a work-around.

On the other hand, I have never personally run into an issue when using a TINYINT column to represent a “Boolean” value.

With all that said, I will conceit that MySQL will allow you to store 2 in a TINYINT field; but, will prevent you from storing 2 in a BIT(1) field because a 2 would require more bits than are defined on the column-type. As such, the BIT(1) type does place more constraints on the stored value.

That said, in my ColdFusion applications, if I’m persisting a true / false value, I’m generally not using numeric types in the code - I’m using Boolean types that get persisted as TINYINT types at write-time (using CFQueryParam):

#coldfusion #sql #mysql