MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
For integer data types, M indicates
the minimum display width. The maximum display width is 255.
Display width is unrelated to the range of values a type can
store, as described in
Section 13.1.6, “Numeric Type Attributes”.
For floating-point and fixed-point data types,
M is the total number of digits that
can be stored.
As of MySQL 8.0.17, the display width attribute is deprecated for integer data types; you should expect support for it to be removed in a future version of MySQL.
If you specify ZEROFILL for a numeric column,
MySQL automatically adds the UNSIGNED
attribute to the column.
As of MySQL 8.0.17, the ZEROFILL attribute is
deprecated for numeric data types; you should expect support for
it to be removed in a future version of MySQL. Consider using an
alternative means of producing the effect of this attribute. 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.
Numeric data types that permit the UNSIGNED
attribute also permit SIGNED. However, these
data types are signed by default, so the
SIGNED attribute has no effect.
As of MySQL 8.0.17, the UNSIGNED attribute is
deprecated for columns of type
FLOAT,
DOUBLE, and
DECIMAL (and any synonyms); 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.
SERIAL is an alias for BIGINT
UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
SERIAL DEFAULT VALUE in the definition of an
integer column is an alias for NOT NULL AUTO_INCREMENT
UNIQUE.
When you use subtraction between integer values where one is
of type UNSIGNED, the result is unsigned
unless the
NO_UNSIGNED_SUBTRACTION SQL
mode is enabled. See Section 14.10, “Cast Functions and Operators”.
A bit-value type. M indicates the
number of bits per value, from 1 to 64. The default is 1 if
M is omitted.
TINYINT[(
M)]
[UNSIGNED] [ZEROFILL]
A very small integer. The signed range is
-128 to 127. The
unsigned range is 0 to
255.
These types are synonyms for
TINYINT(1). A value of zero
is considered false. Nonzero values are considered true:
mysql>SELECT IF(0, 'true', 'false');+------------------------+ | IF(0, 'true', 'false') | +------------------------+ | false | +------------------------+ mysql>SELECT IF(1, 'true', 'false');+------------------------+ | IF(1, 'true', 'false') | +------------------------+ | true | +------------------------+ mysql>SELECT IF(2, 'true', 'false');+------------------------+ | IF(2, 'true', 'false') | +------------------------+ | true | +------------------------+
However, the values TRUE and
FALSE are merely aliases for
1 and 0, respectively,
as shown here:
mysql>SELECT IF(0 = FALSE, 'true', 'false');+--------------------------------+ | IF(0 = FALSE, 'true', 'false') | +--------------------------------+ | true | +--------------------------------+ mysql>SELECT IF(1 = TRUE, 'true', 'false');+-------------------------------+ | IF(1 = TRUE, 'true', 'false') | +-------------------------------+ | true | +-------------------------------+ mysql>SELECT IF(2 = TRUE, 'true', 'false');+-------------------------------+ | IF(2 = TRUE, 'true', 'false') | +-------------------------------+ | false | +-------------------------------+ mysql>SELECT IF(2 = FALSE, 'true', 'false');+--------------------------------+ | IF(2 = FALSE, 'true', 'false') | +--------------------------------+ | false | +--------------------------------+
The last two statements display the results shown because
2 is equal to neither
1 nor 0.
SMALLINT[(
M)]
[UNSIGNED] [ZEROFILL]
A small integer. The signed range is
-32768 to 32767. The
unsigned range is 0 to
65535.
MEDIUMINT[(
M)]
[UNSIGNED] [ZEROFILL]
A medium-sized integer. The signed range is
-8388608 to 8388607.
The unsigned range is 0 to
16777215.
INT[(
M)]
[UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is
-2147483648 to
2147483647. The unsigned range is
0 to 4294967295.
INTEGER[(
M)]
[UNSIGNED] [ZEROFILL]
This type is a synonym for
INT.
BIGINT[(
M)]
[UNSIGNED] [ZEROFILL]
A large integer. The signed range is
-9223372036854775808 to
9223372036854775807. The unsigned range
is 0 to
18446744073709551615.
SERIAL is an alias for BIGINT
UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
Some things you should be aware of with respect to
BIGINT columns:
All arithmetic is done using signed
BIGINT or
DOUBLE values, so you
should not use unsigned big integers larger than
9223372036854775807 (63 bits) except
with bit functions! If you do that, some of the last
digits in the result may be wrong because of rounding
errors when converting a
BIGINT value to a
DOUBLE.
MySQL can handle BIGINT
in the following cases:
When using integers to store large unsigned values
in a BIGINT column.
In
MIN(
or
col_name)MAX(,
where col_name)col_name refers to
a BIGINT column.
When using operators
(+,
-,
*,
and so on) where both operands are integers.
You can always store an exact integer value in a
BIGINT column by storing
it using a string. In this case, MySQL performs a
string-to-number conversion that involves no
intermediate double-precision representation.
The -,
+, and
*
operators use BIGINT
arithmetic when both operands are integer values. This
means that if you multiply two big integers (or results
from functions that return integers), you may get
unexpected results when the result is larger than
9223372036854775807.
DECIMAL[(
M[,D])]
[UNSIGNED] [ZEROFILL]
A packed “exact” fixed-point number.
M is the total number of digits
(the precision) and D is the
number of digits after the decimal point (the scale). The
decimal point and (for negative numbers) the
- sign are not counted in
M. If
D is 0, values have no decimal
point or fractional part. The maximum number of digits
(M) for
DECIMAL is 65. The maximum
number of supported decimals (D)
is 30. If D is omitted, the
default is 0. If M is omitted,
the default is 10. (There is also a limit on how long the
text of DECIMAL literals can
be; see Section 14.24.3, “Expression Handling”.)
UNSIGNED, if specified, disallows
negative values. As of MySQL 8.0.17, the
UNSIGNED attribute is deprecated for
columns of type DECIMAL (and
any synonyms); 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.
All basic calculations (+, -, *, /) with
DECIMAL columns are done with
a precision of 65 digits.
DEC[(,
M[,D])]
[UNSIGNED] [ZEROFILL]NUMERIC[(,
M[,D])]
[UNSIGNED] [ZEROFILL]FIXED[(
M[,D])]
[UNSIGNED] [ZEROFILL]
These types are synonyms for
DECIMAL. The
FIXED synonym is available
for compatibility with other database systems.
FLOAT[(
M,D)]
[UNSIGNED] [ZEROFILL]
A small (single-precision) floating-point number.
Permissible values are -3.402823466E+38
to -1.175494351E-38,
0, and 1.175494351E-38
to 3.402823466E+38. These are the
theoretical limits, based on the IEEE standard. The actual
range might be slightly smaller depending on your hardware
or operating system.
M is the total number of digits
and D is the number of digits
following the decimal point. If M
and D are omitted, values are
stored to the limits permitted by the hardware. A
single-precision floating-point number is accurate to
approximately 7 decimal places.
FLOAT(
is a nonstandard MySQL extension. As of MySQL 8.0.17, this
syntax is deprecated, and you should expect support for it
to be removed in a future version of MySQL.
M,D)
UNSIGNED, if specified, disallows
negative values. As of MySQL 8.0.17, the
UNSIGNED attribute is deprecated for
columns of type FLOAT (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.
Using FLOAT might give you
some unexpected problems because all calculations in MySQL
are done with double precision. See
Section B.3.4.7, “Solving Problems with No Matching Rows”.
FLOAT(
p)
[UNSIGNED] [ZEROFILL]
A floating-point number. p
represents the precision in bits, but MySQL uses this value
only to determine whether to use
FLOAT or
DOUBLE for the resulting data
type. If p is from 0 to 24, the
data type becomes FLOAT with
no M or
D values. If
p is from 25 to 53, the data type
becomes DOUBLE with no
M or D
values. The range of the resulting column is the same as for
the single-precision FLOAT or
double-precision DOUBLE data
types described earlier in this section.
UNSIGNED, if specified, disallows
negative values. As of MySQL 8.0.17, the
UNSIGNED attribute is deprecated for
columns of type FLOAT (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.
FLOAT(
syntax is provided for ODBC compatibility.
p)
DOUBLE[(
M,D)]
[UNSIGNED] [ZEROFILL]
A normal-size (double-precision) floating-point number.
Permissible values are
-1.7976931348623157E+308 to
-2.2250738585072014E-308,
0, and
2.2250738585072014E-308 to
1.7976931348623157E+308. These are the
theoretical limits, based on the IEEE standard. The actual
range might be slightly smaller depending on your hardware
or operating system.
M is the total number of digits
and D is the number of digits
following the decimal point. If M
and D are omitted, values are
stored to the limits permitted by the hardware. A
double-precision floating-point number is accurate to
approximately 15 decimal places.
DOUBLE(
is a nonstandard MySQL extension. As of MySQL 8.0.17, this
syntax is deprecated and you should expect support for it to
be removed in a future version of MySQL.
M,D)
UNSIGNED, if specified, disallows
negative values. As of MySQL 8.0.17, the
UNSIGNED attribute is deprecated for
columns of type DOUBLE (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.
DOUBLE
PRECISION[(,
M,D)]
[UNSIGNED] [ZEROFILL]REAL[(
M,D)]
[UNSIGNED] [ZEROFILL]
These types are synonyms for
DOUBLE. Exception: If the
REAL_AS_FLOAT SQL mode is
enabled, REAL is a synonym
for FLOAT rather than
DOUBLE.