MySQL - BOOLEAN

MySQL: Understanding the BOOLEAN Data Type

In logic and programming, a Boolean represents a truth value. It can only be one of two possibilities: true or false. This is fundamental for controlling application flow, filtering data, and representing binary states.

For example, in an e-commerce database, you might want to filter products that are currently in stock. A boolean column like is_available would be perfect for this.

/* Fictional Product Table Example */
-- product_name | is_available
----------------|--------------
-- 'Laptop'     | true
-- 'Mouse'      | false
-- 'Keyboard'   | true

Here, is_available is a boolean column that clearly indicates the stock status, allowing for simple and efficient queries.

The Truth About BOOLEAN in MySQL

A crucial point to understand is that MySQL does not have a native, built-in BOOLEAN data type. When you declare a column as BOOLEAN or its synonym BOOL, MySQL internally maps it to TINYINT(1).

MySQL treats the integer 0 as FALSE and any non-zero integer as TRUE. For convenience, MySQL provides the keywords TRUE and FALSE as aliases for 1 and 0, respectively.

Note: The keywords TRUE and FALSE are case-insensitive. true, True, and TRUE are all treated as 1.

Syntax for Defining a Boolean Column

You can use the BOOLEAN keyword when creating a table for clarity and standard SQL compliance.

CREATE TABLE user_settings (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    email_notifications BOOLEAN DEFAULT TRUE,
    is_premium_member BOOL NOT NULL
);

Example: Verifying the Underlying Type

Let’s create the table and then inspect its structure using DESCRIBE.

DESCRIBE user_settings;

Output

As you can see, the email_notifications and is_premium_member columns, defined as BOOLEAN and BOOL, are both created as tinyint(1).

Field	Type	Null	Key	Default	Extra
user_id	int	NO	PRI	NULL	auto_increment
email_notifications	tinyint(1)	YES		1
is_premium_member	tinyint(1)	NO

Defining and Using BOOLEAN Columns

Now, let’s insert data into our user_settings table using the TRUE and FALSE keywords.

INSERT INTO user_settings (user_id, email_notifications, is_premium_member)
VALUES
    (1, FALSE, TRUE),
    (2, TRUE, FALSE),
    (3, DEFAULT, FALSE); -- Uses the default value 'TRUE' for email_notifications

When we retrieve the data, we’ll see the stored integer values.

SELECT * FROM user_settings;

The result shows 1 for TRUE and 0 for FALSE.

user_id	email_notifications	is_premium_member
1	0	1
2	1	0
3	1	0

You can filter using boolean conditions in the WHERE clause.

-- Find all premium members
SELECT user_id, is_premium_member FROM user_settings WHERE is_premium_member IS TRUE;

Best Practices for Boolean Values

• Use BOOLEAN or BOOL: Always use the BOOLEAN or BOOL keyword for defining columns that represent truth values. It makes the schema’s intent clear, even though the underlying type is TINYINT(1).
• Use IS TRUE / IS FALSE: When filtering, prefer WHERE my_column IS TRUE or WHERE my_column IS FALSE over WHERE my_column = 1 or WHERE my_column = 0. This syntax is more readable and explicitly handles boolean logic. It also correctly handles cases involving NULL.
• Handle NULL: A boolean column can be NULL, which represents an ‘unknown’ or ‘not applicable’ state, distinct from TRUE or FALSE. Ensure your application logic correctly handles this third state if you allow NULL values.
• Application Layer Logic: Decide whether to handle the conversion from 0/1 to true/false in the database or in your application code. Most modern applications handle this in the application layer, keeping database queries focused on data retrieval.

Displaying ‘TRUE’ and ‘FALSE’ in Queries

While application code typically handles the display logic, you can use a CASE statement in your SQL query to return more readable string representations of boolean values directly from the database.

Syntax

SELECT
    column,
    CASE
        WHEN boolean_column = 1 THEN 'Yes, Premium'
        WHEN boolean_column = 0 THEN 'No, Standard'
        ELSE 'Unknown'
    END AS member_status
FROM your_table;

Example

Let’s apply this to our user_settings table to create a descriptive status.

SELECT
    user_id,
    CASE is_premium_member
        WHEN 1 THEN 'TRUE'
        WHEN 0 THEN 'FALSE'
    END AS 'Premium Status'
FROM user_settings;

Output

user_id	Premium Status
1	TRUE
2	FALSE
3	FALSE

Common Errors and Debugging

• Error: WHERE is_premium_member = 'TRUE'. Reason: TRUE is a keyword representing 1, not a string. Don’t wrap it in quotes. Fix: Use WHERE is_premium_member IS TRUE or WHERE is_premium_member = TRUE.
• Confusion: A query WHERE non_boolean_column where non_boolean_column is an integer. Reason: MySQL’s boolean evaluation (0 is false, non-zero is true) applies everywhere, not just on TINYINT(1) columns. A query like SELECT * FROM products WHERE stock_quantity; will return all products where stock_quantity is not 0. Be explicit to avoid confusion: SELECT * FROM products WHERE stock_quantity > 0;.

Working with Boolean Data in Application Code

Most modern database drivers and ORMs automatically handle the conversion between the database’s 0/1 representation and the programming language’s native boolean type (true/false). Here are some secure, modern examples.

Modern Implementation Examples

Node.js (async/await)
Python (mysql-connector)
Java (JDBC)
PHP (PDO)



This example uses the popular `mysql2/promise` library with async/await for clean, non-blocking code. It uses prepared statements to prevent SQL injection.

```javascript
// main.js
import mysql from 'mysql2/promise';

async function main() {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'your_database'
    });

    // Insert data using prepared statements
    const insertQuery = 'INSERT INTO user_settings (email_notifications, is_premium_member) VALUES (?, ?)';
    await connection.execute(insertQuery, [true, false]);
    console.log('New user setting inserted successfully.');

    // Fetch and process boolean data
    const [rows] = await connection.execute('SELECT user_id, is_premium_member FROM user_settings WHERE is_premium_member = ?', [false]);

    console.log('Standard members:');
    rows.forEach(row => {
      // The driver converts 0/1 to false/true automatically
      console.log(`User ID: ${row.user_id}, Is Premium: ${row.is_premium_member}`);
    });

  } catch (error) {
    console.error('Database operation failed:', error);
  } finally {
    if (connection) await connection.end();
  }
}

main();

This Python example uses mysql-connector-python with parameterized queries to ensure security. The with statement ensures resources are managed correctly.

main.py

import mysql.connector
from mysql.connector import errorcode

config = {
    'user': 'root',
    'password': 'password',
    'host': '127.0.0.1',
    'database': 'your_database'
}

try:
    with mysql.connector.connect(**config) as cnx:
        with cnx.cursor(dictionary=True) as cursor:
            # Insert data using parameter substitution (protects against SQL injection)
            add_setting = ("INSERT INTO user_settings "
                           "(email_notifications, is_premium_member) VALUES (%s, %s)")
            data_setting = (True, True) # Booleans are handled correctly
            cursor.execute(add_setting, data_setting)
            cnx.commit()
            print(f"Inserted new setting for user ID: {cursor.lastrowid}")

            # Fetch and process data
            query = "SELECT user_id, is_premium_member FROM user_settings WHERE is_premium_member = %s"
            cursor.execute(query, (True,))

            print("\nPremium members:")
            for row in cursor:
                # The connector returns 1/0, which Python evaluates as True/False
                print(f"User ID: {row['user_id']}, Is Premium: {bool(row['is_premium_member'])}")

except mysql.connector.Error as err:
    print(f"Something went wrong: {err}")

This modern Java example uses try-with-resources for automatic resource management and PreparedStatement for security and type safety.

Main.java

import java.sql.*;

public class MysqlBooleanExample {
    private static final String URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        String sql = "SELECT user_id, email_notifications FROM user_settings WHERE is_premium_member = ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // Set boolean parameter
            pstmt.setBoolean(1, true);

            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.println("Users who are premium members:");
                while (rs.next()) {
                    int id = rs.getInt("user_id");
                    // Retrieve as a boolean directly
                    boolean hasNotifications = rs.getBoolean("email_notifications");

                    System.out.printf("User ID: %d, Has Email Notifications: %b\n", id, hasNotifications);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This PHP example uses PDO (PHP Data Objects), which is the modern, recommended way to interact with databases. It supports prepared statements and provides a consistent interface for different database systems.

config.php

<?php
$host = '127.0.0.1';
$db   = 'your_database';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// main.php
require 'config.php';

// Fetch premium members using a prepared statement
$is_premium = true;
$stmt = $pdo->prepare('SELECT user_id, email_notifications FROM user_settings WHERE is_premium_member = ?');
$stmt->execute([$is_premium]);

$users = $stmt->fetchAll();

echo "Premium members:\n";
foreach ($users as $user) {
    // PDO can fetch boolean values correctly
    $notifications_enabled = (bool)$user['email_notifications'];
    echo sprintf(
        "User ID: %d, Notifications Enabled: %s\n",
        $user['user_id'],
        $notifications_enabled ? 'Yes' : 'No'
    );
}
?>