MySQL 5.7 to 8.0: INT Display Changes

1. Background

MySQL 5.7 is no longer updated, with the latest version being 5.7.44. Many clients upgrade to newer versions for security or new features. In this case, a client upgraded from MySQL 5.7.36 to 8.0.35 and noticed changes in table structures, with INT(10) becoming INT.

2. Local Reproduction

2.1 Table Structure and Data Before Upgrade

mysql> show create table t2\G
CREATE TABLE `t2` (
  `id1` int(4) unsigned DEFAULT NULL,
  `id2` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

mysql> insert into t2 values(1,1),(10,10),(100,100),(1000,1000),(12345,12345);

mysql> select * from t2;
+-------+-------+
| id1   | id2   |
+-------+-------+
|     1 |     1 |
|    10 |    10 |
|   100 |   100 |
|  1000 |  1000 |
| 12345 | 12345 |
+-------+-------+

‍

2.2 Table Structure and Data After Upgrade

mysql> show create table t2\G
CREATE TABLE `t2` (
  `id1` int unsigned DEFAULT NULL,
  `id2` int unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

mysql> select * from t2;
+-------+-------+
| id1   | id2   |
+-------+-------+
|     1 |     1 |
|    10 |    10 |
|   100 |   100 |
|  1000 |  1000 |
| 12345 | 12345 |
+-------+-------+

‍

2.3 Conclusion

After upgrading to MySQL 8.0.35, the id1 and id2 fields in table t2 became INT. This change only affects the display of the table structure and does not impact the data.

3. Discussion: MySQL Integer Type Expansion

The storage (in bytes) and ranges required for MySQL integer types.

3.1 How to Choose Integer Types?

1. Will negative numbers be used?
   If negative numbers are used, check if the integer type allows the required maximum value; if not, use unsigned integers.
2. How to choose cost-effective integer types?
   Each integer type has an upper limit for inserted values. Larger storage (bytes) allows larger values but consumes more memory and reduces computational efficiency. Choose the appropriate integer type based on the upper limit of the inserted values.

INT(num) does not limit the range of inserted values!

3.2 Use Cases for INT(num)

The data type INT(num) is different from CHAR(num) and is easily confused. CHAR(num) refers to the maximum number of characters that can be inserted.

mysql> show create table tv\G
CREATE TABLE `tv` (
  `name` char(2) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

mysql> insert into tv values('a');
mysql> insert into tv values('aa');
// Exceeds the char(2) limit and cannot be inserted
mysql> insert into tv values('aaa');
ERROR 1406 (22001): Data too long for column 'name' at row 1

‍

INT(num) only works with zerofill to indicate display width, padding with 0s if the width is not met. Using INT(num) alone has no effect and does not limit the range of inserted data, so it is not recommended. When used with zerofill, the table structure is not modified during MySQL upgrades.

// Before upgrade
mysql> show create table t1\G
CREATE TABLE `t1` (
  `id1` int(4) unsigned zerofill DEFAULT NULL,
  `id2` int(10) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

mysql> insert into t1 values(1,1),(10,10),(100,100),(1000,1000),(12345,12345);
mysql> select * from t1;
+-------+------------+
| id1   | id2        |
+-------+------------+
|  0001 | 0000000001 |
|  0010 | 0000000010 |
|  0100 | 0000000100 |
|  1000 | 0000001000 |
| 12345 | 0000012345 |
+-------+------------+

// After upgrade
mysql> show create table t1\G
CREATE TABLE `t1` (
  `id1` int(4) unsigned zerofill DEFAULT NULL,
  `id2` int(10) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

mysql> select * from t1;
+-------+------------+
| id1   | id2        |
+-------+------------+
|  0001 | 0000000001 |
|  0010 | 0000000010 |
|  0100 | 0000000100 |
|  1000 | 0000001000 |
| 12345 | 0000012345 |
+-------+------------+

‍

3.3 Direct Use of INT

Different MySQL versions display INT differently. In MySQL 5.7.25, the default display is INT(11), and with zerofill, it's INT(10). In MySQL 8.0.35, the default is still INT, and with zerofill, it's INT(10).

‍