MySQL - TINYINT

MySQL: The TINYINT Data Type

The TINYINT data type in MySQL is the smallest integer type available. It is used to store integer values that fall within a very small range, occupying only 1 byte (8 bits) of storage. This makes it highly efficient for storing data like flags, status codes, or small counters.

Key Characteristics

TINYINT can be defined as SIGNED (the default) or UNSIGNED.

• TINYINT SIGNED: Can store values from -128 to 127.
• TINYINT UNSIGNED: Can store values from 0 to 255.

**Deprecation Notice:** In older versions of MySQL, you might see `TINYINT(M)`, where `M` was a display width. This attribute, along with `ZEROFILL`, has been deprecated since MySQL 8.0.17. They do not affect the storage range of the column and should no longer be used in modern development.

The Convention for Boolean Values

MySQL does not have a native BOOLEAN or BOOL data type. Instead, these are aliases that create a TINYINT(1) column. This has become the standard convention for storing true/false values in MySQL.

• A value of 0 is considered false.
• Any non-zero value (typically 1) is considered true.

When you create a table with is_active BOOLEAN, MySQL actually creates is_active TINYINT(1). This is an important concept for both database design and application-level logic.

Practical Example

Let’s create a products table that uses TINYINT for a status flag and an inventory status code.

-- Create the products table
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    is_available BOOLEAN NOT NULL DEFAULT TRUE, -- Stored as TINYINT(1)
    status_code TINYINT UNSIGNED NOT NULL DEFAULT 0 -- 0: In Stock, 1: Low Stock, 2: Out of Stock
);

-- Let's see how MySQL actually created the table
DESCRIBE products;

The DESCRIBE command will show you the underlying types:

Field	Type	Null	Key	Default	Extra
id	int	NO	PRI	NULL	auto_increment
product_name	varchar(100)	NO		NULL
is_available	tinyint(1)	NO		1
status_code	tinyint unsigned	NO		0

Inserting and Querying Data

Now let’s insert some data. You can use TRUE/FALSE keywords for boolean columns.

INSERT INTO products (product_name, is_available, status_code) VALUES
('Laptop Pro', TRUE, 0),
('Wireless Mouse', FALSE, 2),
('Keyboard', TRUE, 1),
('Webcam', TRUE, 0);

Handling Out-of-Range Errors

If you try to insert a value outside the allowed range for a TINYINT column, MySQL will raise an error.

-- This will fail because -5 is not allowed in an UNSIGNED column.
INSERT INTO products (product_name, status_code) VALUES ('Invalid Product', -5);

MySQL will return an error similar to this:

ERROR 1264 (22003): Out of range value for column 'status_code' at row 1

Filtering by Boolean Values

You can easily filter for records based on the boolean column.

-- Find all available products
SELECT product_name FROM products WHERE is_available = TRUE;

product_name
Laptop Pro
Keyboard
Webcam

Using TINYINT in an Application (Python Example)

When you retrieve TINYINT data in your application, the database driver often converts it to a native integer or boolean type. Here’s a Python example showing how TINYINT(1) is typically handled as a boolean.

import mysql.connector
import os
from dotenv import load_dotenv

load_dotenv()

def get_product_availability():
    """Fetches product data and interprets the TINYINT(1) column."""
    products = []
    try:
        with mysql.connector.connect(
            host=os.getenv('DB_HOST'),
            user=os.getenv('DB_USER'),
            password=os.getenv('DB_PASSWORD'),
            database=os.getenv('DB_NAME')
        ) as conn:
            # The cursor will automatically handle TINYINT(1) to Python's int (0 or 1)
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute("SELECT product_name, is_available FROM products")
                products = cursor.fetchall()

    except mysql.connector.Error as e:
        print(f"Database error: {e}")
        return None

    return products

if __name__ == "__main__":
    product_list = get_product_availability()

    if product_list:
        print("Product Availability Status:")
        for product in product_list:
            # We can treat the integer result as a boolean in our code
            name = product['product_name']
            available = bool(product['is_available'])
            status_text = "Available" if available else "Not Available"
            print(f"- {name}: {status_text}")
            # You can also check its type
            # print(f"Type of is_available for {name}: {type(product['is_available'])}") # Will likely be <class 'int'>