MySQL INT Data Type

Introduction

In this chapter, we will learn about the INT data type in MySQL. The INT data type is used to store integer values. It is one of the most commonly used data types for storing numeric data. Understanding how to use INT effectively can help optimize storage and improve query performance. We will cover the syntax for defining INT columns, examples, and important considerations for using INT in MySQL.

Syntax

The basic syntax for defining an INT column in MySQL is:

INT[(length)] [UNSIGNED] [ZEROFILL]

• length: Specifies the display width of the integer. This is optional and does not affect the storage size.
• UNSIGNED: Specifies that the integer cannot be negative. This doubles the upper range of positive values.
• ZEROFILL: Pads the integer with zeros up to the display width.

Example

Here is an example of how to define an INT column in a table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    age INT,
    salary INT(10) UNSIGNED,
    employee_number INT(5) ZEROFILL
);

In this example, age is a standard INT column, salary is an UNSIGNED INT, and employee_number is a ZEROFILL INT.

Full Example

Let’s go through a complete example where we create a database and a table with INT columns, insert data, and demonstrate their usage.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Employees Table with INT Columns:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    age INT,
    salary INT(10) UNSIGNED,
    employee_number INT(5) ZEROFILL
);

4. Insert Data into the Employees Table:

INSERT INTO employees (first_name, last_name, age, salary, employee_number) VALUES
('Rahul', 'Sharma', 30, 50000, 23),
('Priya', 'Singh', 28, 60000, 7),
('Amit', 'Kumar', 35, 70000, 45),
('Neha', 'Verma', 25, 55000, 123),
('Sahil', 'Mehta', 40, 80000, 3);

5. Query the Employees Table:

SELECT * FROM employees;

Output

In this example, the age column is a standard INT, the salary column is an UNSIGNED INT, and the employee_number column is a ZEROFILL INT.

Important Considerations

• Range and Storage: The INT data type has a range of -2,147,483,648 to 2,147,483,647 for signed integers and 0 to 4,294,967,295 for unsigned integers. It requires 4 bytes of storage.
• Performance: Using the appropriate integer type (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT) based on the data range can help optimize storage and performance.
• UNSIGNED: Use UNSIGNED for columns that should not have negative values, such as quantities, IDs, and other counts.
• ZEROFILL: The ZEROFILL attribute is useful for displaying numbers with leading zeros, but it does not affect the stored value.

Conclusion

The INT data type is versatile and widely used in MySQL for storing integer values. This chapter covered the syntax for defining INT columns, provided a full example with different integer attributes, and discussed important considerations.