BIGINT

Learning Focus

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

Example of BIGINT

Certainly! The BIGINT data type in MySQL is used to store very large integers. It requires 8 bytes of storage and can hold values within the following ranges:

• Signed: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
• Unsigned: 0 to 18,446,744,073,709,551,615

Example of using BIGINT in a table:

Table: transactions

transaction_id (BIGINT)	user_id (BIGINT)	amount (BIGINT UNSIGNED)	transaction_date
1000000000001	1234567890	5000000000	2025-05-13 10:30:00
1000000000002	9876543210	7500000000	2025-05-13 11:00:00
1000000000003	1234567890	2000000000	2025-05-13 12:15:00
1000000000004	5555555555	10000000000	2025-05-13 13:45:00
1000000000005	2222222222	3000000000	2025-05-13 14:30:00

SQL to create this table:

CREATE TABLE transactions (
    transaction_id BIGINT PRIMARY KEY,
    user_id BIGINT,
    amount BIGINT UNSIGNED,
    transaction_date DATETIME
);

SQL to insert sample data:

INSERT INTO transactions (transaction_id, user_id, amount, transaction_date) VALUES
(1000000000001, 1234567890, 5000000000, '2025-05-13 10:30:00'),
(1000000000002, 9876543210, 7500000000, '2025-05-13 11:00:00'),
(1000000000003, 1234567890, 2000000000, '2025-05-13 12:15:00'),
(1000000000004, 5555555555, 10000000000, '2025-05-13 13:45:00'),
(1000000000005, 2222222222, 3000000000, '2025-05-13 14:30:00');

---

MySQL BIGINT Data Type: Complete Guide

---

What is BIGINT?

BIGINT is MySQL's largest integer data type, designed for storing extremely large whole numbers (both positive and negative). It uses 8 bytes (64 bits) of storage and is ideal for scenarios where numbers exceed the range of INT.

Key Features

Feature	Details
Storage Size	8 bytes (64 bits)
Signed Range	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Unsigned Range	0 to 18,446,744,073,709,551,615
Synonyms	None (unique to MySQL)
Default Value	NULL (if not specified)

---

When to Use BIGINT?

[OK] Best for:

• Systems requiring unique IDs for billions of records (e.g., social media platforms)
• Scientific calculations (e.g., astronomical distances in kilometers)
• Financial systems tracking transactions in fractions of a cent
• Storing large timestamps (nanosecond precision)

[X] Avoid for:

• Small numbers (use TINYINT, SMALLINT, or INT)
• Decimal numbers (use DECIMAL or FLOAT)
• General-purpose tables (unless expecting exponential growth)

---

Syntax & Options

Basic Declaration

column_name BIGINT [OPTIONS]

Available Options

Option	Description	Example
UNSIGNED	Only allows positive values	BIGINT UNSIGNED
AUTO_INCREMENT	Generates sequential numbers	BIGINT AUTO_INCREMENT PRIMARY KEY
ZEROFILL	Pads with leading zeros (deprecated in MySQL 8.0+)	BIGINT(20) ZEROFILL

---

Practical Examples

Example 1: User IDs for Large-Scale Systems

CREATE TABLE users (
    user_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- Supports 18 quintillion users
    username VARCHAR(50)
);

Example 2: Scientific Data Storage

CREATE TABLE galaxy_data (
    galaxy_id INT PRIMARY KEY,
    distance_km BIGINT UNSIGNED  -- 18,446,744,073,709,551,615 km max
);

Example 3: Financial Microtransactions

CREATE TABLE transactions (
    tx_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    amount BIGINT  -- Store cents: $12.34 = 1234
);

---

BIGINT vs Other Integer Types

Type	Storage	Signed Range	Unsigned Range	Best For
INT	4 bytes	-2B to 2B	0 to 4B	Common integers
BIGINT	8 bytes	-9Q to 9Q	0 to 18Q	Extremely large numbers
DECIMAL	Variable	Exact decimals	N/A	Precise fractional values

---

Performance & Limitations

** Key Limitations**

1. Storage Overhead: Uses twice the space of INT (8 bytes vs 4 bytes)
2. Index Size: Larger indexes slow down queries
3. Application Compatibility: Some languages struggle with 64-bit integers

[OK] Best Practices

Yes Use UNSIGNED unless negative values are needed

Yes Reserve for columns that truly require massive ranges

Yes Test application handling of 64-bit integers

---

BIGINT Edge Cases

Maximum Value Test

INSERT INTO huge_numbers (value) VALUES (9223372036854775807);  -- Signed max
INSERT INTO huge_numbers (value) VALUES (18446744073709551615); -- Unsigned max

Overflow Behavior

-- Signed BIGINT overflow wraps to minimum value
SELECT 9223372036854775807 + 1;  -- Returns -9223372036854775808

---

When to Avoid BIGINT

• Small Datasets: A table with 1 million rows doesn't need BIGINT IDs
• Joins: BIGINT foreign keys increase join complexity
• Disk Space: 8 bytes/row adds up in large tables

---

Summary

• BIGINT stores 64-bit integers for astronomical-scale numbers
• Signed range: 9.2 quintillion to 9.2 quintillion
• Unsigned range: 0 to 18.4 quintillion
• Best for: Systems requiring IDs/numbers beyond INT's capacity

---

Next Topic Suggestions

1. FLOAT/DOUBLE (approximate numeric types)
2. BIT (binary data storage)
3. Aggregate Functions (e.g., SUM() for BIGINT columns)

Concept Map

flowchart LR
    A[Schema Context] --> B[BIGINT]
    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_bigint (id INT PRIMARY KEY, value BIGINT);
DESCRIBE sample_table;
SHOW CREATE TABLE sample_table;

What's Next

• Previous: MEDIUMINT - Review the previous lesson to reinforce context.
• Next: DECIMAL - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.