Which MySQL data type to use for storing boolean values

Question

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?

Terribly

Anyone who is reading the old answers to this question needs to understand that MySQL added a bit datatype in version 5. Use that information as you may. dev.mysql.com/doc/refman/5.0/en/bit-type.html – Arriaga

First tinyint is not a varchar field as you implied above. Do you mean that bigint is a varchar field too? Second, I recommend tinyint for storing boolean fields no reasons though. – Hakim

Question related to Alternative to lots of booleans in MySQL? – Advice

for the current version of MYSQL Boolean type is available- dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html check this. according to that value zero considered as false – Loydloydie

depends on the case, for example, for a deleted flag on a message table, i use DATE NULL, so i can already use it to store the date it was deleted. – Studdard

bit(1)'s a bit** to import in Excel. Switching to tinyint(1) works. – Omni

now we have boolean after 5 years – Potpie

There are two feature requests for real (not tinyint alias) booleans, both of which are about 10 years old: bugs.mysql.com/bug.php?id=24530 and bugs.mysql.com/bug.php?id=24531. I couldn't find a ticket for MariaDB but you can check yourself here. – Earthenware

@tereško Based on the accepted answer to that question: No, not related. It could have been related, if anyone had responded to the last sentence "And maybe just have one column that would contain all of those options..." – Subside

I use SET '0', '1' in columns with prefix is_. For example is_uploaded. My object model knows then that it should evaluate it to boolean. – Tucket

Answer 1

For MySQL 5.0.3 and higher, you can use BIT. The manual says:

As of MySQL 5.0.3, the BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.

Otherwise, according to the MySQL manual you can use BOOL or BOOLEAN, which are at the moment aliases of tinyint(1):

Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

MySQL also states that:

We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.

References: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

Execrate

Yeah, I'd go for either this or, for a CHAR(1) and store 'Y'/'N' or 'T'/'F' etc. depending upon the context. The advantage of using a small integer type is that you get maximum portability across RDBMS-es – Pless

Going for char, in PHP at least, will lead to more code as !$boolean will never evaluate properly without further processing. – Frogmouth

The solution is $casts the column in the Laravel model as boolean like protected $casts = [ 'shipped' => 'boolean', 'refund' => 'boolean', ]; – Ghiberti

BIT is annoying in node.js + mysql because it comes back as a Buffer, which is always truthy. – Nora

Reference link is broken. – Elysium

Answer 2

BOOL and BOOLEAN are synonyms of TINYINT(1). Zero is false, anything else is true. More information here.

Linter

The (1) does nothing more than determine how the value is displayed, if you're conscious about storage size then you want to use BIT instead – Hydrophane

@JamesHalsall: Actually, BIT(1) and TINYINT(1) will both use one byte of storage. Up until MySQL 5.0.3, BIT was actually a synonym for TINYINT. Later versions of MySQL changed the implementation of BIT. But even with the implementation change, there's still no "storage size" benefit to the BIT datatype (at least with InnoDB and MyISAM; other storage engines e.g. NDB may have some storage optimization for multiple BIT column declarations.) The bigger issue is that some client libraries don't recognize or appropriately handle returned BIT datatype columns. A TINYINT works better. – Enterectomy

MySQL 5.0 manual clearly says a boolean value is either 1 or 0. The phrase "anything else is true" is not true. – Arleanarlee

@Walter: It actually is sort of true, the explanation is somewhat lacking. Briefly, in a boolean context, an expression can evaluate to NULL, FALSE or TRUE. In a MySQL statement, an expression evaluated in a boolean context is first evaluated as integer (decimal and float values are rounded, strings are converted in the usual quirky way MySQL converts strings to integer). A NULL is obviously NULL (neither TRUE nor FALSE). An integer value of 0 is handled as FALSE, and any other integer value (1, 2, -7, etc) evaluates to TRUE. For compatibility, we mimic that logic/handling of TINYINT boolean – Enterectomy

@Walter: This is easy to test, e.g SELECT 'foo' AS bar FROM dual WHERE -7. The expression -7 is evaluated in a boolean context, and the query returns a row. We can test with 0, or any expression that evaluates to integer value 0, and no row is returned. If the expression in the WHERE clause evaluates to any non-null integer value other than zero, the expression is TRUE. (I believe decimal and float values get "rounded" to integer, e.g. WHERE 1/3 evaluates to WHERE 0. We get the same result with WHERE 'foo', because string 'foo' also evaluates to integer value 0. – Enterectomy

And more examples with visuals: mysqltutorial.org/mysql-boolean – Bancroft

Answer 3

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.)

Diplococcus

fancy trick! this is helpful if working with MySQL <5 and perhaps even a lighter footprint than BIT, however in an effort to comply with convention and slightly less computation overhead (logic vs exact value) I would say BIT is the better way to go. – Hildebrandt

Might be 'fast', but it obfuscates the data such that any new developer would have no idea what the column stands for. – Bab

This is uses the same amount of bytes as BIT(1) – Java

Good luck getting an ORM to map nicely to this. – Kaka

I agree with @Richthofen, and find it hard to imagine a situation in which I would ever advocate using this solution. However, if it is to be used, then specifying as a COMMENT in the column's definition that NULL indicates false and '' indicates true might go some very small way towards aiding future understanding. – Vair

This is conceptually and practically a horrible solution. Don't know enough about its performance impact - why would I not assume a bit performs better? But you can't make it null (assuming you want "True" and "false"), and null requires special handling when comparing with other values, and that special handling may make the query not use an index. – Twickenham

Answer 4

This question has been answered but I figured I'd throw in my $0.02. I often use a CHAR(0), where '' == true and NULL == false.

From MySQL docs:

CHAR(0) is also quite nice when you need a column that can take only two values: A column that is defined as CHAR(0) NULL occupies only one bit and can take only the values NULL and '' (the empty string).

Agleam

mm, this seems like asking for trouble if you as me. I mean, depending on the language it may be too easy to not spot the diff between NULL and '' (for example PHP). – Pless

In terms of saving space (the number of bytes used to represent a boolean), this approach is a clear winner. This saves a byte over TINYINT. The downside (as some comments point out) is that some clients may have difficulty distinguishing between a NULL and an empty string. Even some relational databases (e.g. Oracle) don't distinguish between a zero-length string and a NULL. – Enterectomy

This is very clever! I used to write clever code, now I avoid it like the plague. I now want my code to have crystal clear intent, not just correct behaviour. My advice? Only do this if you want to confuse anyone who has to support the code/database. For instance, in PHP both '' and null are falsy values. – Eurydice

@CJDennis If you've abstracted your database layer behind the repository pattern, you don't have to worry about the obscurity of this solution. – Higher

Answer 5

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.

Cedell

Your remark concerning the ENUM is not true: try CAST(yourenumcol AS UNSIGNED) and you'll notice that False will be 1, and True will be 2. Another problem with ENUM is that it is too easy to insert '' (empty string) . I would disrecommend using this. – Pless

In my experience, using a BIT(1) field from PHP code was a bit troublesome. TINYINT(1) was a lot easier and produced more readable code. – Topdrawer

@Topdrawer - "using a BIT(1) field from PHP code was a bit troublesome"... no pun intended. :) But, yeah, I agree. I remember TINYINT(1) being easier, too... just can't remember why. Anyone else have thoughts on this? BIT(1) seems nicer on the surface because you can restrict to 0 or 1. I think BIT was sometimes interpreted as binary data (depending on the programming language and driver/library); whereas, TINYINT was treated more like a number. – Stickup

@Stickup - haha, it was really unintended, didn't notice that :) But indeed, if I remember correctly the bit field was interpreted as something binary, whereas the tinyint was easier to treat as a number and because of that, easier to use in a (boolean) expression. – Topdrawer

Answer 6

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)

http://dev.mysql.com/doc/refman/5.0/en/other-vendor-data-types.html

Spun

Answer 7

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.

Graticule

+1 for the typecasting comment. To add on to this when working with programming languages avoid using lazy programming techniques in favor of consistency. Use identical operators instead of just equals. In the case of PHP if( $var == "" ) will be true for 0, false, null, undefined, and "". to test all of the values it is often best to use if( true === empty( $var ) ) as it will avoid the undefined errors as well. You should also validate the data type you are working with if( is_int( $var ) && $var === 0 ) or typecast it to force it into being a specific data type (int) $var for the task. – Furred

@Graticule is this true for MySQL to the same extent it is true for MSSQL? I am migrating a new application that has not gone into production yet from MSSQL to MySQL. I'm not using PHP but rather converting C# over to Java 8. Given that Java is a strongly typed language I am not worried about type handling ... just all the bit flags that would move from one byte for up to 8 flags to 1 byte each flag given TINYINT(1). Do you know of any documentation on this topic for MySQL? – Aretino

@Graticule Doing some deeper research it is clear what the answer should be. Change does happen and we have seen improvements in this handling. Know your language that will be in the Application Layer / Data Access Layer and know your libraries support. I am using Java currently and BIT(1) is the recommended choice at this time for libraries like Hybernate and use of JDBC. Here is the URL [See table 5.2]: dev.mysql.com/doc/connector-j/en/… – Aretino

prove with a resource link that 8 bit(1) columns take the same byte... I've searched for a while and nowhere have I found it... – Selfdriven

i think this is actually not true in practice (isam) – Tylertylosis

Answer 8

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
• Decimal/Binary Conversion Tool

Big

This seems just like a dreadful way to obfuscate intention to future programmers. Sure seems like a lot of trouble to save 7 bytes (assuming you are using all 8 bools in that single table!) – Rhythm

@Rhythm there's no obfuscation at all! Write documentation and MySQL comments explaining each field in the table (as the answer mentions)! The suggested MySQL unmasking strategy looks solid and storing up to 16 different boolean fields with just a couple of columns is better than having 16 of them instead. If it is too confusing using bit manipulation and you prefer to use your web-scripting language to get each boolean, just store it as a VARCHAR and do the unmasking procedure in the code (you also don't need to limit it to 8 fields)... – Unweave

The BIT type exists. See dev.mysql.com/doc/refman/8.0/en/bit-type.html – Acclimate

Answer 9

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.

Gurney

If you really wanted to waste this much space and compromise performance, you could have at least done CHAR(1) with Y and N options. – Pinzler

In most real world situations there is a real difference between a 'no' and a mere absence of information. For example, you may want to have a checkbox ticked by default if a user didn't actually say 'no' yet. Exactly how much space do you think you are saving, and how much processing do you do every time you need to distinguish between a false and a NULL - if indeed you even CAN distinguish? In a world of stored images and digital video, the bit or two of space saving is utterly irrelevant but the clarity and reduced processing is real. – Gurney

This answer isn't wrong because it will work and it isn't as bad as people give it credit. For most projects (ie: table sizes < 1mil rows) The performance differences between the provided solutions will be neglegable. I won't complain if my queries return in 7 vs 5 milliseconds... To be fair though if your tables are growing into the 10mil rows or more this probably isn't the preferred solution. – Henning

+1 from me for using the ENUM data type. I personally prefer this notation: ENUM('y','n'). It is compact (just a byte long), intuitive and good looking as application-level convention for all boolean flags. You can use it directly with HTML form fields. for example with PHP: <select name="production"> <option value="y"<?=$production === 'y'? ' selected="selected"' : ''?>>Yes</option> <option value="n"<?=$production === 'n'? ' selected="selected"' : ''?>>No</option> </select> – Gallipot

Lol this cought my eye but I have to say @GeoffKendall is right. In a ton of cases there is no need for optimum performance and whatever method does the job for you is the right method. – Paroxysm

Add CHARSET ascii COLLATE ascii_bin. – Acclimate

Answer 10

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

Parasiticide

what about enum(0, 1) – Bea

@santiagoarizti ENUM (it must be enum('0', '1') - note: those are strings) is not a good idea. There are too many issues due to how it is stored internally, and how non-string values are treated. Eg. 0 and FALSE cannot be stored. 1 and TRUE become '0'. And 2 becomes '1'. – Parasiticide

Best answer... for those using MySQL 8+ – Acclimate

Answer 11

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

Attempt

It's true that BIT(1) will only allow a b'0' or b'1' value to be stored. The biggest issue with BIT datatype is that various client libraries have a variety of wonky handling of the datatype. Checkout the behavior in various SQL tools (SQLyog, TOAD for MySQL, SQL Developer), tools that "reverse engineer" database models, and various clients, like JDBC, PHP, Perl DBI, and for good measure, test a few ORM frameworks (Hibernate, Mybatis, JPA). In terms of ease of use, tool/framework compatibility/native support, TINYINT(1) is the clear winner. – Enterectomy

Yes. It completes depends on the framework being considered for the app. For example, PHP 's Phalcon framework doesn't handle Bit datatype – Attempt

For the record, MyBatis supports both BIT and TINYINT. Refer MyBatis's JdbcType class, mybatis.org/mybatis-3/apidocs/reference/org/apache/ibatis/type/… – Brawny

@Attempt I give you plus one for the mention of BIT(1) but also would point out to the developers reading this - Know your language that will be in the Application Layer / Data Access Layer and know your libraries support. I am using Java currently and BIT(1) is the recommended choice at this time for libraries like Hybernate and use of JDBC. Here is the URL [See table 5.2]: dev.mysql.com/doc/connector-j/en/… – Aretino

Answer 12

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.

Maldonado

Care to explain in what way it complicated your development? – Sainted

@ChazyChaz it expects true/false instead of 1/0, unlike some other dbs like SQL Server. This can sometimes lead to weird situations where you think you're setting it to true but it doesn't actually happen. – Ingleside

Answer 13

You can use BOOL, BOOLEAN data type for storing boolean values.

These types are synonyms for TINYINT(1)

However, the BIT(1) data type makes more sense to store a boolean value (either true[1] or false[0]) but TINYINT(1) is easier to work with when you're outputting the data, querying and so on and to achieve interoperability between MySQL and other databases. You can also check this answer or thread.

MySQL also converts BOOL, BOOLEAN data types to TINYINT(1).

Further, read documentation

Dinky