13.1.6 数值类型属性
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 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.
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.
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.
All integer types can have an optional (nonstandard) UNSIGNED
attribute. An unsigned type can be used to permit only nonnegative numbers in a column or when you need a larger upper numeric range for the column. For example, if an INT
column is UNSIGNED
, the size of the column's range is the same but its endpoints shift up, from -2147483648
and 2147483647
to 0
and 4294967295
.
Floating-point and fixed-point types also can be UNSIGNED
. As with integer types, this attribute prevents negative values from being stored in the column. Unlike the integer types, the upper range of column values remains the same. UNSIGNED
is deprecated for columns of type FLOAT
, DOUBLE
, and DECIMAL
(and any synonyms) and you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK
constraint instead for such columns.
If you specify ZEROFILL
for a numeric column, MySQL automatically adds the UNSIGNED
attribute.
Integer or floating-point data types can have the AUTO_INCREMENT
attribute. When you insert a value of NULL
into an indexed AUTO_INCREMENT
column, the column is set to the next sequence value. Typically this is
, where value
+1value
is the largest value for the column currently in the table. (AUTO_INCREMENT
sequences begin with 1
.)
Storing 0
into an AUTO_INCREMENT
column has the same effect as storing NULL
, unless the NO_AUTO_VALUE_ON_ZERO
SQL mode is enabled.
Inserting NULL
to generate AUTO_INCREMENT
values requires that the column be declared NOT NULL
. If the column is declared NULL
, inserting NULL
stores a NULL
. When you insert any other value into an AUTO_INCREMENT
column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the inserted value.
Negative values for AUTO_INCREMENT
columns are not supported.
CHECK
constraints cannot refer to columns that have the AUTO_INCREMENT
attribute, nor can the AUTO_INCREMENT
attribute be added to existing columns that are used in CHECK
constraints.
AUTO_INCREMENT
is deprecated for FLOAT
and DOUBLE
columns; you should expect support for it to be removed in a future version of MySQL. Consider removing the AUTO_INCREMENT
attribute from such columns to avoid potential compatibility issues, or convert them to an integer type.