MySQL 8 忽略整数长度

Question

我有一个 MySQL 8.0.19 在 Docker 容器中运行并使用 InnoDB 引擎。我注意到表整数字段长度被忽略了。

无论是运行 CREATE 还是 ALTER 查询，整数数据类型都会出现此问题

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;

长度在我的 MySQL 客户端 (Navicat) 中显示为 0，但如果在控制台中使用 SHOW FULL COLUMNS FROM checkin ，也会发生同样的情况。测试 ;

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 |         |
+---------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+

Type 列应该显示两个整数字段的 int(11) ，但它不是。

这是否与我的 MySQL 设置中的某些内容有关，如果是，则必须更改哪个变量？

Answer 1

这是 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)

整数列的“长度”没有任何意义。一栏 int(11)与 int(2) 相同或 int(40) .它们都是固定大小的 32 位整数数据类型。它们支持相同的最小值和最大值。

整数列的“长度”多年来一直是 MySQL 的一个令人困惑的特性。这只是影响显示宽度的提示，而不是存储或值的范围。实际上，只有在您使用 ZEROFILL 时才重要。选项。

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)

因此，现在已弃用并删除了误导性整数“长度”，这是一件好事。多年来，它引起了困惑。