Rechercher dans le manuel MySQL
11.2.5 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.)
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.
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.
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. As of MySQL 8.0.17, the
UNSIGNED
attribute is deprecated for columns
of type FLOAT
,
DOUBLE
, and
DECIMAL
(and any synonyms) and
will be removed in a future MySQL version. 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.
As of MySQL 8.0.17, AUTO_INCREMENT
support is
deprecated for FLOAT
and
DOUBLE
columns and will be
removed in a future MySQL version. Consider removing the
AUTO_INCREMENT
attribute from such columns,
or convert them to an integer type.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-numeric-type-attributes.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.