Choosing Integer Types in MySQL & PostgreSQL

Choosing the right integer type depends on storage size, range, and performance. Here’s a guide to selecting the best type based on your needs. Integer Types Overview Data Type MySQL Range (Signed) PostgreSQL Range (Signed) Storage (Bytes) Best Used For TINYINT -128 to 127 (UNSIGNED: 0 to 255) ❌ Not available 1 Boolean values, small counters SMALLINT -32,768 to 32,767 -32,768 to 32,767 2 Small IDs, counts MEDIUMINT -8,388,608 to 8,388,607 ❌ Not available 3 Large counters in MySQL INT / INTEGER -2,147,483,648 to 2,147,483,647 Same as MySQL 4 Most general purpose ID or counter BIGINT -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 Same as MySQL 8 Large IDs, timestamps When to Use Each Integer Type? ✅ Use TINYINT (MySQL) when: ✔ Boolean values (0 or 1) ✔ Small flags or statuses (1-100) ✔ Example: CREATE TABLE users ( is_active TINYINT(1) NOT NULL ); ✅ Use SMALLINT when: ✔ Small numerical values, such as age, small counts, or ratings (-32,768 to 32,767) ✔ Example: CREATE TABLE products ( stock SMALLINT UNSIGNED NOT NULL -- Only positive values (0-65,535) ); ✅ Use MEDIUMINT (MySQL only) when: ✔ Larger counters than SMALLINT, but INT is overkill ✔ Example: CREATE TABLE orders ( order_count MEDIUMINT UNSIGNED NOT NULL ); ✅ Use INT / INTEGER when: ✔ General-purpose primary keys (auto-increment IDs) ✔ Larger counters (-2B to 2B range) ✔ Example: CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, salary INT NOT NULL ); ✅ Use BIGINT when: ✔ Storing timestamps, large IDs, or very large numbers ✔ Needed for social media users, financial records, or global IDs ✔ Example: CREATE TABLE transactions ( transaction_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, amount BIGINT NOT NULL ); Should I Use SIGNED or UNSIGNED? Use UNSIGNED if you only need positive numbers (doubles the max value). Use SIGNED if you need both positive & negative values. Example: -- UNSIGNED: allows 0 to 4,294,967,295 CREATE TABLE accounts ( balance INT UNSIGNED NOT NULL ); PostgreSQL-Specific Notes No TINYINT or MEDIUMINT → Use SMALLINT or INTEGER instead. Supports SERIAL types for auto-incrementing IDs: CREATE TABLE users ( id SERIAL PRIMARY KEY -- Equivalent to INT AUTO_INCREMENT ); Choosing the Right Integer Type Use Case MySQL Type PostgreSQL Type Boolean flags (0/1) TINYINT(1) BOOLEAN Small numbers (age, ratings) SMALLINT SMALLINT Medium counters MEDIUMINT INTEGER General IDs & primary keys INT AUTO_INCREMENT SERIAL Large counters (big user base, financial data) BIGINT BIGINT Here are real-world use cases for each integer type. 1️⃣ TINYINT (1 Byte) – Small Flags & Boolean Values

   CREATE TABLE users (
       is_active TINYINT(1) NOT NULL
   );

   CREATE TABLE products (
       stock SMALLINT UNSIGNED NOT NULL  -- Only positive values (0-65,535)
   );

   CREATE TABLE orders (
       order_count MEDIUMINT UNSIGNED NOT NULL
   );

   CREATE TABLE employees (
       id INT AUTO_INCREMENT PRIMARY KEY,
       salary INT NOT NULL
   );

   CREATE TABLE transactions (
       transaction_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       amount BIGINT NOT NULL
   );

  -- UNSIGNED: allows 0 to 4,294,967,295
  CREATE TABLE accounts (
      balance INT UNSIGNED NOT NULL
  );

  CREATE TABLE users (
      id SERIAL PRIMARY KEY  -- Equivalent to INT AUTO_INCREMENT
  );

Read More

Tags:

Previous Article

"Revolutionizing Quantum Error Correction: Meet Micro Blossom's Breakthrough!"

Next Article

Por que você deve repensar o uso de Regex em validações de strings no Go

Related Posts

PostgreSQL basics & clauses.

Mar 26, 2025  0

React Hooks Tutorial: A Beginner’s Guide to Modern Reac...

Feb 20, 2025  0

Linux 101: Part 4 - Mastering Package Management: A Gui...

Feb 15, 2025  0

Comments

Name

Email

Comment

Post Comment