How do i add a boolean field in mysql?

In SQL Server, a Boolean Datatype can be created by means of keeping BIT datatype. Though it is a numeric datatype, it can accept either 0 or 1 or NULL values only. Hence easily we can assign FALSE values to 0 and TRUE values to 1. This will provide the boolean nature for a data type. 

Regarding the storage, if there are less than 9 columns of the bit data are present in the table,  they are stored as 1 byte. And for 9 to 16 BIT columns,  their consumption is 2 bytes and so on it will hold. In this article, let us how we can add a Boolean i.e. BIT datatype to an existing table in SQL SERVER.

Let us assume there is a database “GEEKSFORGEEKS” is available and there is a table called “Authors” available with the following data.

Query:

USE GEEKSFORGEEKS
SELECT * FROM Authors;

Output:

Let us add a BIT data type to the “Authors” table. Bit datatype is helpful to represent Boolean nature either True(1) or False(0) and they are the only allowed values for BIT datatype.

-- Add a column named "isActiveAuthor" 
with BIT datatype for "Authors" table
ALTER TABLE Authors  ADD isActiveAuthor BIT;

As there are already few rows available in the table, we can add a new column “isActiveAuthor” as a NULL pattern only. After that, we can update the data. After adding the column, 

Query:

SELECT * FROM Authors;

Output:

So, when a BIT datatype column is added to an existing table, its value will be populated with “NULL” values.

Now let us try to update the column with a condition that if “NumberOfPosts” > 5, set “isActiveAuthor” to have the value 1 or else 0. 

-- Let us assume that if "NumberOfPosts" 
   is more than 5, author is active enough to write articles
-- Hence for this condition , set "isActiveAuthor"
   column to have value 1
UPDATE Authors SET isActiveAuthor = 1 WHERE NumberOfPosts > 5;

-- On the other hand, if "NumberOfPosts"
   is less  than and equal to 5, 
-- author is not active in the recent days
   and hence set "isActiveAuthor" column to have value 0
UPDATE Authors SET isActiveAuthor = 0 WHERE NumberOfPosts <= 5;

SELECT * FROM Authors;

Output:

As the BIT column supports only 0 or 1 as values, we have populated like above.

Let us try to update the value other than 0 or 1 

-- Trying to update the "isActiveAuthor"
   with value 2, though errors are not 
   produced, output is different
UPDATE Authors SET isActiveAuthor = 
2 WHERE NumberOfPosts = 5;

SELECT * FROM Authors;

Output:

Reason for the updated value of 1 in the 3rd row :

Though an update is given to get the value of 2, because of the “BIT” datatype for “isActiveAuthor” column, its value is converted to 1 only as the “BIT” datatype supports either 0 or 1 only. i.e. values other than 0 are converted to 1 and updated in the table.

We can check the same here with different examples

DECLARE @isValid BIT

-- Initially it will have "NULL" value 
SELECT @isValid AS BitType
 
--Assigning any nonzero value converts it to 1
-- i.e. other than 0, if any
   value is provided , it is converted to 1
SET @isValid=9
 
SELECT @isValid AS BitType
 
--Assigning any nonzero value converts it to 1
SET @isValid=-100
 
SELECT @isValid AS BitType

Output:

This proves the “BIT” datatype accepts only the “Boolean” value i.e. either 0 or 1 only. If a nonzero value is given, it is converted to 1 only.

Advantages :

  • Always if the expected values are either 0 or 1 plus NULL, it is a good approach in keeping the column as “BIT” datatype as the code becomes logical
  • Regarding storage, it is economic to use as it takes a little space while comparing to other data types.

By considering the advantages, BIT datatype is the right approach for storing boolean data

By default, MySQL does not offer a native Boolean Data Type. However, it provides us with the TINYINT data type, allowing us to store Boolean values—like values with the TINYINT type.

This guide will focus on how to use the MySQL TINYINT data type to store Boolean Values.

Basic Usage

MySQL defines a value of 0 as false and a non-zero value as true. Therefore, to use Boolean literal values, you use the constants TRUE and FALSE that evaluate the value of 0 and 1.

An example:

Once you execute the above query, you will get an output similar to the one shown below:

mysql> select TRUE, FALSE;

+------+-------+

| TRUE | FALSE |

+------+-------+

|    1 |     0 |

+------+-------+

1 row in set (0.00 sec)

From the above, we can deduce that MySQL considers 1 and 0 as True and False, respectively.

It is good to note that as Uppercase or Lowercase, MySQL assign True and False 0 and 1 as shown in the query below:

select TRUE, FALSE, true, false, True, False;

The output is as shown below:

+------+-------+------+-------+------+-------+

| TRUE | FALSE | true | false | True | False |

+------+-------+------+-------+------+-------+

|    1 |     0 |    1 |     0 |    1 |     0 |

+------+-------+------+-------+------+-------+

1 row in set (0.00 sec)

Example Use Cases

Let us use an example to illustrate how we can use the Boolean Type in MySQL.

Start by creating a database and a table called languages, which will store information about various programming languages.

Inside the table, we have the following columns:

  1. ID – INT NOT NULL AUTO_INCREMENT
  2. Language_name – VARCHAR(100) NOT NULL
  3. Beginner_Friendly – BOOLEAN

Consider the query below to implement the above database:

CREATE DATABASE sampled;

USE sampled;

CREATE TABLE languages (

  ID INT NOT NULL AUTO_INCREMENT,

  Language_name VARCHAR(100),

  Beginner_friendly BOOLEAN,

  PRIMARY KEY (ID)

);

Once you execute the above query, you will have the sampled database with the languages table.

To get the information about the table above, we can use the MySQL DESC statement as shown in the output below:

mysql> DESC languages;

+-------------------+--------------+------+-----+---------+----------------+

| Field             | Type         | Null | Key | Default | Extra          |

+-------------------+--------------+------+-----+---------+----------------+

| ID                | int          | NO   | PRI | NULL    | auto_increment |

| Language_name     | varchar(100) | YES  |     | NULL    |                |

| Beginner_friendly | tinyint(1)   | YES  |     | NULL    |                |

+-------------------+--------------+------+-----+---------+----------------+

3 rows in set (0.01 sec)

If you look at the Beginner_friendly field, which we set as Boolean when creating the table, it now shows Type of TINYINT.

In the next step, let us add some data to the table using the queries provided below:

INSERT INTO sampled.languages (ID, Language_name, Beginner_friendly) VALUES(1, "Python", True);

INSERT INTO sampled.languages (ID, Language_name, Beginner_friendly) VALUES(2, "C++", False);

If you now select the values in the above table:

mysql> select * from sampled.languages

-> ;

+----+---------------+-------------------+

| ID | Language_name | Beginner_friendly |

+----+---------------+-------------------+

|  1 | Python        |                 1 |

|  2 | C++           |                 0 |

+----+---------------+-------------------+

2 rows in set (0.00 sec)

You will see that the values are set to 0 and 1, respectively.

NOTE: You can store numerical values in the Boolean column other than True and False values. For example, consider the query below:

INSERT INTO sampled.languages (ID, Language_name, Beginner_friendly) VALUES(3, "Go Lang", 10);

If you execute the above query, MySQL will not report an error. Instead, it will store the numerical value as 10. Consider the output shown below:

mysql> select * from sampled.languages;

+----+---------------+-------------------+

| ID | Language_name | Beginner_friendly |

+----+---------------+-------------------+

|  1 | Python        |                 1 |

|  2 | C++           |                 0 |

|  3 | Go Lang       |                10 |

+----+---------------+-------------------+

3 rows in set (0.00 sec)

MySQL Boolean Operators

MySQL supports various Boolean operators as IS TRUE, IS FALSE, IS NOT TRUE, and IS NOT FALSE. Based on the name, you can determine that some do exactly the same thing.

For example, IS TRUE and IS NOT FALSE are similar. The same case applies to the other pair.

However, we can use these operators to get a set of values that is either True or False. For example, the query below gets all the values where Beginner_friendly is True.

mysql> select * from sampled.languages WHERE Beginner_friendly IS TRUE;

+----+---------------+-------------------+

| ID | Language_name | Beginner_friendly |

+----+---------------+-------------------+

|  1 | Python        |                 1 |

|  3 | Go Lang       |                10 |

+----+---------------+-------------------+

2 rows in set (0.00 sec)

As we can see from the output, we only get values where the value of Beginner_friendly is True.

You can also get the False values using either IS TRUE or IS NOT TRUE.

mysql> select * from sampled.languages WHERE Beginner_friendly IS NOT TRUE;

+----+---------------+-------------------+

| ID | Language_name | Beginner_friendly |

+----+---------------+-------------------+

|  2 | C++           |                 0 |

+----+---------------+-------------------+

1 row in set (0.00 sec)

Conclusion

This guide has shown you how to implement and use the MySQL Boolean Data types to store True or False values.

About the author

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list

How do I add a boolean column to a MySQL table?

ALTER TABLE users ADD bio VARCHAR(100) NOT NULL; Adding a boolean column with a default value: ALTER TABLE users ADD active BOOLEAN DEFAULT TRUE; MySQL offers extensive documentation on supported datatypes in their documentation.

How do you declare a boolean variable in MySQL?

MySQL does not have a boolean (or bool) data type. Instead, it converts boolean values into integer data types (TINYINT). When you create a table with a boolean data type, MySQL outputs data as 0, if false, and 1, if true.

How do I add a boolean field?

Click on the "+" symbol (Insert a field) and add the Boolean field along with other required fields..
Display Name : Add a display name (For example, “I agree”).
Unique ID : default value..
Help Text : You can provide additional information about the field..

How do you create a boolean field in SQL?

In SQL Server, a Boolean Datatype can be created by means of keeping BIT datatype. Though it is a numeric datatype, it can accept either 0 or 1 or NULL values only. Hence easily we can assign FALSE values to 0 and TRUE values to 1. This will provide the boolean nature for a data type.