Boolean vs tinyint 1 for boolean values in MySQL

Question

What column type is best to use in a MySQL database for boolean values  I use boolean but my colleague uses tinyint 1

User · Answer

While it s true that bool and tinyint 1  are functionally identical  bool should be the preferred option because it carries the semantic meaning of what you re trying to do  Also  many ORMs will convert bool into your programing language s native boolean type

User · Answer

I am going to take a different approach here and suggest that it is just as important for your fellow developers to understand your code as it is for the compiler database to   Using boolean may do the same thing as using tinyint  however it has the advantage of semantically conveying what your intention is  and that s worth something     If you use a tinyint  it s not obvious that the only values you should see are 0 and 1   A boolean is ALWAYS true or false

User · Answer

boolean isn t a distinct datatype in MySQL  it s just a synonym for tinyint  See this page in the MySQL manual   Personally I would suggest use tinyint as a preference  because boolean doesn t do what you think it does from the name  so it makes for potentially misleading code  But at a practical level  it really doesn t matter -- they both do the same thing  so you re not gaining or losing anything by using either

User · Answer

My experience when using Dapper to connect to MySQL is that it does matter  I changed a non nullable bit 1  to a nullable tinyint 1  by using the following script   ALTER TABLE TableName MODIFY Setting BOOLEAN null    Then Dapper started throwing Exceptions  I tried to look at the difference before and after the script  And noticed the bit 1  had changed to tinyint 1    I then ran   ALTER TABLE TableName CHANGE COLUMN Setting Setting BIT 1  NULL DEFAULT NULL    Which solved the problem

User · Answer

These data types are synonyms

User · Answer

Whenever you choose int or bool it matters especially when nullable column comes into play  Imagine a product with multiple photos  How do you know which photo serves as a product cover  Well  we would use a column that indicates it  So far out product image table has two columns  product id and is cover Cool  Not yet  Since the product can have only one cover we need to add a unique index on these two columns  But wait  if these two column will get an unique index how would you store many non-cover images for the same product  The unique index would throw an error here  So you may though  quot Okay  but you can use NULL value since these are ommited by unique index checks quot   and yes this is truth  but we are loosing linguistic rules here  What is the purpose of NULL value in boolean type column  Is it  quot all quot    quot any quot   or  quot no quot   The null value in boolean column allows us to use the unique index  but it also messes up how we interpret the records  I would tell that in some cases the integer can serve a better purpose since its not bound to strict true or false meaning

User · Answer

use enum its the easy and fastest  i will not recommend enum or tinyint 1  as bit 1  needs only 1 bit for storing boolean value while tinyint 1  needs 8 bits   ref  TINYINT vs ENUM 0  1  for boolean values in MySQL

[sql] Boolean vs tinyint(1) for boolean values in MySQL

Examples related to sql

Examples related to mysql