What is the difference between BIT and TINYINT in MySQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] What is the difference between BIT and TINYINT in MySQL?

Examples related to mysql

Examples related to bit

Examples related to tinyint