Which MySQL data type to use for storing boolean values

Since MySQL doesn't seem to have any 'boolean' data type, which data type do you 'abuse' for storing true/false information in MySQL?

Especially in the context of writing and reading from/to a PHP script.

Over time I have used and seen several approaches:

- tinyint, varchar fields containing the values 0/1,
- varchar fields containing the strings '0'/'1' or 'true'/'false'
- and finally enum Fields containing the two options 'true'/'false'.

None of the above seems optimal. I tend to prefer the tinyint 0/1 variant, since automatic type conversion in PHP gives me boolean values rather simply.

So which data type do you use? Is there a type designed for boolean values which I have overlooked? Do you see any advantages/disadvantages by using one type or another?

I use TINYINT(1) in order to store boolean values in Mysql.

I don't know if there is any advantage to use this... But if i'm not wrong, mysql can store boolean (BOOL) and it store it as a tinyint(1)

[

[To see links please register here]

][1]


[1]:

[To see links please register here]

If you use the BOOLEAN type, this is aliased to TINYINT(1). This is best if you want to use standardised SQL and don't mind that the field could contain an out of range value (basically anything that isn't 0 will be 'true').

ENUM('False', 'True') will let you use the strings in your SQL, and MySQL will store the field internally as an integer where 'False'=0 and 'True'=1 based on the order the Enum is specified.

In MySQL 5+ you can use a BIT(1) field to indicate a 1-bit numeric type. I don't believe this actually uses any less space in the storage but again allows you to constrain the possible values to 1 or 0.

All of the above will use approximately the same amount of storage, so it's best to pick the one you find easiest to work with.

Bit is only advantageous over the various byte options (tinyint, enum, char(1)) if you have a lot of boolean fields. One bit field still takes up a full byte. Two bit fields fit into that same byte. Three, four,five, six, seven, eight. After which they start filling up the next byte. Ultimately the savings are so small, there are thousands of other optimizations you should focus on. Unless you're dealing with an enormous amount of data, those few bytes aren't going to add up to much. If you're using bit with PHP you need to typecast the values going in and out.

Referring to this link
[Boolean datatype in Mysql][1], according to the application usage, if one wants only 0 or 1 to be stored, bit(1) is the better choice.


[1]:

[To see links please register here]

This is an elegant solution that I quite appreciate because it uses zero data bytes:

some_flag CHAR(0) DEFAULT NULL

To set it to true, set `some_flag = ''` and to set it to false, set `some_flag = NULL`.

Then to test for true, check if some_flag `IS NOT NULL`, and to test for false, check if some_flag `IS NULL`.

(This method is described in "High Performance MySQL: Optimization, Backups, Replication, and More" by Jon Warren Lentz, Baron Schwartz and Arjen Lentz.)

I got fed up with trying to get zeroes, NULLS, and '' accurately round a loop of PHP, MySql and POST values, so I just use 'Yes' and 'No'.

This works flawlessly and needs no special treatment that isn't obvious and easy to do.

Until MySQL implements a bit datatype, if your processing is truly pressed for space and/or time, such as with high volume transactions, create a TINYINT field called `bit_flags` for all your boolean variables, and mask and shift the boolean bit you desire in your SQL query.

For instance, if your left-most bit represents your bool field, and the 7 rightmost bits represent nothing, then your `bit_flags` field will equal 128 (binary 10000000). Mask (hide) the seven rightmost bits (using the bitwise operator `&`), and shift the 8th bit seven spaces to the right, ending up with 00000001. Now the entire number (which, in this case, is 1) is your value.

SELECT (t.bit_flags & 128) >> 7 AS myBool FROM myTable t;

if bit_flags = 128 ==> 1 (true)
if bit_flags = 0 ==> 0 (false)

You can run statements like these as you test

SELECT (128 & 128) >> 7;

SELECT (0 & 128) >> 7;

etc.

Since you have 8 bits, you have potentially 8 boolean variables from one byte. Some future programmer will invariably use the next seven bits, so you *must* mask. Don’t just shift, or you will create hell for yourself and others in the future. Make sure you have MySQL do your masking and shifting — this will be significantly faster than having the web-scripting language (PHP, ASP, etc.) do it. Also, make sure that you place a comment in the MySQL comment field for your `bit_flags` field.

You’ll find these sites useful when implementing this method:

* [MySQL — Bit Functions and Operators](

[To see links please register here]

)
* [Decimal/Binary Conversion Tool](

[To see links please register here]

)

After reading the answers here I decided to use `bit(1)` and yes, it is somehow better in space/time, **BUT** after a while I changed my mind and I will never use it again. It complicated my development a lot, when using prepared statements, libraries etc (php).

Since then, I always use `tinyint(1)`, seems good enough.

Since MySQL (8.0.16) and MariaDB (10.2.1) both implemented the CHECK constraint, I would now use

bool_val TINYINT CHECK(bool_val IN(0,1))

You will only be able to store `0`, `1` or `NULL`, as well as values which can be converted to `0` or `1` without errors like `'1'`, `0x00`, `b'1'` or `TRUE`/`FALSE`.

If you don't want to permit NULLs, add the `NOT NULL` option

bool_val TINYINT NOT NULL CHECK(bool_val IN(0,1))

Note that there is virtually no difference if you use `TINYINT`, `TINYINT(1)` or `TINYINT(123)`.

If you want your schema to be upwards compatible, you can also use `BOOL` or `BOOLEAN`

bool_val BOOL CHECK(bool_val IN(TRUE,FALSE))

[db<>fiddle demo](

[To see links please register here]

)