MariaDB TINYINT

In this article, I’m going to walk you through everything you need to know about the TINYINT data type. We’ll cover ranges, storage, and a few “pro tips” that will save you from common pitfalls.

MariaDB TINYINT

What is MariaDB TINYINT?

At its core, TINYINT is the smallest integer data type available in MariaDB. While a standard INT is like a big container, a TINYINT is a small, compact box. It is designed to store very small whole numbers with maximum efficiency.

Every time you define a column as TINYINT, MariaDB allocates exactly 1 byte of storage per row. In a world of petabytes, 1 byte might not seem like much, but when you’re dealing with a table containing 500 million records, those saved bytes translate into faster queries, smaller indexes, and lower cloud storage bills.

Storage and Ranges: Signed vs. Unsigned

One of the first things I teach junior developers at my firm in Seattle is the difference between Signed and Unsigned integers. This distinction determines the “reach” of your numbers.

1. TINYINT SIGNED (The Default)

By default, all integer types in MariaDB are “Signed.” This means they can store both positive and negative numbers. One bit is used to track the sign (+ or -), leaving the rest for the value.

• Range: -128 to 127
• Use Case: Tracking temperature fluctuations in a Denver warehouse or net gain/loss for a small transaction.

2. TINYINT UNSIGNED

If you know for a fact that your data will never be negative (like someone’s age or a star rating), you should always use the UNSIGNED attribute. This effectively doubles your positive capacity.

• Range: 0 to 255
• Use Case: Age of a customer, number of items in a shopping cart, or a priority level (1–10).

At-a-Glance Range Table

Attribute	Minimum Value	Maximum Value	Storage Size
SIGNED	-128	127	1 Byte
UNSIGNED	0	255	1 Byte

The “Boolean” Mystery: TINYINT(1)

If you’ve ever looked at a schema, you might have seen the data type BOOLEAN. Here’s a professional secret: MariaDB doesn’t have a true built-in Boolean type.

When you run a command like CREATE TABLE users (is_active BOOLEAN);, MariaDB silently converts that to TINYINT(1).

• 0 is treated as FALSE.
• 1 (or any non-zero value) is treated as TRUE.

What does the (1) mean?

There is a common misconception that TINYINT(1) can only store the number 1. This is false. The number in parentheses is the Display Width. It tells client applications (like a command-line tool) how many characters to display. A TINYINT(1) can still store the number 127 if it’s signed.

Step-by-Step Tutorial: Implementing TINYINT

Let’s walk through a real-world scenario. Imagine we are building a “Product Review” table. We want to store a star rating (1-5) and a flag to show if the review is verified.

Step 1: Connecting to your Instance

First, fire up your terminal or a GUI tool like DBeaver or HeidiSQL. Connect to your MariaDB server.

Step 2: Creating the Table

I always recommend using UNSIGNED for ratings because a “negative 3-star review” doesn’t make sense.

SQL

CREATE TABLE product_reviews (
    review_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    -- We use UNSIGNED because ratings are 1-5
    rating TINYINT UNSIGNED NOT NULL,
    -- BOOLEAN is a synonym for TINYINT(1)
    is_verified BOOLEAN DEFAULT 0
);

Step 3: Inserting Data

Notice that even though is_verified is technically a TINYINT, we can use the keywords TRUE and FALSE. MariaDB handles the conversion for us.

SQL

INSERT INTO product_reviews (product_name, rating, is_verified)
VALUES ('Organic Coffee Beans', 5, TRUE),
       ('Desktop Lamp', 3, FALSE);

Step 4: Querying the Data

When you pull the data back out, your application will see 0 and 1 for the boolean field.

SQL

SELECT product_name, rating 
FROM product_reviews 
WHERE is_verified = 1;

Advanced Feature: ZEROFILL

Sometimes you might be working with legacy systems that require numbers to be a specific length for sorting or printing. This is where the ZEROFILL attribute comes in.

When you use TINYINT(3) ZEROFILL, MariaDB pads the number with leading zeros up to the display width.

• The number 5 becomes 005.
• The number 42 becomes 042.

Expert Note: Using ZEROFILL automatically makes the column UNSIGNED. It’s also important to know that display width and ZEROFILL are being deprecated in newer versions of MySQL/MariaDB, so use them sparingly and only for visual requirements in specific legacy clients.

Best Practices for MariaDB TINYINT

Here are three golden rules for using TINYINT:

1. Always Default to NOT NULL

Unless you specifically need to represent a “Missing” or “Unknown” state, define your TINYINT columns as NOT NULL. This saves a tiny bit of space and avoids the complexity of NULL logic in your application code.

2. Use TINYINT for Status Enums

If you have a column like order_status that can be Pending, Shipped, or Delivered, don’t use a VARCHAR. Use a TINYINT and map the numbers in your application code (1=Pending, 2=Shipped, etc.). It makes indexing much faster.

3. Be Careful with Calculations

If you add two TINYINT values and the result exceeds 255, you will get an “Out of Range” error if you are in Strict Mode (which you should be). Always ensure your math won’t “overflow” the 1-byte limit.

Summary

Knowing MariaDB TINYINT is about more than just knowing a range of numbers; it’s about an efficient design. By using the smallest possible type for your data, you create a database that is lean, fast, and ready to scale from a single server in Miami to a global cluster.

Whether you’re using it as a boolean flag or a compact counter, the TINYINT is a workhorse of the SQL world.

You may also like the following articles:

• MariaDB SHOW COLUMNS
• MariaDB EXPLAIN
• How to Create Trigger in MariaDB