Numeric Data Types0%

Numeric Data Types

Beginner12 min read•Updated: 2026-09-12

Numeric Data Types: Integers, Decimals, and Floating-Point

Choosing the correct numeric data type is one of the most critical decisions in database engineering. An incorrect choice can lead to subtle financial calculation bugs (such as 0.1 + 0.2 equaling 0.30000000000000004) or waste hundreds of gigabytes of server RAM and SSD storage across billions of rows.

1. Exact Integers in MySQL

MySQL provides five distinct integer types depending on the range of numbers you need to store:

Type	Storage Bytes	Signed Range	Unsigned Range	Common Use Case
`TINYINT`	1 byte	-128 to 127	0 to 255	Age, status codes, flags.
`SMALLINT`	2 bytes	-32,768 to 32,767	0 to 65,535	Year, country codes, items in cart.
`MEDIUMINT`	3 bytes	-8,388,608 to 8,388,607	0 to 16,777,215	Zip codes, customer IDs for medium apps.
`INT` / `INTEGER`	4 bytes	-2.14 billion to +2.14 billion	0 to 4.29 billion	Standard primary keys, order IDs.
`BIGINT`	8 bytes	-9.22 quintillion to +9.22 quintillion	0 to 18.44 quintillion	Global payment transactions, analytics logs.

The `UNSIGNED` Keyword

If a column will never store negative values (e.g., student age, product price, quantity in stock), designate it as UNSIGNED. This doubles the positive range without consuming any additional disk space!

1CREATE TABLE product_inventory (
2    product_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
3    stock_quantity SMALLINT UNSIGNED NOT NULL,
4    reorder_threshold TINYINT UNSIGNED NOT NULL DEFAULT 5
5);

2. Fixed-Point vs Floating-Point: Financial Accuracy

1   DECIMAL(M, D)  ---------> EXACT STORAGE (Base-10 math)  <-- USE FOR MONEY!
2   FLOAT / DOUBLE ---------> APPROXIMATE (IEEE 754 Binary) <-- USE FOR SCIENTIFIC DATA!

`DECIMAL(M, D)` (Exact Numeric)

M (Precision): Total number of digits (1 to 65).
D (Scale): Number of digits to the right of the decimal point (0 to 30).
DECIMAL(10, 2) can store up to 8 digits before the dot and 2 digits after: -99999999.99 to +99999999.99.
Stored as binary-encoded decimal digits with zero rounding error. Always use DECIMAL for prices, salaries, currency, and taxes!

`FLOAT` & `DOUBLE` (Approximate Numeric)

FLOAT (4 bytes): ~7 decimal digits of precision.
DOUBLE (8 bytes): ~15 decimal digits of precision.
Implements IEEE 754 floating-point arithmetic. Numbers like 0.1 cannot be represented with absolute exactness in binary, leading to rounding discrepancies. Use strictly for scientific measurements, GPS coordinates, or game coordinates where speed outweighs microscopic rounding precision.

1CREATE TABLE account_ledger (
  entry_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  transaction_amount DECIMAL(12, 4) NOT NULL, -- Exact to 4 decimal places
  currency_exchange_rate DECIMAL(8, 6) NOT NULL,
  latitude DOUBLE, -- GPS coordinates (approximate is fine)
  longitude DOUBLE
7);

3. Best Practices & Common Pitfalls

Never Use FLOAT for Currency: Storing rupees, dollars, or cryptocurrency in FLOAT or DOUBLE will result in reconciliation errors during financial audits.
The Meaning of INT(11) in MySQL: The number inside parentheses in INT(11) or INT(4) is only a display width hint used with ZEROFILL. It does not limit the maximum value or storage size! An INT(4) can still store 2,000,000,000. In MySQL 8.0.19+, integer display widths are officially deprecated.
Choose the Smallest Viable Integer: Using BIGINT for a column that will never exceed 100 entries wastes 7 bytes per row. Across 100 million rows, that is 700 MB of wasted RAM!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which numeric data type MUST be used when storing monetary values like product prices and salaries in MySQL?

Question 2

How many bytes of storage does a standard signed INT column occupy in MySQL?

Question 3

What does the UNSIGNED keyword accomplish on an integer column?

Question 4

In DECIMAL(8, 2), what is the maximum number of digits allowed before the decimal point?

Question 5

What did the specification INT(5) historically indicate in MySQL versions prior to 8.0.19?

Next Lesson

String & Text Types

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Database Metadata & Server Status	String & Text Types

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

String & Text Types

Numeric Data Types

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Numeric Data Types: Integers, Decimals, and Floating-Point

1. Exact Integers in MySQL

MySQL provides five distinct integer types depending on the range of numbers you need to store:

Type	Storage Bytes	Signed Range	Unsigned Range	Common Use Case
`TINYINT`	1 byte	-128 to 127	0 to 255	Age, status codes, flags.
`SMALLINT`	2 bytes	-32,768 to 32,767	0 to 65,535	Year, country codes, items in cart.
`MEDIUMINT`	3 bytes	-8,388,608 to 8,388,607	0 to 16,777,215	Zip codes, customer IDs for medium apps.
`INT` / `INTEGER`	4 bytes	-2.14 billion to +2.14 billion	0 to 4.29 billion	Standard primary keys, order IDs.
`BIGINT`	8 bytes	-9.22 quintillion to +9.22 quintillion	0 to 18.44 quintillion	Global payment transactions, analytics logs.

The `UNSIGNED` Keyword

1CREATE TABLE product_inventory (
2    product_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
3    stock_quantity SMALLINT UNSIGNED NOT NULL,
4    reorder_threshold TINYINT UNSIGNED NOT NULL DEFAULT 5
5);

2. Fixed-Point vs Floating-Point: Financial Accuracy

1   DECIMAL(M, D)  ---------> EXACT STORAGE (Base-10 math)  <-- USE FOR MONEY!
2   FLOAT / DOUBLE ---------> APPROXIMATE (IEEE 754 Binary) <-- USE FOR SCIENTIFIC DATA!

`DECIMAL(M, D)` (Exact Numeric)

M (Precision): Total number of digits (1 to 65).
D (Scale): Number of digits to the right of the decimal point (0 to 30).
DECIMAL(10, 2) can store up to 8 digits before the dot and 2 digits after: -99999999.99 to +99999999.99.
Stored as binary-encoded decimal digits with zero rounding error. Always use DECIMAL for prices, salaries, currency, and taxes!

`FLOAT` & `DOUBLE` (Approximate Numeric)

FLOAT (4 bytes): ~7 decimal digits of precision.
DOUBLE (8 bytes): ~15 decimal digits of precision.
Implements IEEE 754 floating-point arithmetic. Numbers like 0.1 cannot be represented with absolute exactness in binary, leading to rounding discrepancies. Use strictly for scientific measurements, GPS coordinates, or game coordinates where speed outweighs microscopic rounding precision.

1CREATE TABLE account_ledger (
  entry_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  transaction_amount DECIMAL(12, 4) NOT NULL, -- Exact to 4 decimal places
  currency_exchange_rate DECIMAL(8, 6) NOT NULL,
  latitude DOUBLE, -- GPS coordinates (approximate is fine)
  longitude DOUBLE
7);

3. Best Practices & Common Pitfalls

Never Use FLOAT for Currency: Storing rupees, dollars, or cryptocurrency in FLOAT or DOUBLE will result in reconciliation errors during financial audits.
The Meaning of INT(11) in MySQL: The number inside parentheses in INT(11) or INT(4) is only a display width hint used with ZEROFILL. It does not limit the maximum value or storage size! An INT(4) can still store 2,000,000,000. In MySQL 8.0.19+, integer display widths are officially deprecated.
Choose the Smallest Viable Integer: Using BIGINT for a column that will never exceed 100 entries wastes 7 bytes per row. Across 100 million rows, that is 700 MB of wasted RAM!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which numeric data type MUST be used when storing monetary values like product prices and salaries in MySQL?

Question 2

How many bytes of storage does a standard signed INT column occupy in MySQL?

Question 3

What does the UNSIGNED keyword accomplish on an integer column?

Question 4

In DECIMAL(8, 2), what is the maximum number of digits allowed before the decimal point?

Question 5

What did the specification INT(5) historically indicate in MySQL versions prior to 8.0.19?

Next Lesson

String & Text Types

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Database Metadata & Server Status	String & Text Types

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Numeric Data Types: Integers, Decimals, and Floating-Point

1. Exact Integers in MySQL

The UNSIGNED Keyword

2. Fixed-Point vs Floating-Point: Financial Accuracy

DECIMAL(M, D) (Exact Numeric)

FLOAT & DOUBLE (Approximate Numeric)

3. Best Practices & Common Pitfalls

Multiple Choice Questions

String & Text Types

Related Lessons

Practice Quiz

Loading Page Content...

Numeric Data Types: Integers, Decimals, and Floating-Point

1. Exact Integers in MySQL

The UNSIGNED Keyword

2. Fixed-Point vs Floating-Point: Financial Accuracy

DECIMAL(M, D) (Exact Numeric)

FLOAT & DOUBLE (Approximate Numeric)

3. Best Practices & Common Pitfalls

Multiple Choice Questions

String & Text Types

Related Lessons

Practice Quiz

The `UNSIGNED` Keyword

`DECIMAL(M, D)` (Exact Numeric)

`FLOAT` & `DOUBLE` (Approximate Numeric)

The `UNSIGNED` Keyword

`DECIMAL(M, D)` (Exact Numeric)

`FLOAT` & `DOUBLE` (Approximate Numeric)