mysql option长度,MySQL 8忽略整数长度

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?

解决方案

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.