INT

Learning Focus

Use this lesson to understand INT with practical syntax and examples.

In MySQL, the INT data type is used to store integer (whole number) values. It is one of the most commonly used numeric data types for storing integers.

Key features of INT in MySQL:

• Range: The range depends on whether you specify SIGNED or UNSIGNED:
  • SIGNED (default): -2,147,483,648 to 2,147,483,647
  • UNSIGNED: 0 to 4,294,967,295
• Storage size: Typically 4 bytes (32 bits)
• Usage: Suitable for storing small to large integer values, such as IDs, counts, or numeric codes.

Variants of INT:

MySQL provides several integer types with different storage sizes and ranges:

• TINYINT (1 byte)
• SMALLINT (2 bytes)
• MEDIUMINT (3 bytes)
• INT or INTEGER (4 bytes)
• BIGINT (8 bytes)

Example:

CREATE TABLE example (
    id INT NOT NULL,
    age SMALLINT UNSIGNED,
    balance BIGINT
);

Summary:

• INT is a 4-byte integer type.
• Default is SIGNED, but you can specify UNSIGNED.
• Use INT when you need to store whole numbers within its range.

Certainly! Here's a simple example of creating a table with an INT data type in MySQL, along with inserting and retrieving data:

-- Create a table named 'employees'
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    salary BIGINT UNSIGNED
);

-- Insert some data into the table
INSERT INTO employees (name, age, salary) VALUES
('Alice', 30, 50000),
('Bob', 45, 75000),
('Charlie', 28, 48000);

-- Retrieve data from the table
SELECT * FROM employees;

Explanation:

• employee_id uses INT with AUTO_INCREMENT to automatically generate unique IDs.
• name is a variable-length string.
• age is an INT to store the employee's age.
• salary is a BIGINT UNSIGNED to store larger salary values without negative numbers.

Result:

employee_id	name	age	salary
1	Alice	30	50000
2	Bob	45	75000
3	Charlie	28	48000

MySQL INT Data Type: Comprehensive Guide

What is INT?

The INT (or INTEGER) data type in MySQL is a 4-byte (32-bit) signed integer used to store whole numbers (positive, negative, or zero). It is one of the most commonly used numeric data types in databases.

Key Characteristics of INT

Feature	Details
Storage Size	4 bytes (32 bits)
Signed Range	-2,147,483,648 to 2,147,483,647
Unsigned Range	0 to 4,294,967,295
Synonyms	INTEGER (identical to INT in MySQL)
Default Value	NULL (if not specified)

---

How to Define INT in MySQL

Basic Syntax

column_name INT [OPTIONS]

Available Options

Option	Description	Example
UNSIGNED	Only allows positive numbers (doubles the max value)	INT UNSIGNED
ZEROFILL	Pads numbers with leading zeros (deprecated in MySQL 8.0+)	INT(5) ZEROFILL -> 00123
AUTO_INCREMENT	Automatically increases value (used for primary keys)	INT AUTO_INCREMENT PRIMARY KEY

---

When to Use INT?

[OK] Best for:

• Primary keys (id INT AUTO_INCREMENT PRIMARY KEY)
• Foreign keys (linking tables)
• Storing whole numbers (e.g., age, quantity, year)
• Counting systems (e.g., page views, user scores)

[X] Avoid for:

• Decimal numbers (use DECIMAL or FLOAT)
• Very small numbers (use TINYINT for 1-byte storage)
• Extremely large numbers (use BIGINT for 8-byte storage)

---

Practical Examples

Example 1: Creating a Table with INT

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    age INT UNSIGNED NOT NULL,
    login_count INT DEFAULT 0
);

• user_id -> Auto-incremented primary key
• age -> Only positive numbers (no negative ages)
• login_count -> Starts at 0, increments with each login

Example 2: Inserting & Querying INT Values

-- Insert data
INSERT INTO users (age, login_count) VALUES (25, 5);

-- Query data
SELECT * FROM users WHERE age > 20;

Example 3: INT with ZEROFILL (Legacy Usage)

(Note: ZEROFILL is deprecated in MySQL 8.0 but still works)

CREATE TABLE products (
    product_code INT(5) ZEROFILL  -- Displays '00123' instead of '123'
);
INSERT INTO products (product_code) VALUES (123);

Output:

product_code

---

00123

---

---

INT vs Other Integer Types

Type	Storage	Signed Range	Unsigned Range	Best For
TINYINT	1 byte	-128 to 127	0 to 255	Small numbers (e.g., status flags)
SMALLINT	2 bytes	-32,768 to 32,767	0 to 65,535	Medium-range numbers
MEDIUMINT	3 bytes	-8,388,608 to 8,388,607	0 to 16,777,215	Larger numbers (but rarely used)
INT	4 bytes	-2.1B to 2.1B	0 to 4.2B	Most common integer storage
BIGINT	8 bytes	-9.2Q to 9.2Q	0 to 18.4Q	Extremely large numbers

---

Common Issues & Best Practices

** Potential Problems**

1. Integer Overflow
   • If you exceed 2,147,483,647 (signed), it wraps to 2,147,483,648.
   • Fix: Use BIGINT if expecting very large numbers.
2. Performance Impact
   • INT is faster than VARCHAR for indexing.
   • Best Practice: Use INT for primary/foreign keys instead of strings.
3. ZEROFILL Deprecation
   • MySQL 8.0+ discourages ZEROFILL.
   • Alternative: Use LPAD() for zero-padding in queries.

[OK] Best Practices

Yes Always use UNSIGNED if negative values aren't needed.

Yes Prefer INT over VARCHAR for numeric IDs (better indexing).

Yes Use AUTO_INCREMENT for primary keys.

---

Summary

• INT is the default choice for whole numbers in MySQL.
• Signed range: 2.1B to 2.1B
• Unsigned range: 0 to 4.2B
• Best for: IDs, counters, and any medium-to-large whole numbers.

---

Next Topic Suggestion

• TINYINT (1-byte integer)
• SMALLINT (2-byte integer)
• Or another section (e.g., SELECT Statement)?

MySQL INT Data Type

INT Overview

• Storage: 4 bytes (32-bit signed integer)
• Range: -2,147,483,648 to 2,147,483,647
• Synonyms: INTEGER (exactly equivalent in MySQL)
• Zero-fill: Can be specified with ZEROFILL attribute

Declaration Syntax

column_name INT[(display_width)] [UNSIGNED] [ZEROFILL] [AUTO_INCREMENT]

Key Attributes

a. UNSIGNED

• Range: 0 to 4,294,967,295

• Storage: Same 4 bytes, different range

• Example:

  CREATE TABLE users (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
  );
  

b. AUTO_INCREMENT

• Automatically generates sequential numbers

• Typically used for primary keys

• Example:

  CREATE TABLE products (
      product_id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100)
  );
  

Storage Requirements

Type	Storage	Signed Range	Unsigned Range
INT	4 bytes	-2^31 to 2^31-1	0 to 2^32-1
INTEGER	4 bytes	(Same as INT)	(Same as INT)

Practical Examples

a. Basic Table Creation

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    age INT UNSIGNED NOT NULL
);

b. With Display Width (deprecated in MySQL 8.0+)

CREATE TABLE demo (
    num INT(5) ZEROFILL  -- Displays 00042 for value 42
);

INT Operations

a. Arithmetic Operations

SELECT (MAX_INT + 1) FROM table;  -- Causes overflow for signed INT

b. Type Conversion

SELECT CAST('123' AS SIGNED INTEGER);  -- String to INT

Performance Considerations

• Indexing: INT is optimal for primary/foreign keys
• Storage: More efficient than VARCHAR for numeric IDs
• Comparison: Faster than string-based numbers in WHERE clauses

Common Use Cases

1. Primary keys (often with AUTO_INCREMENT)
2. Foreign key relationships
3. Counters or quantity fields
4. Age, year, or other whole number values

Limitations and Warnings

• Overflow: Exceeding max value wraps around (2147483647 + 1 = -2147483648)
• Storage: Uses fixed 4 bytes regardless of stored value size
• Display Width: Doesn't constrain storage, only display formatting

INT vs Other Integer Types

Type	Storage	Range (Signed)	When to Use
TINYINT	1 byte	-128 to 127	Very small numbers
SMALLINT	2 bytes	-32,768 to 32,767	Medium-range numbers
MEDIUMINT	3 bytes	-8M to 8M	Between SMALLINT and INT
INT	4 bytes	-2B to 2B	Most common integer
BIGINT	8 bytes	-9Q to 9Q	Very large numbers

Concept Map

flowchart LR
    A[Schema Context] --> B[INT]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

CREATE TABLE sample_int (id INT PRIMARY KEY, value INT);
DESCRIBE sample_table;
SHOW CREATE TABLE sample_table;

What's Next

• Next: TINYINT - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.