MySQL 8忽略整数长度 [英] MySQL 8 ignoring integer lengths

问题描述

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

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.

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

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;

在我的MySQL客户端(Navicat)中，长度显示为0，但是如果在控制台中使用SHOW FULL COLUMNS FROM test ;

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

类型"列的两个整数字段应该显示int(11)，但不是.

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

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

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

推荐答案

这是

不赞成使用整数数据类型的显示宽度规范 MySQL 8.0.17，现在包含在其中的数据类型定义的语句 它们的输出不再显示整数类型的显示宽度， 这些例外:

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:

• 类型为TINYINT(1). MySQL连接器假设 TINYINT(1)列起源于BOOLEAN列；这个例外 使他们能够继续做这个假设.

• 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.

类型包括ZEROFILL属性.

The type includes the ZEROFILL attribute.

此更改适用于表，视图和存储的例程，并且会影响 SHOW CREATE和DESCRIBE语句以及 INFORMATION_SCHEMA表.

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

对于DESCRIBE语句和INFORMATION_SCHEMA查询，输出为 对于以前的MySQL 8.0版本中创建的对象不受影响，因为 已经存储在数据字典中的信息保持不变. 对于从MySQL 5.7到8.0的升级，该例外不适用于 重新创建所有数据字典信息的数据 类型定义不包括显示宽度. (错误#30556657，错误#97680)

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位整数数据类型.它们支持相同的最小值和最大值.

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.

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

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)

因此，不建议使用并删除具有误导性的整数"length"是一件好事.多年来引起混乱.

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

这篇关于MySQL 8忽略整数长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！