121

In which cases would you use which? Is there much of a difference? Which I typically used by persistence engines to store booleans?

carrier
  • 32,209
  • 23
  • 76
  • 99

6 Answers6

139

A TINYINT is an 8-bit integer value, a BIT field can store between 1 bit, BIT(1), and 64 bits, BIT(64). For a boolean values, BIT(1) is pretty common.

Robert Gamble
  • 106,424
  • 25
  • 145
  • 137
  • 15
    what's the difference between a TINYINT and a BIT(8) ? – Pacerier Oct 16 '11 at 15:54
  • 18
    TINYINT can be signed or unsigned and relate to negative numbers. Bit just stores the bits without signing data, you are left to interpret the MSB yourself. – defines Feb 03 '12 at 16:54
  • 11
    To avoid confusion it should be added that TINYINT and BIT(1) do not differ in their [Storage Requirements](https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html) and that BOOL and BOOLEAN are synonyms for TINYINT(1) [Numeric Type Overview](https://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html). – Timo Strotmann Jul 11 '18 at 17:18
64

From Overview of Numeric Types;

BIT[(M)]

A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.

This data type was added in MySQL 5.0.3 for MyISAM, and extended in 5.0.5 to MEMORY, InnoDB, BDB, and NDBCLUSTER. Before 5.0.3, BIT is a synonym for TINYINT(1).

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

Additionally consider this;

BOOL, BOOLEAN

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

Sean Fahey
  • 1,850
  • 3
  • 26
  • 36
Nelson Miranda
  • 5,484
  • 5
  • 33
  • 54
  • 12
    You are saying that `boolean` will take a byte even though its really just a bit, so a BIT(1) is better after v5.0.3? – Pacerier Jul 06 '12 at 00:57
  • 3
    Yes @Pacerier. Boolean is simply an ugly alias for a number field. – Áxel Costas Pena Mar 20 '13 at 09:54
  • 9
    As far as actual storage, BIT(1) still occupies one byte minimum. BIT(M) = (M+7)/8 bytes. (1+7)/8 = 1 byte. See [Numeric Type Storage Requirements](https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html#data-types-storage-reqs-numeric). – Drazen Bjelovuk Aug 11 '17 at 14:52
  • 5
    Sad that `BOOL`/`BOOLEAN` are aliases for `TINYINT(1)` instead of `BIT`. Sure, they all end up occupying a whole byte, but semantically `BIT` would be much more appropriate. – MestreLion Feb 23 '19 at 03:20
43

All these theoretical discussions are great, but in reality, at least if you're using MySQL and really for SQLServer as well, it's best to stick with non-binary data for your booleans for the simple reason that it's easier to work with when you're outputting the data, querying and so on. It is especially important if you're trying to achieve interoperability between MySQL and SQLServer (i.e. you sync data between the two), because the handling of BIT datatype is different in the two of them. SO in practice you will have a lot less hassles if you stick with a numeric datatype. I would recommend for MySQL to stick with BOOL or BOOLEAN which gets stored as TINYINT(1). Even the way MySQL Workbench and MySQL Administrator display the BIT datatype isn't nice (it's a little symbol for binary data). So be practical and save yourself the hassles (and unfortunately I'm speaking from experience).

Sheldmandu
  • 1,700
  • 16
  • 10
  • 1
    In my opinion it is not my fault, that some interfaces etc. do interpret the correct binary data incorrectly. If an administrator (including myself) complains about some symbol (referring to MySQL Wrokbench) then this is the fault of whoever misinterpreted my correct (binary) data as a symbol which gives no information about the content. So MySQL/Oracle made the mistake and I am not willing to change my programming concept only because anybody made a mistake. – Matteo B. Mar 18 '15 at 09:15
12

BIT should only allow 0 and 1 (and NULL, if the field is not defined as NOT NULL). TINYINT(1) allows any value that can be stored in a single byte, -128..127 or 0..255 depending on whether or not it's unsigned (the 1 shows that you intend to only use a single digit, but it does not prevent you from storing a larger value).

For versions older than 5.0.3, BIT is interpreted as TINYINT(1), so there's no difference there.

BIT has a "this is a boolean" semantic, and some apps will consider TINYINT(1) the same way (due to the way MySQL used to treat it), so apps may format the column as a check box if they check the type and decide upon a format based on that.

Michael Madsen
  • 54,231
  • 8
  • 72
  • 83
5

Might be wrong but:

Tinyint is an integer between 0 and 255

bit is either 1 or 0

Therefore to me bit is the choice for booleans

Allen Hardy
  • 169
  • 1
  • 8
3

From my experience I'm telling you that BIT has problems on linux OS types(Ubuntu for ex). I developped my db on windows and after I deployed everything on linux, I had problems with queries that inserted or selected from tables that had BIT DATA TYPE.

Bit is not safe for now. I changed to tinyint(1) and worked perfectly. I mean that you only need a value to diferentiate if it's 1 or 0 and tinyint(1) it's ok for that

AndreiTiberiu
  • 55
  • 2
  • 6