MySQL Boolean Data Type

Introduction

In this chapter, we will learn about the Boolean data type in MySQL. The Boolean data type is used to store values that can be either true or false. Although MySQL does not have a built-in Boolean data type, it provides alternatives to achieve the same functionality. We will cover the syntax for defining Boolean-like columns, examples, and important considerations for using Boolean values in MySQL.

Syntax

In MySQL, Boolean values are typically represented using the TINYINT(1) data type. The values 0 and 1 are used to represent FALSE and TRUE, respectively.

Defining a Boolean Column

TINYINT(1)

Example

Let’s go through an example where we create a table with Boolean-like columns and demonstrate their usage.

Full Example

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Employees Table with Boolean-like Columns:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    is_active TINYINT(1),
    is_manager TINYINT(1)
);

4. Insert Data into the Employees Table:

INSERT INTO employees (first_name, last_name, email, is_active, is_manager) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', 1, 0),
('Priya', 'Singh', 'priya.singh@example.com', 1, 1),
('Amit', 'Kumar', 'amit.kumar@example.com', 0, 0),
('Neha', 'Verma', 'neha.verma@example.com', 1, 0),
('Sahil', 'Mehta', 'sahil.mehta@example.com', 0, 0);

5. Query the Employees Table:

SELECT * FROM employees;

Output

In this example, the is_active and is_manager columns are defined as TINYINT(1) to store Boolean-like values, where 1 represents TRUE and 0 represents FALSE.

Using Boolean Values in Queries

You can use Boolean values in queries to filter or sort results based on the Boolean columns.

Example: Query Active Employees

SELECT * FROM employees WHERE is_active = 1;

Output

Example: Query Manager Employees

SELECT * FROM employees WHERE is_manager = 1;

Output

Important Considerations

• Storage Requirements: The TINYINT(1) data type uses 1 byte of storage. This is efficient for storing Boolean-like values.

• Boolean Aliases: MySQL supports the use of BOOLEAN and BOOL as aliases for TINYINT(1). However, these aliases do not change the underlying data type, which remains TINYINT(1).

  CREATE TABLE example (
      id INT PRIMARY KEY AUTO_INCREMENT,
      flag BOOLEAN
  );
  

• Default Values: Consider setting default values for Boolean columns to ensure consistency in your data. For example, you can set the default value of a Boolean column to 0 (FALSE).

  CREATE TABLE example (
      id INT PRIMARY KEY AUTO_INCREMENT,
      flag BOOLEAN DEFAULT 0
  );
  

Conclusion

The Boolean data type in MySQL is typically represented using the TINYINT(1) data type. This chapter covered the syntax for defining Boolean-like columns, provided examples, and discussed important considerations.