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

Sometimes, you want a column that only accepts 0 and positive numbers, and you can use unsigned integer type. Columns of unsigned type only accept 0 and positive integers. When you insert a negative number into an unsigned integer column, MySQL will return an error message.

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);

MySQL returned an error:

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

If you want to prohibit inserting negative numbers into the column, unsigned integer types are a good choice.

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.