Understanding Integer Data Types (TINYINT, SMALLINT, MEDIUMINT, BIGINT, INT) in MySQL

Integers in MySQL

• MySQL provides various integer data types to store whole numbers, each with distinct storage size and value range.
• Choosing the appropriate type depends on the range of values your data needs to represent and how much storage space you want to optimize.

Unsigned Variants

• Each integer type also has an unsigned variant that can only store non-negative values.
• Unsigned types offer a wider range for positive numbers within the same storage size.

Key Points

• Storage Efficiency: Use the smallest type that can accommodate your data's value range to save storage space, especially for large datasets.
• Range Considerations: If you anticipate very large or small numbers, choose bigint or tinyint accordingly.
• MySQL-Specific: MEDIUMINT is not part of the SQL standard and might not be portable across different database systems.

Example

Imagine storing product IDs:

• If your product IDs will never exceed 255, tinyint is a good choice (efficient storage).
• If you expect a wider range (up to 65,535 IDs), smallint would be suitable.
• But for millions of products, int or bigint would be necessary.

Best Practices

• Carefully analyze the expected range of your data before selecting an integer type.
• If unsure, start with int as the default and adjust later if needed.
• Consider database portability if using MEDIUMINT.

tinyint:

CREATE TABLE Products (
  id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  category_id TINYINT NOT NULL,
  FOREIGN KEY (category_id) REFERENCES Categories(id)
);

In this example:

• id is a tinyint UNSIGNED AUTO_INCREMENT primary key, ensuring unique, non-negative product IDs within the range of 0 to 255 (ideal for small product sets).
• category_id is also a tinyint, assuming category IDs won't exceed 255.

smallint:

CREATE TABLE Users (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  age SMALLINT
);

Here:

• id is a smallint UNSIGNED AUTO_INCREMENT primary key, suitable for user IDs that likely won't surpass 32,767.
• age is a smallint to store user ages, assuming they'll fall within the range of -32,768 to 32,767 (though this might need adjustment depending on your application).

mediumint (MySQL-specific):

CREATE TABLE Orders (
  id MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id MEDIUMINT UNSIGNED NOT NULL,
  order_date DATE NOT NULL,
  FOREIGN KEY (user_id) REFERENCES Users(id)
);

While not part of the SQL standard:

• id is a mediumint UNSIGNED AUTO_INCREMENT primary key, offering a wider range for order IDs if you anticipate a large number of orders.
• user_id is also mediumint UNSIGNED, assuming it references user IDs within the same table.

int (default):

CREATE TABLE Posts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  author_id INT UNSIGNED NOT NULL,
  FOREIGN KEY (author_id) REFERENCES Users(id)
);

• id is an int UNSIGNED AUTO_INCREMENT primary key, the default type, offering a good balance for most scenarios.
• author_id is also an int UNSIGNED, assuming it references user IDs with potentially more than 2 billion users (though this might be an overestimate in practice).

bigint:

CREATE TABLE Comments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  post_id BIGINT UNSIGNED NOT NULL,
  content TEXT NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  FOREIGN KEY (post_id) REFERENCES Posts(id),
  FOREIGN KEY (user_id) REFERENCES Users(id)
);

• id is a bigint UNSIGNED AUTO_INCREMENT primary key, ideal for storing very large numbers of comments (e.g., millions or billions).
• post_id and user_id are also bigint UNSIGNED to accommodate potential references to a vast number of posts and users.

DECIMAL/NUMERIC:

• If you need to store floating-point numbers (numbers with decimal places) or require high precision for calculations, consider using DECIMAL or NUMERIC data types.
• These types offer more control over the number of decimal places and overall precision compared to integer types.

CREATE TABLE Products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  price DECIMAL(10, 2) NOT NULL,  -- Stores prices with up to 2 decimal places
  ...
);

VARCHAR/CHAR (for String Representation):

• In rare cases, if you need to store numerical data as strings (e.g., for compatibility with external systems), you could use VARCHAR or CHAR.
• However, this approach is generally discouraged as it loses the benefits of numerical data types (e.g., efficient storage, faster calculations).
• It's usually preferable to convert the data back to a numerical format when performing calculations within the database.

Custom Data Types (User-Defined Functions):

• For highly specialized scenarios, MySQL allows creating user-defined functions (UDFs) to define custom data types.
• This approach is very advanced and requires in-depth knowledge of MySQL internals. It's generally not recommended unless absolutely necessary.

Remember:

• Choose the method that best suits your data representation and processing needs.
• Prioritize using integer types for numerical data whenever possible for storage efficiency and performance benefits.
• Explore DECIMAL/NUMERIC for floating-point numbers with controlled precision.
• Only consider string representation (VARCHAR/CHAR) as a last resort if you have specific compatibility requirements.