int 4 bytes
小结:
0、int(4) + ZEROFILL 实现3-->0003,但可以存储12345;
1、ZEROFILL 在MySQL 8.0.17中被移除,可以通过LPAD实现补0;
https://dev.mysql.com/doc/refman/8.0/en/integer-types.html
MySQL supports the SQL standard integer types INTEGER
(or INT
) and SMALLINT
. As an extension to the standard, MySQL also supports the integer types TINYINT
, MEDIUMINT
, and BIGINT
. The following table shows the required storage and range for each integer type.
Table 11.1 Required Storage and Range for Integer Types Supported by MySQL
Type | Storage (Bytes) | Minimum Value Signed | Minimum Value Unsigned | Maximum Value Signed | Maximum Value Unsigned |
---|---|---|---|---|---|
TINYINT |
1 | -128 |
0 |
127 |
255 |
SMALLINT |
2 | -32768 |
0 |
32767 |
65535 |
MEDIUMINT |
3 | -8388608 |
0 |
8388607 |
16777215 |
INT |
4 | -2147483648 |
0 |
2147483647 |
4294967295 |
BIGINT |
8 | -263 |
0 |
263-1 |
264-1 |
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.)
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 asSMALLINT(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.
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
.
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.
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. Support for ZEROFILL
and display widths for integer data types will be removed in a future MySQL version. Consider using an alternative means of producing the effect of these attributes. For example, applications could use the LPAD()
function to zero-pad numbers up to the desired width, or they could store the formatted numbers in CHAR
columns.