Introduction to MySQL INT Type

In MySQL, INT and INTEGER are integer data types. To store numbers of different lengths more efficiently, MySQL supports several different integer data types: INT, SMALLINT, TINYINT, MEDIUMINT and BIGINT.

The following table shows the number of bytes and range of values ​​for different integer types:

INT and INTEGER are synonyms.

MySQL INT syntax

The MySQL INT type is simple to use, as follows:

INT [UNSIGNED]

Here: UNSIGNED attribute identifies this data type as an unsigned integer.

You can also specify display width attribute as the following syntax:

INT(display_width) ZEROFILL

here:

• display_width is the display width attribute of INT.
• The attribute ZEROFILL indicates that MySQL will left pad the value with 0 automatically if the value’s length is less than the display width specified display display_width.
• As of MySQL 8.0.17, both the display-width and ZEROFILL attribute are deprecated and will be removed in a future release.

MySQL INT data type instance

The INT data type column is used to store integers, such as age, quantity, etc. It can also be a primary key column with AUTO_INCREMENT attribute.

Define INT column and insert data

Let’s look at an example of a simple integer column. First we create a demo table :

CREATE TABLE test_int(
    name char(30) NOT NULL,
    age INT NOT NULL
);

You can also use INTEGER instead of INT in the above SQL statement.

Let’s insert two rows :

INSERT INTO test_int (name, age)
VALUES ('Tom', '23'), ('Lucy', 20);

Then, let’s query the rows in the table using the following SELECT statement:

SELECT * FROM test_int;

+------+-----+
| name | age |
+------+-----+
| Tom  |  23 |
| Lucy |  20 |
+------+-----+

Use an INT column as an auto-incrementing column

Typically, the primary key column uses the INT datatype and AUTO_INCREMENT attribute. See the following SQL:

CREATE TABLE test_int_pk(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name char(30) NOT NULL,
    age INT NOT NULL
);

Here, the column id is the primary key column. Its is of INT type and uses the AUTO_INCREMENT attribute.

Let’s insert two rows as same as the above example:

INSERT INTO test_int_pk (name, age)
VALUES ('Tom', '23'), ('Lucy', 20);

Then, let’s query the rows in the table using the following SELECT statement:

SELECT * FROM test_int_pk;

+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Tom  |  23 |
|  2 | Lucy |  20 |
+----+------+-----+

Here, the values of the id column are generated automatically.

Display width and ZEROFILL attributes

MySQL provides display width and ZEROFILL attributes for the INT data type, which are mainly used to display numbers in format. However, as of MySQL 8.0.17, both display-width and ZEROFILL are deprecated and will be removed in a future release. If you have similar formatting needs, you can use the LPAD() function.

Let’s look at an example.

First, let’s create a demo table:

CREATE TABLE test_int_zerofill(
    v2 INT(2) ZEROFILL,
    v3 INT(3) ZEROFILL,
    v4 INT(4) ZEROFILL
);

Then, let’s insert another row of data:

INSERT INTO test_int_zerofill (v2, v3, v4)
VALUES (2, 3, 4), (200, 3000, 40000);

Then, let’s query the rows in the table using the following SELECT statement:

SELECT * FROM test_int_zerofill;

+------+------+-------+
| v2   | v3   | v4    |
+------+------+-------+
|   02 |  003 |  0004 |
|  200 | 3000 | 40000 |
+------+------+-------+

Here, the values 2, 3, 4 are left padded with 0.

Unsigned integer data type

Use an unsigned integer when negative values are invalid for the column’s meaning. With strict SQL mode, MySQL rejects negative or too-large values; with a non-strict mode, it can clip them to a range boundary and report a warning.

First, let’s create a demo table:

CREATE TABLE test_int_unsigned(
    v INT UNSIGNED
);

Then, let’s try to insert an integer into the column:

INSERT INTO test_int_unsigned VALUES (1);

It worked. Now, let’s try to insert a negative number again:

INSERT INTO test_int_unsigned VALUES (-1);

With strict SQL mode enabled, MySQL rejects this value with Error 1264:

ERROR 1264 (22003): Out of range value for column 'v' at row 1

When strict SQL mode is disabled, MySQL clips an out-of-range value to the type boundary and reports a warning instead. Check @@SESSION.sql_mode and inspect the stored value; see how to diagnose MySQL Error 1264. Use an unsigned integer only when negative values are invalid for the column’s meaning.

Conclusion

In this article, we learned about INT data type and how to use the INT design tables and columns.

1. INT and INTEGER are synonyms.
2. MySQL supports several different integer data types: INT, SMALLINT, TINYINT, MEDIUMINT and BIGINT.
3. INT UNSIGNED is an unsigned integer.
4. Primary key columns usually use INT and AUTO_INCREMENT.