MySQL INT Type Changes After Upgrading from 5.7 to 8.0 and How to Choose Integer Types

1 Background

MySQL 5.7 has reached end‑of‑life; many customers need to upgrade to newer versions. In this case a client upgraded from MySQL 5.7.36 to 8.0.35 and noticed that a column defined as INT(10) became INT after the upgrade, raising concerns about possible impact.

Before the story, a quick question: what is the purpose of INT(num) in MySQL? Can it limit the range of inserted data? How should it be used correctly?

2 Local Reproduction

Pre‑upgrade table structure and data

mysql> show create table t2\G
*************************** 1. row ***************************
        Table: t2
Create Table: CREATE TABLE `t2` (
  `id1` int(4) unsigned DEFAULT NULL,
  `id2` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> insert into t2 values(1,1),(10,10),(100,100),(1000,1000),(12345,12345);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+-------+-------+
| id1  | id2  |
+-------+-------+
|     1 |     1 |
|    10 |    10 |
|   100 |   100 |
|  1000 |  1000 |
| 12345 | 12345 |
+-------+-------+
5 rows in set (0.00 sec)

Post‑upgrade table structure and data

mysql> show create table t2\G
*************************** 1. row ***************************
        Table: t2
Create Table: CREATE TABLE `t2` (
  `id1` int unsigned DEFAULT NULL,
  `id2` int unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> select * from t2;
+-------+-------+
| id1  | id2  |
+-------+-------+
|     1 |     1 |
|    10 |    10 |
|   100 |   100 |
|  1000 |  1000 |
| 12345 | 12345 |
+-------+-------+
5 rows in set (0.00 sec)

Conclusion

After upgrading to 8.0.35 the id1 and id2 columns of table t2 are both displayed as INT . The change is only visual; the underlying data is unaffected.

3 Discussion: MySQL Integer Type Expansion

MySQL integer types and their storage size and range are listed below (reference). Choosing an integer type depends on whether negative values are needed, the trade‑off between storage cost and maximum value, and the fact that INT(num) does not limit the range of inserted values.

How to choose an integer type?

Whether to use signed numbers; if not, use unsigned integers.

Balance storage size against the maximum value required; larger types consume more memory and compute resources.

INT(num) does not restrict the range of inserted data; it only defines display width.

int(num) usage scenarios

The meaning of INT(num) differs from CHAR(num) . CHAR(num) limits the number of characters that can be stored, while INT(num) only affects how the value is displayed and works with ZEROFILL to pad leading zeros.

mysql> show create table tv\G
*************************** 1. row ***************************
        Table: tv
Create Table: CREATE TABLE `tv` (
  `name` char(2) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> insert into tv values('a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tv values('aa');
Query OK, 1 row affected (0.01 sec)

// exceeding char(2) limit, cannot insert
mysql> insert into tv values('aaa');
ERROR 1406 (22001): Data too long for column 'name' at row 1

In INT(num) , the num part only takes effect when combined with ZEROFILL , which specifies a display width and pads with leading zeros. Using INT(num) alone has no effect on stored values or range limits, so it is not recommended.

Direct use of INT

Different MySQL versions display plain INT differently: MySQL 5.7.25 shows INT(11) by default; with ZEROFILL it shows INT(10) . MySQL 8.0 still shows INT , and with ZEROFILL it shows int(10) .

Reference

[1] Integer type storage limits: https://dev.mysql.com/doc/refman/8.0/en/integer-types.html