MySQL INT Data Type

Summary: in this tutorial, you will learn about MySQL INT data type, and how to use it to store whole numbers in the databases.

Introduction to MySQL INT data type

In MySQL, INT stands for the integer that represents the whole numbers. An integer can be written without a fractional component such as 1, 100, 4, -10, and it cannot be 1.2, 5/3, etc. An integer can be zero, positive, and negative.

MySQL supports all standard SQL integer types INTEGER or INT and SMALLINT. Additionally, MySQL provides TINYINT MEDIUMINT, and BIGINT as extensions to the SQL standard.

MySQL INT data type can be signed and unsigned. The following table illustrates the characteristics of each integer type including storage in bytes, minimum value, and maximum value.

MySQL INT data type examples

Let’s look at some examples of using the integer data type.

1) Using INT for a column example

Because integer type represents exact numbers, you usually use it as the primary key of a table. In addition, the INT column can have an AUTO_INCREMENT attribute.

When you insert a NULL value or 0 into the INT AUTO_INCREMENT column, the value of the column is set to the next sequence value. Notice that the sequence value starts with 1.

When you insert a value, which is not NULL or zero, into the AUTO_INCREMENT column, the column accepts the value. In addition, the sequence is reset to the next value of the inserted value.

First, create a new table named items with an integer column as the primary key:

CREATE TABLE items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    item_text VARCHAR(255)
);Code language: SQL (Structured Query Language) (sql)

You can use either INT or INTEGER in the CREATE TABLE statement above because they are interchangeable. Whenever you insert a new row into the items table, the value of the item_id column is increased by 1.

Next, the following INSERT statement inserts three rows into the items table.

INSERT INTO 
    items(item_text)
VALUES
    ('laptop'), 
    ('mouse'),
    ('headphone');Code language: SQL (Structured Query Language) (sql)

Then, query data from the items table using the following SELECT statement:

SELECT * FROM items;Code language: SQL (Structured Query Language) (sql)

Output:

+---------+-----------+
| item_id | item_text |
+---------+-----------+
|       1 | laptop    |
|       2 | mouse     |
|       3 | headphone |
+---------+-----------+
3 rows in set (0.00 sec)Code language: JavaScript (javascript)

After that, insert a new row whose value of the item_id column is specified explicitly.

INSERT INTO items(item_id,item_text)
VALUES(10,'Server');Code language: SQL (Structured Query Language) (sql)

Since the current value of the item_id column is 10, the sequence is reset to 11. If you insert a new row, the AUTO_INCREMENT column will use 11 as the next value.

INSERT INTO items(item_text)
VALUES('Router');Code language: SQL (Structured Query Language) (sql)

Finally, query the data of the items table again to see the result.

SELECT * FROM items;Code language: SQL (Structured Query Language) (sql)

Output:

+---------+-----------+
| item_id | item_text |
+---------+-----------+
|       1 | laptop    |
|       2 | mouse     |
|       3 | headphone |
|      10 | Server    |
|      11 | Router    |
+---------+-----------+
5 rows in set (0.00 sec)Code language: JavaScript (javascript)

2) Using INT UNSIGNED example

First, create a table called classes that has the column total_member with the unsigned integer data type:

CREATE TABLE classes (
    class_id INT AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    total_member INT UNSIGNED,
    PRIMARY KEY (class_id)
);
Code language: SQL (Structured Query Language) (sql)

Second, insert a new row into the classes table:

INSERT INTO classes(name, total_member)
VALUES('Weekend',100);Code language: SQL (Structured Query Language) (sql)

It worked as expected.

Third, attempt to insert a negative value into the total_member column:

INSERT INTO classes(name, total_member)
VALUES('Fly',-50);Code language: SQL (Structured Query Language) (sql)

MySQL issued the following error:

Error Code: 1264. Out of range value for column 'total_member' at row 1Code language: SQL (Structured Query Language) (sql)

Note that the display width has been deprecated. Additionally, the ZEROFILL attribute has also been deprecated, and the suggested alternatives are to use LPAD for zero-padding numbers or to store the formatted numbers in a CHAR column.

Summary

• INT represents the integer type.
• MySQL offers various variants of the INT type including TINYINT, SMALLINT, MEDIUMINT, and BIGINT.