MySQL 8 ignoring integer lengths

Question

I have a MySQL 8.0.19 running in a Docker container and using the InnoDB engine. I have noticed that table integer field lengths are getting ignored.

The issue occurs with integer datatypes regardless if running a CREATE or ALTER query

CREATE TABLE `test` (
  `id` int DEFAULT NULL,
  `text_field` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `decimal_field` decimal(6,2) DEFAULT NULL,
  `int_field` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The lengths are showing as 0 in my MySQL client (Navicat), but the same occurs if checking in the console with SHOW FULL COLUMNS FROMtest;

mysql> SHOW FULL COLUMNS FROM `test`;
+---------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field         | Type         | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+---------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| id            | int          | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| text_field    | varchar(20)  | utf8mb4_unicode_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| decimal_field | decimal(6,2) | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| int_field     | int          | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
+---------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+

The Type column should be showing int(11) for the two integer fields, but it isn't.

Is this related to something in my MySQL settings, and if so, which variable would have to be changed?

Answer 1

This is a change documented in the MySQL 8.0.19 release notes:

Display width specification for integer data types was deprecated in MySQL 8.0.17, and now statements that include data type definitions in their output no longer show the display width for integer types, with these exceptions:

• The type is TINYINT(1). MySQL Connectors make the assumption that TINYINT(1) columns originated as BOOLEAN columns; this exception enables them to continue to make that assumption.

• The type includes the ZEROFILL attribute.

This change applies to tables, views, and stored routines, and affects the output from SHOW CREATE and DESCRIBE statements, and from INFORMATION_SCHEMA tables.

For DESCRIBE statements and INFORMATION_SCHEMA queries, output is unaffected for objects created in previous MySQL 8.0 versions because information already stored in the data dictionary remains unchanged. This exception does not apply for upgrades from MySQL 5.7 to 8.0, for which all data dictionary information is re-created such that data type definitions do not include display width. (Bug #30556657, Bug #97680)

The "length" of an integer column doesn't mean anything. A column of int(11) is the same as int(2) or int(40). They are all a fixed-size, 32-bit integer data type. They support the same minimum and maximum value.

The "length" of integer columns has been a confusing feature of MySQL for years. It's only a hint that affects the display width, not the storage or the range of values. Practically, it only matters when you use the ZEROFILL option.

mysql> create table t ( i1 int(6) zerofill, i2 int(12) zerofill );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t set i1 = 123, i2 = 123;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+--------+--------------+
| i1     | i2           |
+--------+--------------+
| 000123 | 000000000123 |
+--------+--------------+
1 row in set (0.00 sec)

So it's a good thing that the misleading integer "length" is now deprecated and removed. It has caused confusion for many years.