MySQL INT Data Type

The INT (short for integer) data type in MySQL is used to store whole numbers (i.e., numbers without decimal places). It is one of the most commonly used data types for storing numeric data like counts, ages, and other integral values.

1. Syntax of INT

INT[(M)] [UNSIGNED] [ZEROFILL]

Where:

M is an optional display width (it does not affect the storage or range of the integer but can affect how values are displayed). The default is 11.
UNSIGNED makes the integer only able to store positive values (i.e., zero and above).
ZEROFILL pads the display of the number with zeros to the left.

2. Range of INT

The INT data type stores 4-byte integers, and its range differs depending on whether it is signed (default) or unsigned.

Signed INT (default):

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

Unsigned INT:

Range: 0 to 4,294,967,295

3. Using INT in Table Definitions

You can define a column as INT when creating or altering a table:

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

The id column is an AUTO_INCREMENT integer, which automatically increments with each new record.
The age column can store both negative and positive integers (signed INT).
The score column is unsigned, meaning it can only store positive values (0 and above).

4. INT Data Type Examples

Inserting Data into INT Column

INSERT INTO users (age, score) VALUES (25, 1500);
INSERT INTO users (age, score) VALUES (30, 2000);

Selecting Data

SELECT * FROM users;

This query will return the rows with age and score values as integers.

5. INT with Optional Attributes

Using UNSIGNED

When defining an INT column as UNSIGNED, it ensures the column will only accept positive integers (no negative values). Here's an example:

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    quantity INT UNSIGNED,
    price INT UNSIGNED
);

In this example:

quantity and price can only have positive values.
id is also UNSIGNED and will store only positive integers.

Using ZEROFILL

The ZEROFILL attribute pads the integer with zeros to the left for display purposes. This does not affect the actual storage of the value, only how it is shown:

CREATE TABLE items (
    item_id INT ZEROFILL
);

For example, when inserting a value like 5:

INSERT INTO items (item_id) VALUES (5);

The value would be displayed as 0000000005 if the display width (M) is set to 10, with leading zeros.

6. INT vs. Other Integer Types

MySQL provides several integer types, and choosing the right one depends on the range and size of values you need to store:

Type	Bytes	Range (Signed)	Range (Unsigned)
TINYINT	1 byte	-128 to 127	0 to 255
SMALLINT	2 bytes	-32,768 to 32,767	0 to 65,535
MEDIUMINT	3 bytes	-8,388,608 to 8,388,607	0 to 16,777,215
INT	4 bytes	-2,147,483,648 to 2,147,483,647	0 to 4,294,967,295
BIGINT	8 bytes	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	0 to 18,446,744,073,709,551,615

When to Use Which Type?

Use TINYINT for small numbers (like flags or small counts).
Use SMALLINT or MEDIUMINT for moderate-sized integer values.
Use INT for most general-purpose use cases.
Use BIGINT for very large integers (e.g., high counts or large identifiers).

7. Arithmetic Operations with INT

You can perform various arithmetic operations with INT columns, including addition, subtraction, multiplication, and division.

Example:

SELECT age + 5 AS new_age FROM users;

This query will return the age value from the users table incremented by 5.

8. INT and Auto Increment

The AUTO_INCREMENT feature automatically generates unique integer values for a column. By default, it starts from 1 and increments by 1 for each new record, but you can customize the starting value.

Example:

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

The emp_id column will automatically generate an increasing integer value each time a new record is inserted.

9. INT Best Practices

Choose the appropriate integer type: If your data can be stored in a smaller integer type (like TINYINT or SMALLINT), use it to save storage space.
Use UNSIGNED for non-negative integers: For fields that will only contain non-negative values (e.g., quantities, counts), use the UNSIGNED modifier to double the upper limit.
Avoid large integer sizes for small data: Don't use BIGINT unnecessarily for small data; it takes up more storage and is less efficient.
For Auto Increment, INT is often ideal, as it provides a large enough range for most applications.

10. Summary

INT is the most commonly used integer data type in MySQL.
It can store signed or unsigned whole numbers.
INT can store values in the range from -2,147,483,648 to 2,147,483,647 (signed) or 0 to 4,294,967,295 (unsigned).
It is ideal for storing counts, ages, or any other whole numbers.
Use UNSIGNED for positive-only integers and ZEROFILL for padded integers for display purposes.

Would you like to see more examples of using INT with different ranges or attributes?

Souy Soeng

MySQL INT Data Type