Movatterモバイル変換

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 anINT 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 doesnot 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 usualSMALLINT range of-32768 to32767, 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 asINT(4) ZEROFILL, a value of5 is retrieved as0005.

TheZEROFILL attribute is deprecated for numeric data types, as is the display width attribute for integer data types. You should expect support forZEROFILL 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 theLPAD() function to zero-pad numbers up to the desired width, or they can store the formatted numbers inCHAR 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 anINT column isUNSIGNED, the size of the column's range is the same but its endpoints shift up, from-2147483648 and2147483647 to0 and4294967295.

Floating-point and fixed-point types also can beUNSIGNED. 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 typeFLOAT,DOUBLE, andDECIMAL (and any synonyms) and you should expect support for it to be removed in a future version of MySQL. Consider using a simpleCHECK constraint instead for such columns.

If you specifyZEROFILL for a numeric column, MySQL automatically adds theUNSIGNED attribute.

Integer or floating-point data types can have theAUTO_INCREMENT attribute. When you insert a value ofNULL into an indexedAUTO_INCREMENT column, the column is set to the next sequence value. Typically this isvalue+1, wherevalue is the largest value for the column currently in the table. (AUTO_INCREMENT sequences begin with1.)

Storing0 into anAUTO_INCREMENT column has the same effect as storingNULL, unless theNO_AUTO_VALUE_ON_ZERO SQL mode is enabled.

InsertingNULL to generateAUTO_INCREMENT values requires that the column be declaredNOT NULL. If the column is declaredNULL, insertingNULL stores aNULL. When you insert any other value into anAUTO_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 forAUTO_INCREMENT columns are not supported.

CHECK constraints cannot refer to columns that have theAUTO_INCREMENT attribute, nor can theAUTO_INCREMENT attribute be added to existing columns that are used inCHECK constraints.

AUTO_INCREMENT is deprecated forFLOAT andDOUBLE columns; you should expect support for it to be removed in a future version of MySQL. Consider removing theAUTO_INCREMENT attribute from such columns to avoid potential compatibility issues, or convert them to an integer type.