Using MySQL Boolean Data Type

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:

select TRUE, FALSE;

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.

from Linux Hint https://ift.tt/3oyvv5h