MySQL - Boolean Datatype: A Beginner's Guide

Hello there, future database wizards! Today, we're going to dive into the fascinating world of MySQL's Boolean datatype. Don't worry if you've never written a line of code before – I'll be your friendly guide through this journey, just as I've been for countless students over my years of teaching. So, grab a cup of your favorite beverage, and let's get started!

What is a Boolean Datatype?

Before we jump into MySQL specifics, let's understand what a Boolean is. Imagine you're playing a simple game of "yes" or "no" with a friend. That's essentially what a Boolean is in programming – it can only have two possible values: true or false.

In real-world applications, Booleans are incredibly useful. They can represent:

• Whether a user is logged in or not
• If a product is in stock
• Whether a student has passed an exam

Now, let's see how MySQL handles this concept.

Boolean in MySQL

Here's where things get a bit quirky – MySQL doesn't have a native BOOLEAN datatype! I know, I know, it sounds confusing. But don't worry, MySQL has a clever workaround.

In MySQL, BOOLEAN is actually treated as a synonym for TINYINT(1). This means when you create a BOOLEAN column, MySQL secretly creates a TINYINT(1) column instead.

Let's see this in action:

CREATE TABLE game_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    game_name VARCHAR(50),
    is_completed BOOLEAN
);

INSERT INTO game_status (game_name, is_completed) VALUES 
('Chess', TRUE),
('Monopoly', FALSE),
('Scrabble', TRUE);

SELECT * FROM game_status;

When you run this SELECT statement, you might be surprised to see:

+----+-----------+--------------+
| id | game_name | is_completed |
+----+-----------+--------------+
|  1 | Chess     |            1 |
|  2 | Monopoly  |            0 |
|  3 | Scrabble  |            1 |
+----+-----------+--------------+

Wait a minute! Where are our TRUE and FALSE values? Well, remember what I said about BOOLEAN being a TINYINT(1)? Here's what's happening:

• TRUE is stored as 1
• FALSE is stored as 0

It's like MySQL is playing a secret game of "1 means yes, 0 means no" behind the scenes!

Replacing BOOLEAN 0,1 with TRUE and FALSE

Now, you might be thinking, "But I want to see TRUE and FALSE in my results!" Don't worry, MySQL has got you covered. We can use a CASE statement to convert those sneaky 0s and 1s back into TRUE and FALSE:

SELECT 
    id, 
    game_name, 
    CASE 
        WHEN is_completed = 1 THEN 'TRUE'
        ELSE 'FALSE'
    END AS is_completed
FROM game_status;

This will give you a more human-readable result:

+----+-----------+--------------+
| id | game_name | is_completed |
+----+-----------+--------------+
|  1 | Chess     | TRUE         |
|  2 | Monopoly  | FALSE        |
|  3 | Scrabble  | TRUE         |
+----+-----------+--------------+

Much better, right? It's like we've given MySQL a little translator to speak our language!

Boolean Operators Using a Client Program

Now that we understand how MySQL handles Booleans, let's look at some Boolean operators. These are like the rules of our "yes/no" game, telling MySQL how to combine or manipulate Boolean values.

Here are the main Boolean operators in MySQL:

Operator	Description
AND	True if both are true
OR	True if either is true
NOT	Reverses the Boolean value
XOR	True if exactly one is true

Let's see these in action with our game_status table:

-- AND operator
SELECT * FROM game_status WHERE is_completed = TRUE AND game_name = 'Chess';

-- OR operator
SELECT * FROM game_status WHERE is_completed = TRUE OR game_name = 'Monopoly';

-- NOT operator
SELECT * FROM game_status WHERE NOT is_completed;

-- XOR operator
SELECT * FROM game_status WHERE is_completed XOR game_name = 'Monopoly';

Let's break these down:

1. The AND query will only return rows where the game is completed AND the game name is 'Chess'.
2. The OR query will return rows where either the game is completed OR the game name is 'Monopoly'.
3. The NOT query will return rows where the game is not completed (remember, this means is_completed = 0).
4. The XOR query will return rows where either the game is completed or the game name is 'Monopoly', but not both.

These operators are like the building blocks of more complex queries. They allow you to ask very specific questions of your data, just like a detective piecing together clues!

Conclusion

And there you have it, folks! We've journeyed through the land of MySQL Booleans, from their quirky implementation as TINYINT(1) to the powerful operators that help us query our data.

Remember, in the world of databases, everything is either a 1 or a 0, a TRUE or a FALSE. It's like the database is constantly playing a huge game of "yes/no" with our data. And now, you have the tools to join in on that game!

As you continue your MySQL adventure, you'll find that these Boolean concepts pop up everywhere. They're the secret sauce that helps us filter data, make decisions in our code, and ultimately build powerful and dynamic applications.

So go forth, my students, and may your queries always return TRUE (unless you want them to return FALSE, of course)!

Credits: Image by storyset