MySQL INT and Integer Types: Ranges and UNSIGNED

In MySQL, INT and INTEGER are synonyms for the same 4-byte integer type. Its signed range is -2,147,483,648 to 2,147,483,647; INT UNSIGNED stores nonnegative values from 0 to 4,294,967,295. MySQL also provides TINYINT, SMALLINT, MEDIUMINT, and BIGINT for other storage and range needs. See the MySQL integer type reference.

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 common declarations are signed by default; add UNSIGNED only when negative values are invalid for the column’s meaning:

INT [UNSIGNED]

INT(M) does not change an integer’s storage size or value range. The M is a deprecated display-width attribute, not a precision or maximum value. ZEROFILL uses that width to pad output with leading zeros and also implicitly makes the column UNSIGNED. Both integer display width and ZEROFILL have been deprecated since MySQL 8.0.17. Avoid them in new schemas; format values in the application or use LPAD() when SQL text output needs padding. See MySQL’s numeric type syntax and 8.0.17 release notes.

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.

Legacy display width and ZEROFILL

The following example shows legacy syntax so you can recognize existing schemas. The width does not restrict the values stored; ZEROFILL only pads displayed values that need fewer digits. New definitions should omit both attributes and format identifiers outside the stored numeric value.

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, and 4 are left-padded with zeros. Values wider than the display width are not truncated.

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.