INT1 (TINYINT) in MariaDB: Code Examples and Alternatives

INT1 in MariaDB

In MariaDB, INT1 is not a distinct data type itself. It's actually a synonym for the TINYINT data type. TINYINT is a small integer data type capable of storing whole numbers within the range of -128 to 127.

Unsigned Values (Optional)
If declared as UNSIGNED TINYINT, the range becomes 0 to 255.
Signed Values
Can represent both negative and positive numbers (-128 to 127)
Storage Size
1 byte

When to Use INT1 (TINYINT)

For flags or boolean values (storing 0 or 1) by using TINYINT(1), which is equivalent to the BOOLEAN data type in MariaDB.
When storage efficiency is a priority, as TINYINT uses only 1 byte of space.
When you need to store small integer values that typically fall within the -128 to 127 range.

Example Usage

CREATE TABLE my_table (
  id INT PRIMARY KEY AUTO_INCREMENT,
  flag TINYINT(1) NOT NULL DEFAULT 0,  -- Boolean-like flag (0 or 1)
  count TINYINT UNSIGNED NOT NULL DEFAULT 0  -- Unsigned count (0 to 255)
);

In this example:

count is an unsigned TINYINT, allowing values from 0 to 255.
flag is a TINYINT(1), essentially a boolean value storing 0 (false) or 1 (true).
id is an integer that automatically increments for each new row.

TINYINT(1) is a space-efficient way to store boolean values (0 or 1).
Consider UNSIGNED TINYINT when you only need non-negative values.
Use INT1 (or TINYINT) for small integer data to save storage space.

Storing User Flags (Boolean-like Values)

CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  is_admin TINYINT(1) NOT NULL DEFAULT 0,  -- 0 for regular user, 1 for admin
  active TINYINT(1) NOT NULL DEFAULT 1   -- 0 for inactive, 1 for active
);

INSERT INTO users (username, is_admin, active) VALUES ('john_doe', 0, 1);
INSERT INTO users (username, is_admin, active) VALUES ('jane_admin', 1, 1);

SELECT * FROM users WHERE is_admin = 1 AND active = 1;  -- Get active admins

Storing Small Counts (Unsigned Values)

CREATE TABLE products (
  product_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  stock_count TINYINT UNSIGNED NOT NULL DEFAULT 0  -- Stock count (0 to 255)
);

UPDATE products SET stock_count = stock_count + 5 WHERE product_id = 1;  -- Increase stock
SELECT * FROM products WHERE stock_count > 0;  -- Get products in stock

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  shipped TINYINT(1) NOT NULL DEFAULT 0,  -- 0 for unshipped, 1 for shipped
  shipping_method VARCHAR(50) DEFAULT NULL  -- Optional shipping method details
);

UPDATE orders SET shipped = 1, shipping_method = 'FedEx' WHERE order_id = 123;
SELECT * FROM orders WHERE shipped = 1;  -- Get shipped orders with shipping method

SMALLINT

Unsigned Values (Optional): 0 to 65,535
Signed Values: -32,768 to 32,767
Storage Size: 2 bytes

Use SMALLINT when you need to store integers slightly larger than the TINYINT range (-128 to 127). It offers a wider range while still maintaining a relatively small storage footprint.

MEDIUMINT

Unsigned Values (Optional): 0 to 16,777,215
Signed Values: -8,388,608 to 8,388,607
Storage Size: 3 bytes

Choose MEDIUMINT when you need a larger range of integers than SMALLINT can handle, but storage space is still a concern.

INT

Unsigned Values (Optional): 0 to 4,294,967,295
Signed Values: -2,147,483,648 to 2,147,483,647
Storage Size: 4 bytes (default, may vary)

This is the standard integer data type in MariaDB. It provides a very wide range for storing integers and is a good choice for general-purpose use cases unless storage is extremely limited.

BIGINT

Unsigned Values (Optional): 0 to 18,446,744,073,709,551,615
Signed Values: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Storage Size: 8 bytes

Use BIGINT when you need to store extremely large integer values. However, be aware that it uses twice the storage space as INT.

Making the Right Choice

The best alternative to INT1 depends on the specific range of values you anticipate:

Reserve BIGINT for exceptionally large values when storage is less of a concern.
Use INT for general-purpose integer storage.
Choose MEDIUMINT for a wider range while still being somewhat compact.
For slightly larger ranges, consider SMALLINT.
If all your values fall within the TINYINT range (-128 to 127), stick with it for optimal storage efficiency.