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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 lt  fiddle demo

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 lt  fiddle demo

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp    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  amp  128   gt  gt  7 AS myBool FROM myTable t   if bit flags   128    gt  1  true  if bit flags   0    gt  0  false    You can run statements like these as you test  SELECT  128  amp  128   gt  gt  7   SELECT  0  amp  128   gt  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp    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  amp  128   gt  gt  7 AS myBool FROM myTable t   if bit flags   128    gt  1  true  if bit flags   0    gt  0  false    You can run statements like these as you test  SELECT  128  amp  128   gt  gt  7   SELECT  0  amp  128   gt  gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Which MySQL data type to use for storing boolean values

Examples related to mysql

Examples related to boolean

Examples related to sqldatatypes