Integer types (TINYINT, INT, BIGINT) in MySQL - Step-by-Step Execution

Concept Flow - Integer types (TINYINT, INT, BIGINT)

Choose Integer Type

↓

TINYINT?

Yes→Range: -128 to 127

|No↓

INT?

Yes→Range: -2,147,483,648 to 2,147,483,647

|No↓

BIGINT

→Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

↓

Store Value

↓

Retrieve Value

↓

Use in Queries

This flow shows how MySQL chooses integer types based on size and stores values within their range.

Execution Sample

MySQL

CREATE TABLE example (
  small_num TINYINT,
  normal_num INT,
  big_num BIGINT
);

INSERT INTO example VALUES (127, 2147483647, 9223372036854775807);

Creates a table with three integer columns of different sizes and inserts the maximum values for each type.

Execution Table

Step	Action	Value Inserted	Type	Stored Value	Notes
1	Insert 127 into small_num	127	TINYINT	127	Within TINYINT range (-128 to 127)
2	Insert 2147483647 into normal_num	2147483647	INT	2147483647	Within INT range (-2,147,483,648 to 2,147,483,647)
3	Insert 9223372036854775807 into big_num	9223372036854775807	BIGINT	9223372036854775807	Within BIGINT range (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)
4	Attempt to insert 128 into small_num	128	TINYINT	Error	128 exceeds TINYINT max 127, causes overflow error
5	Attempt to insert 2147483648 into normal_num	2147483648	INT	Error	2147483648 exceeds INT max 2147483647, causes overflow error
6	Retrieve values	N/A	N/A	127, 2147483647, 9223372036854775807	Values stored correctly

💡 Execution stops on overflow errors when inserting values outside type ranges.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
small_num	NULL	127	127	127	Error (overflow)	Error (overflow)	127 (last valid)
normal_num	NULL	NULL	2147483647	2147483647	2147483647	Error (overflow)	2147483647 (last valid)
big_num	NULL	NULL	NULL	9223372036854775807	9223372036854775807	9223372036854775807	9223372036854775807

Key Moments - 3 Insights

Why does inserting 128 into a TINYINT column cause an error?

Because TINYINT can only store values from -128 to 127. Step 4 in the execution_table shows the overflow error when 128 is inserted.

Can INT store values larger than 2,147,483,647?

No, INT's maximum is 2,147,483,647. Step 5 shows an error when trying to insert 2,147,483,648.

Why use BIGINT instead of INT?

BIGINT stores much larger numbers (up to about 9 quintillion). Step 3 shows storing the maximum BIGINT value successfully.

Visual Quiz - 3 Questions

Look at the execution_table, what is the stored value in 'small_num' after step 1?

A 127

B 128

C Error

D NULL

💡 Hint

Check the 'Stored Value' column in row for step 1.

At which step does inserting a value cause an overflow error for the INT type?

A Step 2

B Step 4

C Step 5

D Step 3

💡 Hint

Look for 'Error' in the 'Stored Value' column for INT type.

If you insert -129 into a TINYINT column, what would happen?

A Value stored successfully

B Overflow error like in step 4

C Value stored as 127

D Value stored as 0

💡 Hint

TINYINT range is -128 to 127, so values outside cause overflow errors.

Concept Snapshot

Integer Types in MySQL:
- TINYINT: 1 byte, range -128 to 127
- INT: 4 bytes, range -2,147,483,648 to 2,147,483,647
- BIGINT: 8 bytes, range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Values outside these ranges cause overflow errors.
Choose type based on needed number size.

Full Transcript

This visual execution shows how MySQL stores integer values in TINYINT, INT, and BIGINT columns. It starts by creating a table with these three types. Then it inserts the maximum allowed values for each type successfully. When trying to insert values outside the allowed range, such as 128 into TINYINT or 2147483648 into INT, MySQL raises overflow errors. The variable tracker shows how values change or errors occur step by step. Key moments clarify why values outside ranges cause errors and why BIGINT is used for very large numbers. The quiz tests understanding of stored values and overflow points. The snapshot summarizes the ranges and behavior of these integer types.

Practice

1. Which MySQL integer type should you use to store a small number between 0 and 255 efficiently?

easy

A. BIGINT

B. INT

C. TINYINT

D. SMALLINT

Solution

1. Step 1: Understand the range of TINYINT and compare with other types

   TINYINT can store values from 0 to 255 when unsigned, which fits small numbers efficiently. INT and BIGINT store much larger numbers but use more space. SMALLINT also uses more space unnecessarily.

2. Final Answer:

   TINYINT -> Option C

3. Quick Check:

   Small numbers (0-255) = TINYINT [OK]

Hint: Use TINYINT for numbers up to 255 to save space [OK]

Common Mistakes:

• Choosing INT for small numbers wastes space
• Confusing TINYINT with SMALLINT
• Not considering unsigned range

2. Which of the following is the correct syntax to declare a NOT NULL BIGINT column named user_id in MySQL?

easy

A. user_id BIGINT;

B. BIGINT user_id;

C. user_id INT BIG;

D. user_id BIGINT NOT NULL;

Solution

1. Step 1: Check correct order of declaration

   In MySQL, the column name comes first, then the data type, optionally followed by constraints like NOT NULL. Only A follows this: user_id BIGINT NOT NULL;.

2. Final Answer:

   user_id BIGINT NOT NULL; -> Option D

3. Quick Check:

   Column name + type + constraints = correct syntax [OK]

Hint: Column name first, then type, then constraints [OK]

Common Mistakes:

• Putting type before column name
• Using invalid order of keywords
• Missing semicolon in some contexts

3. Given the table orders with a column order_count TINYINT UNSIGNED, what will be the result of this query?

INSERT INTO orders (order_count) VALUES (300);
SELECT order_count FROM orders;

medium

A. 300

B. Error: Out of range value

C. 0

D. 44

Solution

1. Step 1: Understand TINYINT UNSIGNED range and insert effect

   TINYINT UNSIGNED stores 0-255 only. 300 exceeds range, so INSERT throws 'Error: Out of range value' (default strict mode).

2. Final Answer:

   Error: Out of range value -> Option B

3. Quick Check:

   Value > 255 in TINYINT UNSIGNED causes error [OK]

Hint: Check value fits type range before insert [OK]

Common Mistakes:

• Assuming values wrap around silently
• Ignoring unsigned range limits
• Expecting automatic truncation without error

4. You have a column defined as INT but you want to store numbers larger than 4,294,967,295. Which change fixes the problem?

medium

A. Change the column type to BIGINT

B. Change the column type to TINYINT

C. Add UNSIGNED to the INT column

D. Use VARCHAR instead of INT

Solution

1. Step 1: Understand INT range limits and select proper type

   Signed INT max ~2.1 billion, UNSIGNED INT max 4,294,967,295. BIGINT handles much larger (up to ~9 quintillion).

2. Final Answer:

   Change the column type to BIGINT -> Option A

3. Quick Check:

   BIGINT stores bigger numbers than INT [OK]

Hint: Use BIGINT for numbers bigger than INT max [OK]

Common Mistakes:

• Using TINYINT which is smaller
• Adding UNSIGNED but still too small
• Switching to VARCHAR loses numeric benefits

5. You want to store user scores that can be very large, but you also want to save space when scores are small. Which MySQL integer type strategy is best?

hard

A. Use INT for most scores and BIGINT only if scores exceed INT range

B. Use TINYINT and switch to BIGINT if needed later

C. Always use BIGINT for all scores

D. Use BIGINT but compress data manually

Solution

1. Step 1: Balance storage and range needs

   TINYINT too small; always BIGINT wastes space. Use INT initially (efficient for small/medium), ALTER to BIGINT later if needed.

2. Final Answer:

   Use INT for most scores and BIGINT only if scores exceed INT range -> Option A

3. Quick Check:

   Balance size and range with INT then BIGINT [OK]

Hint: Start with INT, upgrade to BIGINT if needed [OK]

Common Mistakes:

• Always using BIGINT wastes space
• Starting with TINYINT causes overflow
• Manual compression is complex and error-prone