1372

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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
  • 240
    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. http://dev.mysql.com/doc/refman/5.0/en/bit-type.html – smp7d Feb 29 '12 at 14:51
  • 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. – Shadrack Orina Apr 02 '12 at 05:11
  • 3
    Question related to [Alternative to lots of booleans in MySQL?](http://stackoverflow.com/questions/7215062/alternative-to-lots-of-booleans-in-mysql) – tereško Jul 25 '12 at 18:26
  • 8
    for the current version of MYSQL Boolean type is available- http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html check this. according to that value zero considered as false – DevT Aug 29 '12 at 08:51
  • 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. – gcb Oct 27 '13 at 01:48
  • 9
    `bit(1)`'s a bit** to import in Excel. Switching to `tinyint(1)` works. – Cees Timmerman Feb 18 '14 at 13:24
  • 8
    now we have boolean after 5 years – V-SHY Mar 14 '14 at 09:54
  • There are two feature requests for real (not tinyint alias) booleans, both of which are about 10 years old: http://bugs.mysql.com/bug.php?id=24530 and http://bugs.mysql.com/bug.php?id=24531. I couldn't find a ticket for MariaDB but you can [check yourself here](https://mariadb.atlassian.net/browse/MDEV-8646?filter=-8&jql=status%20%3D%20Open%20AND%20text%20~%20%22boolean%22%20order%20by%20updated%20DESC). – Ben Creasy Feb 17 '16 at 02:52
  • @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..."* – ToolmakerSteve Oct 06 '19 at 19:11
  • 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. – Robert Sep 24 '21 at 09:23

13 Answers13

1364

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

flow2k
  • 3,999
  • 40
  • 55
markus
  • 40,136
  • 23
  • 97
  • 142
  • 13
    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 – Roland Bouman May 15 '10 at 22:42
  • 47
    Going for char, in PHP at least, will lead to more code as `!$boolean` will never evaluate properly without further processing. – Mild Fuzz Jun 01 '11 at 20:58
  • The solution is $casts the column in the Laravel model as boolean like protected $casts = [ 'shipped' => 'boolean', 'refund' => 'boolean', ]; – mercury Mar 16 '22 at 19:32
  • BIT is annoying in node.js + [mysql](https://www.npmjs.com/package/mysql) because it comes back as a Buffer, which is always truthy. – Oliver Bock Feb 09 '23 at 22:00
  • Reference link is broken. – SandPiper Jun 25 '23 at 16:34
291

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

informatik01
  • 16,038
  • 10
  • 74
  • 104
Philip Morton
  • 129,733
  • 38
  • 88
  • 97
  • 7
    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 – JamesHalsall Mar 20 '14 at 09:09
  • 42
    @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. – spencer7593 Jul 09 '14 at 19:37
  • 5
    MySQL 5.0 manual clearly says a boolean value is either 1 or 0. The phrase "anything else is `true`" is not true. – Walter Jan 23 '15 at 12:25
  • 7
    @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 – spencer7593 Apr 25 '15 at 01:45
  • 6
    @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. – spencer7593 Apr 25 '15 at 01:50
  • And more examples with visuals: http://www.mysqltutorial.org/mysql-boolean/ – 0x49D1 Oct 07 '19 at 13:26
79

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

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
R. S.
  • 963
  • 7
  • 2
  • 3
    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. – zamnuts Aug 13 '12 at 19:28
  • 78
    Might be 'fast', but it obfuscates the data such that any new developer would have no idea what the column stands for. – Richthofen Apr 02 '13 at 18:16
  • 6
    This is uses the same amount of bytes as BIT(1) – ITS Alaska Apr 25 '13 at 18:18
  • 36
    Good luck getting an ORM to map nicely to this. – Craig Labenz Aug 03 '13 at 21:55
  • 6
    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. – eggyal Mar 21 '15 at 11:52
  • 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. – Gerard ONeill Sep 30 '21 at 03:51
40

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

informatik01
  • 16,038
  • 10
  • 74
  • 104
Josh
  • 10,961
  • 11
  • 65
  • 108
  • 24
    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). – Roland Bouman May 15 '10 at 22:36
  • 5
    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. – spencer7593 Jun 10 '15 at 14:15
  • 11
    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. – CJ Dennis May 15 '18 at 06:30
  • 3
    @CJDennis If you've abstracted your database layer behind the repository pattern, you don't have to worry about the obscurity of this solution. – prograhammer Jan 31 '19 at 02:14
35

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.

Ciaran McNulty
  • 18,698
  • 6
  • 32
  • 40
  • 8
    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. – Roland Bouman May 15 '10 at 22:40
  • 5
    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. – M-Peror Jul 21 '11 at 07:27
  • 1
    @M-Peror - "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. – BMiner Dec 06 '11 at 19:29
  • 2
    @BMiner - 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. – M-Peror Dec 21 '11 at 07:59
22

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

Fred
  • 4,846
  • 1
  • 23
  • 21
18

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.

Thor
  • 659
  • 4
  • 10
  • 1
    +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. – Will B. Jan 17 '14 at 01:35
  • @Thor 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? – Zack Jannsen Feb 02 '16 at 20:29
  • 1
    @Thor 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]: https://dev.mysql.com/doc/connector-j/en/connector-j-reference-type-conversions.html – Zack Jannsen Feb 03 '16 at 11:43
  • 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... – assensi Dec 30 '20 at 18:48
12

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.

Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
Geoff Kendall
  • 1,307
  • 12
  • 13
  • 20
    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. – ILikeTacos Sep 19 '13 at 13:42
  • 4
    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. – Geoff Kendall Aug 06 '14 at 11:59
  • 8
    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. – Brad Sep 29 '14 at 19:05
  • 1
    +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: – Vlado Oct 14 '14 at 09:34
  • 3
    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. – Madmenyo Sep 12 '15 at 14:17
  • Add `CHARSET ascii COLLATE ascii_bin`. – dolmen Nov 05 '19 at 16:14
12

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:

TRiG
  • 10,148
  • 7
  • 57
  • 107
Jonathan
  • 129
  • 1
  • 2
  • 7
    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!) – yep Jul 15 '11 at 18:24
  • @yep 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)... – CPHPython May 09 '18 at 11:00
  • The `BIT` type exists. See https://dev.mysql.com/doc/refman/8.0/en/bit-type.html – dolmen Nov 05 '19 at 16:12
9

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

Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
  • what about enum(0, 1) – santiago arizti Aug 08 '19 at 22:55
  • 3
    @santiagoarizti `ENUM` (it must be `enum('0', '1')` - note: those are strings) is not a good idea. There are [too many issues](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f5e08772c7cea401ec1d66c8707734c3) 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'`. – Paul Spiegel Aug 09 '19 at 07:11
  • Best answer... for those using MySQL 8+ – dolmen Nov 05 '19 at 16:17
7

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.

Community
  • 1
  • 1
Vidz
  • 545
  • 1
  • 6
  • 16
  • 7
    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. – spencer7593 Apr 25 '15 at 02:13
  • 1
    Yes. It completes depends on the framework being considered for the app. For example, PHP 's Phalcon framework doesn't handle Bit datatype – Vidz Apr 28 '15 at 05:54
  • For the record, MyBatis supports both `BIT` and `TINYINT`. Refer MyBatis's JdbcType class, http://mybatis.org/mybatis-3/apidocs/reference/org/apache/ibatis/type/JdbcType.html – Lucky Dec 31 '15 at 14:03
  • 1
    @Vidz 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]: https://dev.mysql.com/doc/connector-j/en/connector-j-reference-type-conversions.html – Zack Jannsen Feb 03 '16 at 11:38
5

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.

Lemures
  • 474
  • 5
  • 11
  • 3
    Care to explain in what way it complicated your development? – Chazy Chaz Dec 20 '18 at 19:08
  • @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. – maembe May 31 '19 at 14:18
2

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

Premkumar chalmeti
  • 800
  • 1
  • 8
  • 23