前几天面试,问到这个问题,crud 惯了,从来没深入研究过这些(一直以为是能存储的字节数)。

回来查了一下官方文档:Numeric Type Attributes

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used is up to the application.)

MySQL 有一个支持在字段类型关键字后边的 () 里指定 integer 数据类型的显示宽度的扩展。例如 INT(4) 指定一个显示宽度为4位数的整数。这个选项一般用在当字段值的宽度小于字段指定的宽度时,用空格对值进行左填充。(也就是说,此宽度存在于随结果集返回的元数据中。是否使用取决于应用程序。)

The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

显示宽度既不限制该字段可以存储的值的范围,也不会阻止比显示宽度宽的值的正确显示。例如一个字段指定为 SMALLINT(3),通常 SMALLINT 的范围为 -32768 到 32767,超出三位数宽度的值将会以三位数以上的数字全部显示。

When used in conjunction with the optional (nonstandard) ZEROFILL attribute, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is retrieved as 0005.

当与可选属性 ZEROFILL 一起使用时,默认的空格填充将替换为用 0 填充。例如一个字段声明为 INT(4) ZEROFILL,那么值 5 将被检索为 0005

NOTE

The ZEROFILL attribute is ignored for columns involved in expressions or UNION queries.

If you store values larger than the display width in an integer column that has the ZEROFILL attribute, you may experience problems when MySQL generates temporary tables for some complicated joins. In these cases, MySQL assumes that the data values fit within the column display width.

注意

ZEROFILL 属性在表达式和联合查询中会被忽略。

如果你在一个设置了 ZEROFILL 属性的整数字段中,存储的值大于显示宽度,你可能会在一些复杂的联合查询中生成临时表是碰到问题。在这些情况下,MySQL 假设数据值适合字段的显示宽度。

Excuse me 🤔️? 那这有啥用?

As of MySQL 8.0.17, the ZEROFILL attribute is deprecated for numeric data types, as is the display width attribute for integer data types. You should expect support for ZEROFILL and display widths for integer data types to be removed in a future version of MySQL. Consider using an alternative means of producing the effect of these attributes. For example, applications can use the LPAD() function to zero-pad numbers up to the desired width, or they can store the formatted numbers in CHAR columns.

MySQL 8.0.17 开始,数字数据类型不推荐使用 ZEROFILL 属性,整数数据类型也不推荐使用 display width 属性。你应该预料到在 MySQL 的未来版本中会删除对整数数据类型的 ZEROFILLdisplay width 的支持,考虑用其他方法来实现这些属性的功能。例如用 LPAD() 函数将数字填充为指定宽度,或者将格式化后的数字存储在 CHAR 类型的字段中。

弃用了我就放心了。